Reading Excel Spreadsheets

[John Schexnaydre]

I have a need to read some very simple Excel spread sheets to extract
data.

I know nothing about COM and the COM Excel Samples will not work with my version
of Office XP.

I looked at the COM examples in these forums, but they all seem to require
certain versions of Excel to be installed.

Before wasting time coming up tp speed with COM, can this ever work out in the
field when I have no idea what version of Excel will be found? Also it seems
if the user didn't install all of Excel, it may fail as well.

Any simple way of reading Excel not knowing what might be installed?

Thanks
John


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

[Edwin Knoppert]

there are 3 approaches.

1) having excel installed and running a doc with a macro (increases user fun).
2) the com way also having excel installed as in the examples dir.
3) or http://www.powerbasic.com/support/pb...ad.php?t=23919
which only gives you access to the excel structures which are a pain to make it work.


------------------
http://www.hellobasic.com
freeware and commercial tools for powerbasic
pbsoft - netherlands

[Russ Srole]

John,

I recently did a job for one of the TV networks & they wanted me to read part of an Excel file. We determined that the most cost effective way of handling it was to have the user invoke a macro that selected the data, copied it to another "sheet" and saved that sheet as a comma delimited file. Reading that file is a snap. My point to them was that if/when the format or com interface to excel changes, they don't have to come back to me for more changes and if they change the spreadsheet, then all they have to do is change the macro. Cost effectiveness won out and that's how we did it.

Russ Srole

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

[John Schexnaydre]

Thanks for the suggestions.

Having the user do anything is not an option. That is the current
method. The users cannot hold the shift key and press another
key at the same time much less select data and export or run a macro.

The goal is avoiding having the user to do anything.

Thanks
John

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

[George Bleck]

Simple answer...

Remove it from Excel then and right a PB app to do all the work.

------------------
Every day I try to learn one thing new,
but new things to learn are increasing exponentially.
At this rate I’m becoming an idiot faster and faster !!!
------------------
George W. Bleck
Lead Computer Systems Engineer
KeySpan Corporation
My Email

[John Schexnaydre]

Wish I could. Data comes from a 3rd party.

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


[This message has been edited by John Schexnaydre (edited February 10, 2004).]

[Chuck Hicks]

You might consider shelling vbscript, which has a MUCH simpler
COM interface than PB...
http://www.rlmueller.net/Programs/XLRead.txt


This simple vbscript example modifies the above code to extract
the first 10 columns of a worksheet of any number of rows, and
dumps them to a .csv file. The first two rows are assumed to
be headers. Run with cscript to test, shell with wscript to
avoid opening DOS window.

[CODE]
Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim objExcel, objSheet, fso
Dim sExcelPath, sCsvPath, hFile
Dim iRow, iCol, sRow, DQ

DQ = Chr(34)

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
Wscript.Quit(Err)
End If

On Error GoTo 0

sExcelPath = "C:\temp\Inventory.xls"
sCsvPath = "C:\temp\Inventory.csv"

Set fso = CreateObject ("Scripting.FileSystemObject")
Set hFile = fso.OpenTextFile(sCsvPath, ForWriting, True)


' Open specified spreadsheet and select the first worksheet.
objExcel.WorkBooks.Open sExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Iterate through the rows of the spreadsheet after the first, until the
' first blank entry in the first column.
iRow = 1

Do
sRow = ""

'-- Extract columns 1-10 of each row
For iCol = 1 To 10
'-- Convert to CSV string
sRow = sRow & DQ & objSheet.Cells(iRow,iCol).Value & DQ
If iCol < 10 Then
sRow = sRow & ","
End If
Next

'-- Write the csv string to the output file
hFile.WriteLine(sRow)

iRow = iRow + 1

'-- Exit when no value in first column (after row 2)
If iRow > 2 And objSheet.Cells(iRow,1).Value = "" Then
Exit Do
End If
Loop

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
hFile.Close

{/CODE]

[This message has been edited by Chuck Hicks (edited February 10, 2004).]

[Edwin Knoppert]

Is equal to use Vb or Excel VBA imo.

For long time i used a Excel sheet having a macro to save to desired filetype.
The only problem we had was that it wan't always poss. to bypass the macro question.
I did this in the calling exe by modifying the registry for a short while.

We currently use com to do so.
O btw, i have a commercial lib on my site named XLTable.
It will open your excel sheet but requires Excel i'm afraid..


