469,903 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Beginner, wanting a code to import multiple spreadsheets

I have no coding experience but am using access 2003 on XP in the hope that I can find some code that will....

Search a folder in my network & import any excel spreadsheets it finds within that folder, putting the data into one large access table. All the spreadsheets have the same layout although for some bizarre reason (I didn't archive them you see!!!) they have different file names.

I have already found some code which I include here, which works but only partly....
1) It runs & imports only the 1st spreadsheet it finds within the folder.
2) It continually imports this spreadsheet over & over until I eventually have to CTRL + ALT + DEL & close access.

So I am close to being able to do what I want, but becuase I'm so inexperienced in dealing with codes I can't quite see what I need to change on the code I've found to make it work exactly as per my needs.

Any help (please bear in mind answers will need to be explained as though discussing with a small child :)) will be apprecited.
Thanks again,

Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel()
  3. Dim myfile
  4. Dim mypath
  5. mypath = "J:\PLANNING\Chart\Excel Test Data\"
  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, "tblMasterChartData", mypath & myfile
  10. myfile = Dir
  11. Loop Until myfile = ""
  13. End Function 
Dec 4 '09 #1
2 2772
637 Expert 512MB

Try this mod
Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel() 
  3. Dim myfile 
  4. Dim mypath 
  5. mypath = "J:\PLANNING\Chart\Excel Test Data\" 
  7. myfile = Dir(mypath & "*.xls") 
  9. Do  Until myfile = ""
  10. 'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want. 
  11. DoCmd.TransferSpreadsheet acImport, 8, "tblMasterChartData", mypath & myfile 
  12. myfile = Dir 
  13. Loop 
  15. End Function  
You need to move the initial Dir() OUTSITE the loop.

As it is it just reinsates the first file in the list and, hence never move past the secon in the list, giving rise to the endless loop.

Not: I've also moved to loop test to the begining.


Dec 4 '09 #2
You have probably just saved me hours of work, just populated the code into access & it worked. Thanks so much, given me even more reason to start learning as much as I can about coding to help me solve these & other issues throughout the working day. Cheers again.
Dec 4 '09 #3

Post your reply

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

Similar topics

reply views Thread by Phil Perrin | last post: by
5 posts views Thread by Johnny Meredith | last post: by
22 posts views Thread by ddg_linux | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.