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

Beginner, wanting a code to import multiple spreadsheets

P: 9
Hi
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,
BEP

Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel()
  2.  
  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 = ""
  12.  
  13. End Function 
Dec 4 '09 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 636
Hi

Try this mod
Expand|Select|Wrap|Line Numbers
  1. Function Impo_allExcel() 
  2.  
  3. Dim myfile 
  4. Dim mypath 
  5. mypath = "J:\PLANNING\Chart\Excel Test Data\" 
  6.  
  7. myfile = Dir(mypath & "*.xls") 
  8.  
  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 
  14.  
  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.

HTH

MTB
Dec 4 '09 #2

P: 9
Mike,
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.