Read fiirst line excel spreadsheet. (1 Viewer)

pekajo

Registered User.
Local time
Today, 20:02
Joined
Jul 25, 2011
Messages
132
Hi,
I have an issue where I need to import an excel sheet and then query the table. However they keep changing the headings of the spreadsheet and I then need to change my vba code.
I was thinking if I could read the first line of the spreadsheet (which has the field names), then use this information to create all the fields in my table.
Any ideas of how it can be done.
Peter
 

pekajo

Registered User.
Local time
Today, 20:02
Joined
Jul 25, 2011
Messages
132
Hi,
Once I wrote this I had an idea that worked. Import the spreadsheet and read the table field names from there.

Peter
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:02
Joined
Sep 21, 2011
Messages
14,037
Do they change the order of the columns? :(
If you link to it, you can read the first record and get the contents on each field, then work from there.?

Though I would ask, if last week the first field was 'InputDate' and this week it is 'Date of Input', how are you meant to know that is the same data.?

There has to be some consistency?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Feb 19, 2013
Messages
16,553
I was thinking if I could read the first line of the spreadsheet (which has the field names), then use this information to create all the fields in my table.
Any ideas of how it can be done.
you could use transferspreadsheet setting the with headers parameter to false. The headers will then come through as the first row of the recordset. This also solves the potential issue of different datatypes in a column since all columns will be text so you can use cdate, clng etc to convert accordingly.

However as as been said - how do you know that a column called one thing one week and another the next are the same thing?
 

pekajo

Registered User.
Local time
Today, 20:02
Joined
Jul 25, 2011
Messages
132
Hi,
Thanks for your reply. Your idea is a good one will test tomorrow.
Off the 15 fields only 5 are required for massaging the data. I can check these 5 fields to see if they have been changed. Of all the other fields only 6-7 are required for display purposes.
Peter
 

Users who are viewing this thread

Top Bottom