Import/Export Multiple Excel Files to Access

B

Beep Beep

I have roughly 500 invididual Excel files that are sent to me via the IT
department. Each file has roughly 5 - 10 thousand lines on them. What I am
wondering is, is there a Access VBA Macro that I could use to inport each one
of these 500 Excel files into a Access database while looping through them in
one Excel directory while I continue to do other things? The person that did
this last spend a day or so just importing them into Access and appending
them into one file. The final Access file contains apprsoximately 2.5 million
records.

PS: I think I originally posted this in the wrong site.

Thanks
Frank
 
B

Beep Beep

Thanks Ken, however here is what I have and it does not run. Can you see
where I am going wrong. When I step through it stops at blnHasFieldNames =
False

Sub ImportExcelMultipleFiles()

'End Sub
'Option Explicit
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet has
field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "F:\Charlotte Russe\Excel\January 2008 Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "FRANK"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Sub
 
K

Ken Snell \(MVP\)

Did you put a breakpoint on that code step?, andiIs it still there? If you
removed it, it may still be there because of VBA compilation error. Delete
that line of code, compile your code, then put it back in, and compile your
code again.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
B

Beep Beep

Ken

It appears that there was a breakpoint stopping it. I ran it just now but
nothing happened. By that I mean the macro ran all the way through, however
no files were Imported to Access.

Do I need a path leading to Access besides the table name, and where do I
start the macro within Excel.

Thanks
 
K

Ken Snell \(MVP\)

The code that I provided must be run from ACCESS, not from EXCEL. (You could
modify the code to work from EXCEL if you automate ACCESS from EXCEL and
modify the code the use the ACCESS object that you instantiate.)
 
B

Beep Beep

Ken thanks for getting back to me. I have copied the code into a mudule in
access however when I run it it goes through without doing anything. Sorry
to be do dim but my experience with Access Macros is within the macro
structure. Could you tell me what I am doing wrong. Sorry to be so vague.
 
K

Ken Snell \(MVP\)

Are you calling the code from somewhere within ACCESS? Usually, one might
use the click event of a command button on a form to run code like this, by
calling the sub:

Private Sub CommandButtonName_Click()
Call ImportExcelMultipleFiles
End Sub
 
B

Beep Beep

Hi Ken:

I will try to answer this again. After writing a dissertation I went to send
it to you and got a message that the service was down and lost all my
message. Here we go again. This time in word just in case.

Sorry to be so dense but my experience with Access macros is by using the
(New) macro process and then picking from the drop down boxes. i.e. Echo;
Maximize; Open query; etc. I have written so simple VBA macros in Excel and
just wrote them in the F11 section of excel but nothing like this in Access.

After getting your latest reply I copied and pasted the code into the VBA
section where the system put all the code for my other macros that I created
by using the New Macro process. I then created a command button and copied
the name of this macro into the On Click section as when I clicked on the …
section it did not show this macro????

When I click on the command button I get a message that the macro cannot be
found. As I mentioned above my Access macro experience is with using the New
Macro process. Would it be possible to do this process by using the New
Macro process or can you figure out what I am doing so wrong.

Thanks
Frank
 
K

Ken Snell \(MVP\)

Macros in ACCESS are not VBA code, but macros in EXCEL are VBA code. The
terminology can cause confusion.

To run the code that I provided in ACCESS, go to Modules in database window
of ACCESS. Create a new module, and name it modEXCELImport.

Paste the following code into that module -- note that this is the same code
that you had EXCEPT I have changed Sub to Function so that we can call it
directly from an ACCESS macro:


Public Function ImportExcelMultipleFiles()

'Option Explicit
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet has
field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "F:\Charlotte Russe\Excel\January 2008 Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "FRANK"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
_
strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function


Save the module.

Go to Macros in database window. Create a new macro, and name it
macEXCELImport. Put the following action in that macro:

Action: RunCode
Function Name: EXCELImport()

Save and close the macro.

Now, when you go to attach a macro to the Click event of a command button on
a form, the macro name macEXCELImport will show so that you can select it.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
K

Ken Snell \(MVP\)

Sorry, error in my info for the RunCode information. Use this:

Action: RunCode
Function Name: ImportExcelMultipleFiles()
 
B

Beep Beep

OK Ken I think we are getting closer, however here is the error message that
I get now: COMPILE ERROR; INVALID INSIDE PROCEDURE. This message comes at
the Option Explicit line. Attached is what my code looks like now.

Option Compare Database

Public Function ImportExcelMultipleFiles()

Option Explicit
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet has
Field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Excel\January 2008 Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "FRANK"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9
strTable , _
strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function
 
K

Ken Snell \(MVP\)

Option Explicit must be at the top of the module, for example, right under
the Option Compare Database statement. It cannot be inside a procedure in
the module.
 
K

Ken Snell \(MVP\)

The "Field nanes" text should be at the end of the line above it; your
newsgroup reader probably wrapped the full text onto a separate line.

Or use this code:

Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Excel\January 2008 Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "FRANK"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable , _
strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function
 
B

Beep Beep

Ken

The good news is that when I run the macro within the VBA screen it runs all
the way through but nothing happens. Same thing if I Step Through it.

The bad news is that when I try to run it from a Command button I get this
message:

The Expression you entered has a function name that Microsoft Office can't
find.

Suggestions.

Thanks
Frnak
 
K

Ken Snell \(MVP\)

Is the command button running a macro that is calling this VBA function? Or
are you trying to run the VBA function directly from the command button?
Please provide details about the setup on the form for the Click event of
the command button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merging from an Excel file with graphics 1
Embed the same Excel multiple times 0
import an Excel file to create a Gantt chart in Visio; Error:do not have the required mapped columns 0
batch print some specific pages in multiple word files 1
Need to export multiple files 3
filter and export to excel 4
Using TransferSpreadsheet to import from Excel to Access with variable file names 1
Importing multiple spread sheets 1

Top