Reading an Excel file into a recordset (VB6)

Topic

New Reply

(Edited by HansV to activate URL – see Help 19)

I have a spreadsheet which I need to import into a table.
I’ve had a look round to find the best approach for doing this. These are my limitations:

The file has a top row which is a title in A1 which spreads across all columns below it.
The file has a 1st row (after the title row) with column headers in it.
If the date field is null the row gets ignored.
If the date field is null but the dfe number doesn’t appear in the establishments table, the row goes into the tagging database,
else the row goes into the reportslookup database.

I looking into reading it in line by line and I looked into opening the file as a recordset, most of which seem to use .net which I don’t have and know nothing about:
http://msdn.microsoft.com/library/default….IntoDataset.asp[/url]

RE: References to the System and System.Data namespaces.
No idea what this means!

Either way i’m not sure how opening it as a recordset would deal with the 1st line.

Can anybody suggest the best approach to go for, it might save me a bit of time looking into one only to find its inappropriate!
Any help would be much appreciated.

Reply | Quote

Replies

Phil

I use ado to connect, It has an undocumented feature when connecting to excel in that it always assumes the first row returned contains field names. The formatting is ignored so the recordset would think that column a had a name and column b was called null etc

I would think you would solve your problem by
opening the recordset
moving the curser to the first row of actual data.
looping till end of file with a do…loop until
refering to your columns by their indexes instead of their names

does this help. attached is some code with a quick connect to an excel file

Reply | Quote

Thanks for your reply, looking into it now – i’ve never programmed in Word before and I didnt realise it was that easy and flexible! Thanks for opening my eyes to that as well. I’ll post back with how I get on with your code.

Reply | Quote

Wow – that worked like a treat, thanks for such a great example Doj.

I can’t believe it is so close to the code to open an Access or SQL recordset!

Thanks for all your help again Doj.

Reply | Quote

Nice to be able to put something back

This is where I learnt how to DO my job

Reply | Quote