Hi,
I download a report weekly for a 10 clients, my aim is to create a mast table with all the data for all the clients to allow me to analyse and create summary reports. (the data is a CSV but I've been saving it to a XLSX)
In my basic world I created a series of import steps to create a table for each client.
Then I run a macro which uses an append query to add the data to a master table.
And there it fell over.
Turns out the downloaded report is dynamic and only contains relevant data for the time frame selected, so, if something didn't happen last week then several columns might be missing from the source spreadsheet.
My question is:
Can Access somehow extract the data I want from the spreadsheet by 'looking' at the column headings and just populating that data?
If there are columns missing in the spreadsheet then access just leaves those fields blank.
My knowledge of access is pretty limited, tables, queries and macro's so anything more than that I'll need idiots guide.
Many thanks
Tim
I download a report weekly for a 10 clients, my aim is to create a mast table with all the data for all the clients to allow me to analyse and create summary reports. (the data is a CSV but I've been saving it to a XLSX)
In my basic world I created a series of import steps to create a table for each client.
Then I run a macro which uses an append query to add the data to a master table.
And there it fell over.
Turns out the downloaded report is dynamic and only contains relevant data for the time frame selected, so, if something didn't happen last week then several columns might be missing from the source spreadsheet.
My question is:
Can Access somehow extract the data I want from the spreadsheet by 'looking' at the column headings and just populating that data?
If there are columns missing in the spreadsheet then access just leaves those fields blank.
My knowledge of access is pretty limited, tables, queries and macro's so anything more than that I'll need idiots guide.
Many thanks
Tim