Importing excel whilst adding a field to table (1 Viewer)

Dandandanf

New member
Local time
Today, 14:43
Joined
May 25, 2020
Messages
1
Hi, really hoping someone with some experience can advise.

I’m trying to import around 500 excel files into access each one being records from a particular day. I need also need to add a date field to the access table. The excel files contain the date reference in the file name only.

I’m not necessarily looking for the code to do this, only what steps you would recommend as the most efficient way of doing this. I’m a beginner at access but keen to learn and ok at vba.

Any suggestions would be much appreciate as I’m out of my depth at the moment.

thanks

dan
 

June7

AWF VIP
Local time
Today, 06:43
Joined
Mar 9, 2014
Messages
5,423
Importing to existing table that already has this date field?

Import the records then run an UPDATE action to populate date field.

UPDATE tablename SET fieldname = somedate WHERE fieldname Is Null;

Code can automate cycling through folder of files, import data, run UPDATE, move to next file, repeat.
 

isladogs

MVP / VIP
Local time
Today, 14:43
Joined
Jan 14, 2017
Messages
18,186
Link your Excel spreadsheets. Create your Access table with the relevant fields plus the date field.
Next, if you need to do any intermediate data processing, first append the data for each of a temporary imported data table and populate the date field based on the Excel spreadsheet name
Otherwise append direct to your final table.

Having tested this successfully on one or more Excel files, create a procedure to loop through your Excel files in turn with the procedure used as above
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:43
Joined
Sep 21, 2011
Messages
14,044
Well I am not an expert, but I'd probably approach it like this
Walk through all the files in the folder
TransferSpreadsheet acImport
Get the date from the filename and update with a query.

As I am writing this I can see that June7 has said pretty much the same, so I was on the right track. :)
 

isladogs

MVP / VIP
Local time
Today, 14:43
Joined
Jan 14, 2017
Messages
18,186
I see June, Gasman and I are all saying similar things.
I suggested append queries as I thought you wanted to add data from each Excel file as separate record(s)
However, use update queries if you just want to update existing records
 

Isaac

Lifelong Learner
Local time
Today, 07:43
Joined
Mar 14, 2017
Messages
8,738
based on your original post it sounds like your plan may have been to add a new column to the access table for each file you import, which I would not recommend. I would recommend having a date field and appending records to it rather than adding a new field to the table over and over..
 

Users who are viewing this thread

Top Bottom