Importing a folders worth of excel work books into Acess

C

chrisbrocco

Board Regular
Joined
Mar 31, 2005
Messages
82
Is there a way (using VBA) to point to a folder and make access import all the files (which will be excel files) into a access a table.

Prefrably beable to say which colums it imports
 
Sort by date Sort by votes

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
It seems like you are missing the proper table and sheet names in the transferspreadsheet method. This is not a robust solution (as to changing numbers of sheets and so forth) but hopefully it will help (note: I haven't read any but the last post).

Code: 
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] TestImport()
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] strSheet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
    [COLOR="Navy"]For[/COLOR] x = 1 [COLOR="Navy"]To[/COLOR] 15
        strSheet = "Sheet" & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "C:\Documents and Settings\abc\Desktop\ClearExtracts\core.xls", _
            True, _
            strSheet
    [COLOR="Navy"]Next[/COLOR] x

MsgBox "Complete."
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Handler:
MsgBox Err.Description
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
Last edited:
Upvote 0
Oops:
Code: 
        strSheet = "Sheet" & x & "!"

Should be:
Code: 
        strSheet = "Result " & x & "!"
 
Upvote 0
This works perfect, thanks alot xenou! I just have another question.. if i wanted to import mutlalso into the same command for example pref.xls/rel.xls workbook into pref/rel table... Would i just remove the public sub testimport/end sub? I highlighted them in red.

Code: 
Public Sub TestImport()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "U:\Projects\Clear Extracts\core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
[COLOR=red]End Sub[/COLOR]
 
[COLOR=red]Public Sub TestImport()[/COLOR]
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "pref", _
            "U:\Projects\Clear Extracts\pref.xls", _
            True, _
            strSheet
    Next x
MsgBox "Pref Complete."
Exit Sub
Handler:
MsgBox Err.Description
[COLOR=red]End Sub[/COLOR]
 
[COLOR=red]Public Sub TestImport()[/COLOR]
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "rel", _
            "U:\Projects\Clear Extracts\rel.xls", _
            True, _
            strSheet
    Next x
MsgBox "Rel Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub
 
Last edited:
Upvote 0
Hi,

Well, you can't have subs with the same name in the same module, but in general I think you have are correct.

There may be a way to generalize this further but in a pinch I'd create a sub for each import and if you need to you can call them all in turn (note: no effort here was made to be sure the three subs do the right thing):

Code: 
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Import_Stuff()
    [COLOR="Navy"]Call[/COLOR] Import_Result
    [COLOR="Navy"]Call[/COLOR] Import_Pref
    [COLOR="Navy"]Call[/COLOR] Import_Rel
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'-------------------------[/COLOR]
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Import_Result()
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] strSheet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
    [COLOR="Navy"]For[/COLOR] x = 1 [COLOR="Navy"]To[/COLOR] 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "U:\Projects\Clear Extracts\core.xls", _
            True, _
            strSheet
    [COLOR="Navy"]Next[/COLOR] x
MsgBox "Core Complete."
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Handler:
MsgBox Err.Description
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'-------------------------[/COLOR]
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Import_Pref()
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] strSheet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
    [COLOR="Navy"]For[/COLOR] x = 1 [COLOR="Navy"]To[/COLOR] 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "pref", _
            "U:\Projects\Clear Extracts\pref.xls", _
            True, _
            strSheet
    [COLOR="Navy"]Next[/COLOR] x
MsgBox "Pref Complete."
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Handler:
MsgBox Err.Description
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'-------------------------[/COLOR]
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Import_Rel()
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] strSheet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
    [COLOR="Navy"]For[/COLOR] x = 1 [COLOR="Navy"]To[/COLOR] 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "rel", _
            "U:\Projects\Clear Extracts\rel.xls", _
            True, _
            strSheet
    [COLOR="Navy"]Next[/COLOR] x
MsgBox "Rel Complete."
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Handler:
MsgBox Err.Description
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
Upvote 0
Hi again the code works but i still have to select within subroutine that i want to run and then click the run button 3 times, anyone have any other suggestions. This is currently the code.

Code: 
Public Sub Import_Stuff()
    Call Import_Result
    Call Import_Pref
    Call Import_Rel
End Sub
'-------------------------
Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 10
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "U:\Projects\Clear Extracts\core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub
'-------------------------
Public Sub Import_Pref()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 16
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "pref", _
            "U:\Projects\Clear Extracts\pref.xls", _
            True, _
            strSheet
    Next x
MsgBox "Pref Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub
'-------------------------
Public Sub Import_Rel()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 15
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "rel", _
            "U:\Projects\Clear Extracts\rel.xls", _
            True, _
            strSheet
    Next x
MsgBox "Rel Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub
 
Upvote 0
i still have to select within subroutine that i want to run and then click the run button 3 times
Click to expand...

Within which subroutine must you do this? It's not making sense at first glance - not sure what's going on.
 
Upvote 0
For example in order to run this script i have to put the cursor between the following:


Code: 
'-------------------------
Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 10
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "U:\Projects\Clear Extracts\core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub
'-------------------------

and then hit the run icon. Then this will only create this single sheet.
 
Upvote 0
The "run em all" sub needs to have the names of the procedures you want to run. Looking closely at post #17 I see this code:
Code: 
Public Sub Import_Stuff()
    Call Import_Result
    Call Import_Pref
    Call Import_Rel
End Sub

But it does not have a sub named Import_Core().

Hence, we may need to amend the "run em all" procedure (here it is a sub called Import_Stuff), something like:
Code: 
Public Sub Import_Stuff()
    Call Import_Result
    Call Import_Pref
    Call Import_Rel
    Call [COLOR="Blue"]Import_Core[/COLOR]
End Sub

Does that make sense?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How Do I? ... Import New Data from Excel into Access, keeping the queries ?
Replies
6
Views
286
PurrSent
P
C
  • Question Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
590
welshgasman
welshgasman
J
  • Solved
Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?
2
Replies
15
Views
950
Micron
M
R
  • Question Question
Run Outlook rule
2
Replies
11
Views
1K
welshgasman
welshgasman
A
  • Question Question
Efficiently Importing Large Records to MS - Access
Replies
8
Views
1K
jackd
J

Forum statistics

Threads
1,224,586
Messages
6,179,712
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top