Import Variable data from Excel Spreadsheet?

[bschmitt13]

I have a situation where data comes in Excel Spreadsheets from sources who occasionally make changes to it's structure. Sometimes the column name will be changed, sometimes a new column will be added somewhere and things like textual explanations for missing numbers keep things interesting. If I could read the column headings I could test their values and figure out what I have. But until I read them I don't even know how many columns will be found.Can anyone suggest an approach or point me to some code?

Thanks,

Bill

 

[spikepl]

You could import the lot as-is into a temporary table using the TranferSpreadsheet method, with HasFieldNames set to True, and then read the column names in that table using this http://allenbrowne.com/func-dao.html#ShowFields

You'll probably get import errors for the data, if the data randomly switch between numbers and text, or a numbers-column can become text in the temp table, but you can deal with that in round two.