Import Individual Excel Worksheet into Access Table

[esneaker]

Greetings!!

I am new to MS Access and have been trying to use VBA to import a specific worksheet called "Access" in an Excel file (from about 400 users) into a single table. I want the code to search through one folder and import the "Access" worksheet in each Excel workbook within that folder. Each user has the same worksheet name. Here's my problem. I got this VBA code from: EXCEL Import

Sub ImportExcel()
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
Dim strWorksheets(1 To 1) As String

' this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1 To 1) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strWorksheets(1) = "Access"


' Replace generic table names with the real table names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strTables(1) = "Access"


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

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "J:\MyWorkbooks\"

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 1

strFile = Dir(strPath & "*.xlsm")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets)
strFile = Dir()
Loop

Next intWorksheets
End Sub

When I execute the code. Nothing happens. I go to the table and find nothing....or I get Runtime error 3011, where MS Access can not find the object "Access". Can someone please help me figure this out?? Also, is it possible to update the table without duplicating records?

I'm using Excel 2010 and Access 2010.

Thank you for your time and help!

 

[ranman256]

use the FileSystemObject to scan the files...
usage: ImportAllFilesInDir "c:\folder\folder2\"

Public Sub ImportAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vOutFile


On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"


sTbl = "xlFile"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)


For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(vFil, ".txt") > 0 Then      'ONLY DO text FILES
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tTable", vFil, True    
    End If
Next


Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub


errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub