Capture File Name on Import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm importing multiple Excel spreadsheets into an Access table. It would be
nice to capture the name of the Excel file associated with the data in the
Access table.

Is that possible?
 
If you name the tab at the bottom of your spreadsheet, it will carry that
over to the table in ACCESS.
 
I'm sorry. That's what I do. Can you add a number or letter to the worksheet
name so you will know what's what?
 
Hi Kirk,

One way is to start by using File|Get External Data|Link to create a
linked table connected to the spreadsheet. Then use an append query with
a calculated field containing the file name to move the data into your
main table.

So if the spreadsheet has column headings AA, BB and CC, and your main
table has fields FileName, AA, BB, CC, the SQL view of the append query
will be something like this:

INSERT INTO MainTable
SELECT "Filename.xls" AS FileName, AA, BB, CC
FROM LinkedTable;

Or you can cut out the linked table by writing an append query that gets
its data directly from the spreadsheet, using syntax like this:

INSERT INTO MainTable
SELECT "Filename.xls" AS FileName, AA, BB, CC
FROM [Excel 8.0;HDR=No;Database=C:\MyWorkbook.xls;].[Sheet1$];
 
Back
Top