Extracting Data From Changing Exel Worksheets

[drrocket5292]

Hey All,
I receive some excel spreadsheets each month from various vendors breaking down the business my company did with them the month before. I've written macros on them to manipulate them and return only the data my company needs. One of my main problems though is that these companies never consistently send them in the same format from month to month. For instance Product Information may be in Column A one month and Column C the next. So each month I have to go back and tweak the macros to account for these changes. I'm sure there are several different ways to deal with my particular situation like setting up an Xref spreadsheet and just changing the column numbers in it each month before running the macros but what I am more interested in is finding out if there is a broader approach or concept that I am missing.

So I guess what I am asking in general terms is: how is one suppose to program in VBA the extraction of data from dynamically changing spreadsheets? Thanks.

 

[SkipVought]

Hi,

You might try using MS Query. faq68-5829

The LOCATION of columns is immaterial. The column names just need to be consistent.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]