Announcement

Collapse

New Sub-Forum

In an effort to help make sure there are appropriate categories for topics of discussion that are happening, there is now a sub-forum for databases and database programming under Special Interest groups. Please direct questions, etc., about this topic to that sub-forum moving forward. Thank you.
See more
See less

excel 2000 to text file

Collapse
X
 
  • 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?

    Thanks

  • #2
    http://www.powerbasic.com/support/pb...searchid=75235
    Roy Cline

    Comment


    • #3
      Roy

      This link does not work

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

      Comment


      • #4
        Hi Tom, try these links:
        http://www.powerbasic.com/support/pb...ad.php?t=22020

        http://www.powerbasic.com/support/pb...ad.php?t=23807
        bye

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

        Comment


        • #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 PBExcel.inc, that includes all the interfaces exported by the COM object that represents MSExcel.exe.

          Code:
          #Compile Exe
          #Dim All
          #Include "PBExcel.inc"
          
          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
          
            strProgramID=ProgId$(ClsId$("Excel.Application"))
            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
            k=1
            For i=1 To 40
              vRange="A" & Trim$(Str$(i)) & ":" & "J" & Trim$(Str$(i))
              For j=0 To 9
                strNum(j)=Str$(k)
                Incr k
              Next j
              vText=strNum()
              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
              Print
            Next i
            Object Call oExcelApp.Quit
            Set oExcelApp = Nothing
            Set oExcelWorkbook = Nothing
            Set oExcelWorkSheet = Nothing
            Waitkey$
          
            PBMain=0
          End Function
          Fred
          "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

          Comment


          • #6
            Excel to Txt

            Tom,

            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

            Comment


            • #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.
              Fred
              "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

              Comment


              • #8
                tried samples

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

                Comment


                • #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.

                  MCM
                  Michael Mattias
                  Tal Systems Inc. (retired)
                  Racine WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment


                  • #10
                    PBExcel.inc

                    Tom,

                    Sorry I Hadn't checked back in awhile. The PBExcel.inc 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 PBExcel.inc. 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.
                    Fred
                    "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

                    Comment


                    • #11
                      Thanks

                      Although I did simply save the excel to csv, I also used several of Fred Harris' samples and his method to create PBEXCEL.inc 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.

                      Comment


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

                        The source code forum has quite a bit for Excel.
                        http://www.powerbasic.com/support/pbforums/search.php

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

                        Comment

                        Working...
                        X