------------------
http://www.hellobasic.com
Freeware and commercial tools for PowerBASIC
PBSoft - Netherlands

[Eric Pearson]

Lots of people use my company's SQL Tools product to open Excel files in PB/CC and PB/Win programs. Excel itself does not even have to be present on the target machine in any form; SQL Tools can open an Excel sheet directly, as long as some version of ODBC is in place (Win 98 and above, or NT4 and above). For more information, click on one of the links below.

-- Eric Pearson, Perfect Sync Software

------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>

[John Schexnaydre]

Well I do own a copy of SQL Tools and I will see if that works. I
don't remember seeing any samples to access Excel. Any available?

Having Excel installed is not an issue, it knowing how to work with
each version.

I once found some code to have Word print a RTF file and exit.
If I could just send Excel a commnad to File/Save as this
would be enough.

Thanks All!

John



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

[Michael Mattias]

well i do own a copy of sql tools and i will see if that works. i
don't remember seeing any samples to access excel. any available

don't know if sql tools has some kind of 'automatic' detection and setup, but iirc to use excel as an odbc datasource you have to make the 'table' = a 'named range' of cells. i futzed around with this for several hours, but once i had the datasource set up and the named range defined in the worksheet it worked fine. (i am terrible with excel, so i'm sure it took me a lot longer than it would take someone who knows what they are doing).

of course, for the "old far.." er, make that "ancient warriors," there's always dde...
a simple dde client example july 02, 2001

mcm




[this message has been edited by michael mattias (edited february 10, 2004).]

[John Hackl]

how to avoid version dependence is the last message here: http://www.powerbasic.com/support/pb...ad.php?t=23631


"excel sheet to tab delimited file using com" source: http://www.powerbasic.com/support/pb...ad.php?t=23807

compiled is here: www.awfullysimple.com/sheetstotab.exe

to use:
sheetstotab c:\temp\book1.xls

[Michael Mattias]

>The users cannot hold the shift key and press another key at the same time

I guess the computer age has affected the language more than I thought.

We used to say they couldn't "walk and chew gum" at the same time...

[Steve Miltiadous]

Originally posted by Michael Mattias:
>The users cannot hold the shift key and press another key at the same time

I guess the computer age has affected the language more than I thought.

We used to say they couldn't "walk and chew gum" at the same time...

Some users cannot walk, never mind the gum part.

Steve

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

[Sean Roe]

Hi John,

Have you tried to use ADO to connect to the Excel spreadsheet? I have tried
this approach before and as long as the spreadsheet is setup fairly simple,
(ie the first row contains column headers and the rest of the rows are data)
then this might be a possible solution. The following is a good article on using
ADO with Excel: http://support.microsoft.com/default...257819#Connect

I created a JellyFish plugin that I have used to look at an Excel spreadsheet but
have not fully tested it against an Excel datasource. I know that the Extended properties
need to be set to be successful at getting the data from Excel using ADO.
If you have JellyFish you can check it out on the JellyFish plugin page.

I haven't tried to use SQL Tools against an Excel data source but I would think
that SQL Tools would give you a good solution as well.

Hope this helps,
Sean

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

[John Schexnaydre]

Thanks all for the many great suggestions.

The simple SheeetToTab may be the easiest, but I do have SQL Tools,
and the other items to try.

Thanks again to all for the most excellent help.

John

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

[Eric Pearson]

John --

We don't have any Excel-specific examples because ODBC (and therefore SQL Tools) are supposed to be DBMS-agnostic. You are not supposed to have to worry about which DBMS you are using.

In reality however Excel is one of the quirkier formats and we have documented a number of things to watch out for, in the SQL Tools Help File. Search for Excel and you should see a number of items, mostly in Appendix A.

-- Eric


------------------
Perfect Sync Development Tools
Perfect Sync Web Site
Contact Us: mailto:[email protected][email protected]</A>

[Erik Christensen]

john,

i know i am a little late. i think you may even consider using
the clipboard for transferring the data as in this program:

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

when you copy selected data in excel to the clipboard, they will
be in tab-separated text format. for cells with formulas, just
the result will be copied. the tab-separated text can very
easily be pasted into any pb program using the routines in
the program above.

best regards,

erik

p.s. another simple approach would be to save the excel data in
tab-separated text-format. this option is a built-in standard
in any excel program and this standard format can be read by
almost any other program. in the link below there is a routine
to read such data to be used in a pb program. good luck.

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

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




[this message has been edited by erik christensen (edited february 17, 2004).]