Hello,
I have a big collection of weekly spreadsheets I'd like to import into one big table. That is no problem, I know how to loop through imports in a folder. The issue I have is I'd like to add an identifier of each tab and file. So for the basic code I am thinking:
Code:
[Initialized variables etc etc]folder = [where files are]
filename = Dir(folder & "*[common string in file names]*")
Do While filename <> ""
For each element in Array(
[list of worksheet names])
DoCmd.TransferSpreadsheet acImport, 10, [Access Table], folder & filename, True, '[element]'![copy range]
[Add worksheet name to extra field]
[Add piece of file name to another extra field]
Next element
filename = Dir()
Loop
So the output would look like this:
Imported Data Headers |
Worksheet Name |
Workbook Name |
Imported Data |
WSName1 |
WSFile1 |
Imported Data |
WSName2 |
WSFile1 |
Imported Data |
WSName1 |
WSFile2 |
Imported Data |
WSName3 |
WSFile2 |
Imported Data |
etc. |
etc. |
Any ideas?