No announcement yet.

excel 2000 to text file

  • Filter
  • Time
  • Show
Clear All
new posts

  • excel 2000 to text file

    Years ago I was given a program that converted a WORD file to a text file.
    This was a COM application. Very useful. Now I need the same for an a simple EXCEL 2000 spreadsheet.

    I found this comment back from 2000:

    Another approach you mention would likely be fairly fast too... use COM to export the file as a text-delimited file that you could subsequently read into PB with relative ease.

    So is there anyone who has such a program?


  • #2
    Roy Cline


    • #3

      This link does not work

      [I]I made a coding error once - but fortunately I fixed it before anyone noticed[/I]
      Kerry Farmer


      • #4
        Hi Tom, try these links:

        Last edited by Sergio Tallone; 16 Nov 2007, 02:45 AM.


        • #5
          More Excel Tomfoolery

          Here's another example. This Console Compiler program opens Excel, prints the numbers 1 through 400 in the first 40 rows moving down the sheet and crossing cells A through J repeadedly, then reads the data back out of the cells and outputs it to a console compiler screen. To create a file you would just need to open one and modify the print statement to direct output to there. The documentation on the COM Brouser describes how to create the interface file - here, that includes all the interfaces exported by the COM object that represents MSExcel.exe.

          #Compile Exe
          #Dim All
          #Include ""
          Function PBMain() As Long
            Local oExcelApp As ExcelApplication
            Local oExcelWorkbook As ExcelWorkbook
            Local oExcelWorkSheet As ExcelWorkSheet
            Local vRange,vText,vVnt,vBool As Variant
            Local strProgramID As String
            Local strNum() As String
            Register i As Long
            Register j As Long
            Local k As Long
            Set oExcelApp = New ExcelApplication In strProgramID
            vBool = 1
            Object Let oExcelApp.Visible = vBool
            Object Call oExcelApp.WorkBooks.Add To vVnt ' Create a new workbook in EXCEL
            Set oExcelWorkbook = vVnt                   ' Copy the Interface reference into an Object Variable
            Object Get oExcelWorkBook.ActiveSheet To vVnt
            Set oExcelWorkSheet = vVnt
            Redim strNum(9) As String
            For i=1 To 40
              vRange="A" & Trim$(Str$(i)) & ":" & "J" & Trim$(Str$(i))
              For j=0 To 9
                Incr k
              Next j
              Object Let oExcelWorkSheet.Range(vRange).Value = vText
            Next i
            Erase strNum
            'Below prints out data to console by obtaining it one cell at a time
            For i= 1 To 40
              For j = 65 To 74
                vRange=Chr$(j) & Trim$(Str$(i))
                Object Get oExcelWorkSheet.Range(vRange).Value To vText
                Print Variant$(vText);"  ";
              Next j
            Next i
            Object Call oExcelApp.Quit
            Set oExcelApp = Nothing
            Set oExcelWorkbook = Nothing
            Set oExcelWorkSheet = Nothing
          End Function


          • #6
            Excel to Txt


            All you have to do is use the SAVE AS option in Excel to
            output in .TXT or .CSV format.
            Then read it as a sequential file
            Old QB45 Programmer


            • #7
              Yes, Guy is of course right. If your programming need is to just get at all the data in an excel spreadsheet, it is far faster and more efficient to let Excel itself export the data either to some sort of delimited text file, or even possibly export the data directly to a database itself, if that is the 'end game'. All Microsoft Office products have wizards for importing and exporting data that are pretty easy to use, and they are quite flexible in terms of the data sources and formats they work with.

              Sometimes though, it is desirable to be able to just make a connection to a spreadsheet and pull out some particular piece or piecies of data. If you find yourself in that situation you will most certainly find yourself needing the VariantVT function of PowerBASIC, as with that function you will be able to determine the internal format of the data in an Excel cell, and this is oftentimes necessary in order to determine which COM function to use to extract the data. When you find yourself staring at zeros or empty strings where you expected data, you've reached the point of either being in the wrong cell, or needing the VariantVT function.


              • #8
                tried samples

                The issue is the ".inc:
                Does everyone have or in the pbcc40 folder
                or do I have to get it from somewhere?


                • #9
                  Yes, Guy is of course right. If your programming need is to just get at all the data in an excel spreadsheet, it is far faster and more efficient to let Excel itself export the data either to some sort of delimited text file....wizards for importing and exporting data...
                  Since I'm not real facile with the COM facilities, I do the 'combination cheat:'

                  I use the COM syntax to export to delimited (working code here somewhere), then I use the delimited file for the real work.

                  Works for me.

                  Michael Mattias
                  Tal Systems (retired)
                  Port Washington WI USA
                  [email protected]


                  • #10


                    Sorry I Hadn't checked back in awhile. The is made using the PowerBASIC COM Browser, which is part of PBEdit. Go to ...

                    Tools >> PowerBASIC COM Browser

                    Then that utility will open. Next go to the File menu and select the choice 'Open Registered Library'

                    File >> Open Registered Library

                    What happens here is that PowerBASIC's COM Browser scans the Window's Registry for registered COM components. To be able to use PB's COM facilities to read/write Excel files you need to have a version of Excel installed. If you don't have a version of Excel installed it is still possible to read/write Excel files, but it can't be done through COM, but must be done rather through either ODBC or DAO (I'm guessing about the DAO part). If that is the case and you don't have Excel installed on your computer, let me know and I'll show you how to do it through ODBC. I'll continue here though under the supposition you have Excel installed.

                    In the list of registered components the COM Browser lists, search for something like 'Microsoft Excel 9.0 Object Library 1.3'. It can be another version. I have several myself on different computers. But when you find that open it then choose ...

                    Select All Interfaces'

                    from the file menu. Then from the file menu again select...

                    'Save Interface To clipboard.

                    Then open Notepad and Paste the interfaces in that. Save that file as You can actually name it whatever you want, so long as your source file program is modified accordingly. That's about all there is to it. If you have any difficulties I email you mine.


                    • #11

                      Although I did simply save the excel to csv, I also used several of Fred Harris' samples and his method to create to write the code to selectively pull off certain rows and columns to a text file that I used to print a report and labels.

                      I wonder how does one gain the knowledge of COM.. Is it a result of working with VB previous to working with PBCC?

                      Thanks to all.


                      • #12
                        Yes it is not at all easy, I can well remember that.

                        The source code forum has quite a bit for Excel.

                        EVERYBODY uses the Excel Macro recorder. And then converts to Powerbasic IF need be.
                        In Excel, Tools, Macro, Record New Macro.