Capture File Name on Import

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?
 
G

Guest

If you name the tab at the bottom of your spreadsheet, it will carry that
over to the table in ACCESS.
 
G

Guest

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?
 
J

John Nurick

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$];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top