Read data from Excel Files (in PB for Windows)

[Tony Lea]

I am new at this game having programmed several prgrams that
solve statistical problems. But I now need to both read and write
data (usually simple data file of reals or integers) from Excel.
I just searched this site and found something that helps write
Excel files, but nothing regarding reading them. Is this
straightforward, is there a subroutine, or is there an
add-on product I can get? Thanks. Tony Lea

------------------

[Paul D. Elliott]

There are at least a couple ways.

If you search the forums for "Excel COM", you'll find several
bits of code to show how to read or write Excel via COM. Check
the last years worth of messages to get started.

Or you can use Perfect Sync's SQL Tools. they make it very easy
to access data in Excel ( and databases ).

You might want to get a copy of POFFS so that you can do a lot
of searching & cutting/pasting of samples when you're off-line.

Hope this helps a little bit.


------------------

[Fred Harris]

Tony,

Under my installations of PBCC40, PBWIN70 and PBWIN80 are
the following folders...

PBWINXX\Samples\COM\Excel\

In the Excel folder are several sample programs provided by
PowerBASIC that very simply show what you want. That's how most
of us that learned how to do this PowerBasic COM stuff learned.
You need at least version 7 or v4 of the console compiler to do
this stuff. I'd advise your trying to learn it from those
sample programs. It is quite easy. If you have any difficulties
post back as many here will be glad to help.

------------------
Fred

[Tony Lea]

Thnks very much both of you.
I have followed up your suggestions and am making progress.
I had hoped it would be more straightforward to read Excel (versus writing to it).
Tony Lea

------------------

[Ken Myers]

Here's a library that may help you quite a bit from Edwin:
http://www.hellobasic.com/cgi-bin/fo...num=1117224621

I think it is great for reading Excel files as long as the computer your app will run on has Excel installed. You should at least take a look.

------------------

[Fred Harris]

Tony,

Just dug up a learning program I made when I was trying to
figure out this somewhat confusing stuff couple years back. The
program first opens up a text file to print output to in the
program's directory. If the version of Excel you have is 8 or 10
you'll want to change the "Excel.Application.9". I believe you
can use a version independent strProgID. See the documentation.
Also, you'll want to change the paths to reflect the files and
directories you're playing with on your machine. The program
also shows how to select a specific worksheet in a workbook.
Hope this helps.

Function PBMain()
  Dim oExcelApp AS ExcelApplication
  Dim oExcelWorkbook AS ExcelWorkbook
  Dim oExcelWorkSheet As ExcelWorkSheet
  Dim vFileName As Variant, vVnt As Variant,vRange As Variant,vText As Variant
  Dim strFileName As String
  Dim fp As Long

  fp=Freefile
  Open "Output.txt" For Output As #fp
  Set oExcelApp = New ExcelApplication In "Excel.Application.9"
  If IsFalse IsObject(oExcelApp) Then
     MsgBox("Problem Opening Excel!")
     Exit Function
  End If
  'strFileName = "C:\Tallies\RawData\13200211.xls"
  strFileName = "C:\PBWin70\MyProjects\COMWork\2004DFPermits.xls"
  vFileName = strFileName
  Object Call oExcelApp.WorkBooks.Open(vFileName) To vVnt
  Set oExcelWorkBook = vVnt
  'vVnt = "FMT-19"
  vVnt="2004DFPermits.xls"
  Object Call oExcelWorkbook.Sheets(vVnt).Select
  Object Get oExcelWorkbook.ActiveSheet TO vVnt
  Set oExcelWorkSheet = vVnt
  'vRange="R6"
  vRange="F13"
  Object Get oExcelWorkSheet.Range(vRange).Value To vText
  Print #fp, "Variant$(vText) = "; Variant$(vText)
  Close #fp
  Object Call oExcelApp.Quit
  MsgBox("Processing Complete!")
End Function

------------------
Fred