Include the spreadsheet name as the first field when importing multiple spreadsheets

[cloa]

I have seen the answer for importing multiple spreadsheets though I haven't tried it. How do I include the spreadsheet filename as the first field for all rows. By the way how does this handle if the workbooks are sometimes formated as one worksheet and sometimes two worksheets. The format is generally very similar.

Function Impo_allExcel()
 
Dim myfile
Dim mypath
mypath = "n:\importxls\aramiska\"
Do
myfile = Dir(mypath & "*.xls")
'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "aramiskaimport2", mypath & myfile
myfile = Dir
Loop Until myfile = ""
 
End Function  
 

After this task I going to work on merging the data many records when some have a common name (field) which I want as one record perhaps in a separate table with the unique data all together- there is a lot of duplicate data.

Thanks in advance

[nico5038]

You have two options:
1) Import all in the same table and have the additional filename column defined.
Afeter the import perform an UPDATE of all rows where the filename column is empty with the filename.
2) Make for every file a different table with the filename and merge them e.g., using a UNION with the filename as additional column.

Nic;o)

[cloa]

Thanks for your reply but could you indicate in either solution how I can automatically gather the spreadsheet name into the database so I can say that this information that I am checking comes from that spreadsheet (they want to keep the spreadsheets). Most spreadsheets are unchangeable data with new updates put on the January spreadsheet so linking is not necessary.





Telling the whole story. There is a html database at work which I suspect is SQL but it is so badly conceived that the information I am checking is not unable to derived from it so they use a spreadsheet system instead (for this data). That is has about 36 files with the last 7 having the same type and format of data as the previous 29+ files but its spread over two worksheets with slightly different header row (one name different) on the second sheet.

[nico5038]

You're collecting the files using the name with "mypath & myfile", these can be "stringed" into e.g,. an UPDATE query.

Another option might be to link to the sheets to tables instead of importing them and refresh the links. Thus the table names can be "stable" and you can build your needed (UNION and/or APPEND) queries on these.

Nic;o)