By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel()
  3. Dim myfile
  4. Dim mypath
  5. mypath = "n:\importxls\aramiska\"
  6. Do
  7. myfile = Dir(mypath & "*.xls")
  8. 'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
  9. DoCmd.TransferSpreadsheet acImport, 8, "aramiskaimport2", mypath & myfile
  10. myfile = Dir
  11. Loop Until myfile = ""
  13. 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
Jan 22 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
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.

Jan 23 '10 #2

P: 2
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.
Jan 23 '10 #3

Expert 2.5K+
P: 3,072
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.

Jan 23 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.