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
- 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