473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Beginner, wanting a code to import multiple spreadsheets

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
2 2979
MikeTheBike
639 Expert 512MB
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
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

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

Similar topics

0
by: Phil Perrin | last post by:
------=_NextPart_000_0001_01C3663D.5E3EAD70 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I'm a relative newbie to MySql, and I've got a small project I'm working...
5
by: Johnny Meredith | last post by:
I have seven huge fixed width text file that I need to import to Access. They contain headers, subtotals, etc. that are not needed. There is also some corrupt data that we know about and can...
3
by: deko | last post by:
I've been trying to use the Access Import Wizard to expedite importing data into my mdb. The nice thing about the wizard is that I can import from different file formats - txt, xls, even Outlook -...
5
by: Java script Dude | last post by:
For those who are missing the feature on how to import into Open Office dBase app from text files and spreadsheets in OOO Base 2.0: A wizard exists to import from spreadsheets only at this time...
0
by: Diddley | last post by:
I want to import a large number of Excel spreadsheets that are 'supposed' to have the same number of columns representaing the same type of data into tables in an Access database. The UNC path and...
3
by: MarcGA | last post by:
(Excel 2003, Access 2003, XP, novice user here) I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23...
2
by: Dave Williams | last post by:
Im not a techie but run a small business in UK. We take instructions from clients from emails hard copy, spreadsheets etc and put them on excel templates then import to Access. A client wants us to...
5
Khriskin
by: Khriskin | last post by:
I have searched the forums (as well as msdn and other websites) and have not been able to find a solution. Any and all suggestions would be greatly appreciated! Problem I have two Excel 2003...
22
by: ddg_linux | last post by:
I have been reading about and doing a lot of php code examples from books but now I find myself wanting to do something practical with some of the skills that I have learned. I am a beginner php...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.