......how do I select multiple Excel files to import into Access?
Any insights would be deeply appreciated.
3 7830 MMcCarthy 14,534
Recognized Expert Moderator MVP
......how do I select multiple Excel files to import into Access?
Any insights would be deeply appreciated.
Are all these files being imported into separate tables or the one table?
Are all these files being imported into separate tables or the one table?
Moving them all into one table would be preferable; each source file is approx. 300 rows and contains data on a monthly basis from January, '95 to Current. I hope I'm telling you enough to answer the question.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Try something like this ... - Dim strFile As String 'Filename
-
Dim strFileList() As String 'File Array
-
Dim intFile As Integer 'File Number
-
Dim path As String
-
Dim filename As String
-
Dim strSQL As String
-
-
path = "path to your folder containing the files"
-
Set db = CurrentDb
-
DoCmd.SetWarnings False
-
-
'Loop through the folder & build file list
-
strFile = Dir(path & "*.xls")
-
While strFile <> ""
-
'add files to the list
-
intFile = intFile + 1
-
ReDim Preserve strFileList(1 To intFile)
-
strFileList(intFile) = strFile
-
strFile = Dir()
-
Wend
-
'see if any files were found
-
If intFile = 0 Then
-
MsgBox "No files found"
-
Exit Sub
-
End If
-
-
'cycle through the list of files
-
For intFile = 1 To UBound(strFileList)
-
filename = path & strFileList(intFile)
-
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "TableName", filename, True 'replace TableName with your table name
-
-
Next intFile
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 - and dump everything into a
table.
The problem is once I have the data imported into a new table, I can't do
much with it. If I try to run an Append query and insert data from the new
table into an existing table, the query fails - "Error...
|
by: amy |
last post by:
Hi, all:
i am a new end user of access, now I have many excel files need to
import to One table in access (combine all excel files into one table
in excel). In excel files, some columns will have both # and text in
the same column. The coloumn names in each excel file are mostly same
but still a lot of different names(items). I was told to set up import
specification, but how? i am not a programer or not access
professional, please help...
|
by: MD |
last post by:
Hello,
I need to import a sheet of 884 different excel-file with same lay-
out.
The sheet name is 'Totaal' and is the same in all different files.
Is there a script (module) in order to:
1. create a table with all the different names of the 884 files
2. create the import queryfor the 884 different files, but also
mentioning the file name of the excel-file in a colomn
|
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 spreadsheets to the same table. I'm not getting a formatting error, just a "file did not import" message.
Also, when I do import one-to-one, Access inserts thousands of blank lines before the first line of imported data. How do I keep Access from doing...
|
by: shenkel55 |
last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an import and the Excel file isn't open I get the following error: "The wizard is unable to access information in the file "...path info... "Please check that the file exists and is in the correct format." If the files are opened directly in Excel, it...
| |
by: hushens10 |
last post by:
Access Naive - Help Needed
I am using Access 2003 and Excel 2003. My goal is to create a database for a school where each student's testing information can be stored and organized so that it can be pulled. Each teacher currently has their own Excel file, into which they have entered their student's data. However, for the purposes of other departments, they would like a database file where information can be culled and queries can be run (thus...
|
by: ivancycheng |
last post by:
I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm very new in using VB). Many Thanks!
|
by: provor |
last post by:
Hello,
I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this one table. My problem is I have two buttons I want to use this code for for the two buttons would put the data in different tables. I have tried copying and changing a few things and nothing will work for me. The code is set up in a module and then I...
|
by: BlackEyedPea |
last post by:
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |