Linking multiple Exel workbooks

[Guest]

We are currently using an excel spreadsheet to keep track of our daily energy
sales and purchases. Each day is saved in a separate workbook by the date
(i.e. data for Aug24 = 082406, etc)

I would like to use Access for the after-the-fact data and analysis. I’ve
tried to use “Get External Data†and “link tablesâ€, which works fine for each
day, but then I end up with a number of separate linked tables, when I would
like all the data to be in one table. Is there a way to do this, or am I
using a totally wrong approach??? I really need to link rather than import
the data, as changes may be made to the Excel worksheet. Any help would be
appreciated!!!

We are currently using Office2000 products.

Patti

 

[John Nurick]

Hi Patti,

A UNION query will combine multiple tables with the same structure into
a single recordset. This works fine for a small number of tables but
gets slow and clumsy with more - especially if you're adding 30 new
worksheets every month. There's probably also a limit to the number of
tables you can UNION together. I can't find documentation, but it could
be 32, the number of tables Jet allows in an ordinary query.

Within whatever that limit is, you can simplify the mechanics by cutting
out the explicit creation of linked tables and instead using syntax like
this to link a query directly to the workbooks:

SELECT *
FROM
[Excel 8.0;HDR=YES;Database=C:\Book1.xls;].[Sheet1$]
UNION ALL
SELECT *
FROM
[Excel 8.0;HDR=YES;Database=C:\Book2.xls;].[Sheet1$]
UNION ALL
SELECT *
FROM
[Excel 8.0;HDR=YES;Database=C:\Book3.xls;].[Sheet1$]
UNION ALL
...
;

Doing it this way makes it reasonably simple to write VBA code that will
let the user input a range of dates and then construct the corresponding
SQL statement.

But a better approach - especially if you want to run queries on more
than a couple of weeks' data - would IMHO be to import all the data into
one table as it arrives, and set up a mechanism that would monitor
subsequent changes to the workbooks.

I'd do this with a second table with fields like this:

Workbook* (e.g. 082406.xls)
ImportedDateTime (the date and time on which
data from this workbook was last imported)
ModifiedDateTime (the 'last modified' timestamp
from the workbook file at the time it was
imported)

Then I'd write VBA code that scanned this table and compared the stored
ModifiedDateTime for each workbook with the actual timestamp on the
file. If the current value is later than the stored value, it means the
workbook may have been edited; in that case the code would either alert
the user or go ahead and run queries to (a) delete existing records for
that date from the main table and (b) import the revised data from the
workook.

This isn't foolproof, because it offers no protection against accidental
changes to the Excel data. From that point of view - and for data
integrity in general - the only safe approach is to change your workflow
to make the database the authoritative store.