Beginner, wanting a code to import multiple spreadsheets

[BlackEyedPea]

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

Function Impo_allExcel()
 
Dim myfile
Dim mypath
mypath = "J:\PLANNING\Chart\Excel Test Data\"
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, "tblMasterChartData", mypath & myfile
myfile = Dir
Loop Until myfile = ""
 
End Function 

[MikeTheBike]

Hi

Try this mod

Function Impo_allExcel() 
 
Dim myfile 
Dim mypath 
mypath = "J:\PLANNING\Chart\Excel Test Data\" 
 
myfile = Dir(mypath & "*.xls") 
 
Do  Until myfile = ""
'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, "tblMasterChartData", mypath & myfile 
myfile = Dir 
Loop 
 
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

[BlackEyedPea]

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.