excel 2000 to text file

[tom kroto]

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

[Roy Cline]

http://www.powerbasic.com/support/pb...searchid=75235

[Kerry Farmer]

Roy

This link does not work

Thanks

[Sergio Tallone]

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

[Fred Harris]

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.

#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

[Guy Dombrowski]

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

[Fred Harris]

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.

[tom kroto]

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?

[Michael Mattias]

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

[Fred Harris]

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.

[tom kroto]

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.

[John Hackl]

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.