Excel multiple sheet import for Access

[daltonrogers_]

Hi, I have found the code below on a posting. Link is below. Can you please make this work in Office 2010. I am wanting to import multiple files into microsoft access, however the With Application.FileSearch does not work and the Funtion ImportExcelFiles() returns an error. new to the script don't know how it all works.

http://www.mrexcel.com/forum/micros...olders-worth-excel-work-books-into-acess.html

Function ImportExcelFiles()
Dim Counter As Integer

With Application.FileSearch
.NewSearch
.LookIn = "\C:\ImportDir" 'change this to your actual directory
.SearchSubFolders = False 'set to True if you want to search subfolders too
FileName = "*.xls" 'changed HERE

If .Execute() > 0 Then 'files found
For Counter = 1 To .FoundFiles.Count 'loop through files
.FileName = .FoundFiles(Counter) 'set / get the file name
'Change the "ImportFile" part in the line below if you are using a different table name
'Note: 1 command for each worksheet. I have assumed they are Sheet1 etc.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, True, "Sheet1!"'Changed HERE

DoEvents 'don't take over all of the PC resources
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else 'files not found
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With
End Function

 

[anvg]

Try and modify for your purpose. It works on a Windows from WinXP (maybe, Win2000)

Public Sub Import()
    Dim pShell As Object, pFolder As Object
    Dim pItems As Object, pItem As Object
    Set pShell = CreateObject("Shell.Application")
    Set pFolder = pShell.Namespace("c:\ImportDir")
    Set pItems = pFolder.Items
    pItems.Filter 64, "*.xlsx"
    If pItems.Count > 0 Then
        For Each pItem In pItems
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", pItem.Path, True, "Sheet1!A1:C7"
        Next
    End If
End Sub

Regards,

 

[daltonrogers_]

Thankyou works perfect

 

[daltonrogers_]

How do I modify the code if the document names are unknown?

 

[anvg]

I am sorry. What is mean "document names are unknown"? The code above searches files with a xlsx extension in a designated folder and puts each from them into DoCmd.TransferSpreadsheet command.

 

[daltonrogers_]

I have multiple files, I would like to import them all into access and call them what they are called without the xlsx extention.

 

[anvg]

Use pItem.Name as a new table nameRegards,