Using the name of an imported file in a query

[Clearlier]

Hello, first question so please be gentle! I think that I'm looking for a macro but if I'm better off following a different path please let me know.

I've got a folder with approximately 50 excel files with the name "Dataxxxxxxxx" where xxxxxxxx is a date in yyyymmdd format.

I need to create a single table out of that data and adding extra pieces of information which are different for each file.

I'm thinking that I could build a table (for all of the files) which has the extra pieces of information with one of the columns in the table having the filename as a value. If I could add the filename as an extra column to the imported data then I could link the two tables and use an append query to create my single table.

I'm confident that I can do this manually but I'm think that there must be a way to automate some or all of this process.

If I were to summarise it I think that I need to:

import a file
add the filename to the data
run an append query

I'm wondering if there's a way to automate it from start to finish as I'm probably going to have to do it for about 250 files in the end (5 years of weekly data). I'm thinking about iterating through a complete list of filenames.

I'd appreciate all of the help that anyone can offer.

 

[Clearlier]

Importing multiple files (more precise question)

Maybe I need to be a bit more specific.

I've got a macro that imports a data file. I have fifty of these files each with the data added to the file name.
I could rename each file to 'Data' and run an import for each individual file. Is there a way that I can import all of these files without having to do this? I have a table of the file names already.

I imagine that it's possible using VBA to pass the file name as a variable to the import and to iterate through the table of file names doing this but I don't know how to in Access.

Can anyone help?

 

[Frothingslosh]

If the files are all in one location, you can use Dir to cycle through the names of every file in that folder to do what you need.