Question Help with using Excel linked tables

[CazB]

!! WARNING - LONG POST!!

I'm having a few issues with a database I've set up....
Each week I receive downloaded data in Excel - this is supplied to us in a pre-defined format from our client(s) (the data is actually timesheet information for Contractors)

When we receive the data, it arrives as one 'row' per timesheet, and then as well as the info about the contractor, one column for each possible rate, and one column for each no of hours claimed. There are over 70 different rate / hours codes, so the spreadsheet has over 150 columns, the majority of which have 0 in them!

What I need to do is to transform the data into a different format - like the report in the attached file.

To do this, I am currently using the spreadsheet as a Linked Table in my database, and using various queries (including a couple of union queries) to swap it around into the relevant format.

The way I'm doing it now works absolutely fine - until the information on the spreadsheet is changed (rates or hours are added, deleted or renamed) - and then I have to go through and redo it all again!

So, my question is:

Is there a way of examining the field names, and creating the values I need from them?
On the spreadsheet, when looking at a particular rate code eg [Overtime Weekend Daily/Day], there are 2 columns I need to look at and 'match' to each other - one for the Rate: Bill Rate [Overtime Weekend Daily/Day] and one for the hours / days claimed: Time Sheet Billable Hours [Overtime Weekend Daily/Day]
On the report, these would need to be shown as
RateType: "Overtime Weekend Daily"
RateCategory: "Day"
Rate: Bill Rate [Overtime Weekend Daily/Day]
Billable: Time Sheet Billable Hours [Overtime Weekend Daily/Day]

... or is there another way altogether that I could do this?

The ZIP file contains a stripped down copy of the database and a sample spreadsheet - just reattach the spreadsheet once you've downloaded it, call it Timesheets, and it should all work!

 

[Rx_]

B.T.W. we also have an Excel site where some of this has been discussed.
In a nutshell, yes.
Using Remote Automation (VBA) my standard is to scan individual cells in Excel, validate the number of rows, validate the values and so on (kind of like you are doing manually) then move a cell at a time over to a Record / Field at a time.
It provides atomic control with a business layer (vba functions or maybe just case statements) inbetween.

Basically, a cursor in Excel can read the header (first row) and evaluate if it has changed.
Of course, when the source changes things, they create Change Management which cost. Still, if a header name is the same, but put into a different column, the process of writing more code to identify the name and column position can help.

Sorry I wont' have time this week to help as there is a big Access convention here in Denver starting tonight. However, if you post this in the VBA or Excel forums, there should be more attention paid to this question.
You can also search from Rx_ post as I have a few Excel sniplets that might give you an idea how to use VBA code and Remote Automation with Excel Object Model Programming.

 

[CazB]

ok, will have a root around and see what I can find.... ty