CODEWITHSUNDEEP
ms-accessvba
user3121688
user3121688

Reputation: 1

Import multiple spreadsheets into Access

I am trying to import all my spreadsheets in a workbook to Access. However, nothing gets imported into Access even though i receive no error message. Everything is working except for the line noted below, where even though it seems like Access is importing the spreadsheets, nothing appears in my table.

Public Sub Import_Excel_Workbook()
Dim strFile As String
Dim StrFldrPath As String
Dim strfilelist() As String
Dim intFile As Integer
Dim filename As String

DoCmd.SetWarnings False
StrFldrPath = "C:\Documents\SPY\New\"

'Loop through the folder & build file list
strFile = Dir(StrFldrPath & "*.xls")

'  (commented-out code removed for clarity)

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Documents\Database2.accdb" 'not dynamic yet

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents\SPY\New\SPY_1.xls") 'not dynamic yet
Set colWorksheets = objWorkbook.Worksheets

'cycle through the list of files
'For intFile = 1 To UBound(strfilelist)
    'filename = StrFldrPath & strfilelist(intFile)   (removed for the time being)
    For Each objWorksheet In colWorksheets
        Set objRange = objWorksheet.UsedRange
        strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)
        '##########  LINE BELOW SEEMS TO FAIL ############
        objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
          "S&P", "C:\Documents\SPY\New\SPY_1.xls", True, strWorksheetName  'not dynamic yet    
    Next
'Next intFile

DoCmd.SetWarnings True

End Sub

Upvotes: 0

Views: 931

Answers (1)

Smandoli
Smandoli

Reputation: 7019

It looks like you are trying to follow this approach: http://blogs.technet.com/b/heyscriptingguy/archive/2008/01/21/how-can-i-import-multiple-worksheets-into-an-access-database.aspx. It's important to note this technique lets you import all tabs (worksheets).

As suggested in a comment, you must avoid setting SetWarnings to False. You can't trouble shoot when you are suppressing errors.

I suggest you change your tab (worksheet) name so it does not contain the symbol &.

If that does not solve your problem, try using the Access import wizard on your problem tab . If you don't have experience with imports, be aware there are many "gotchas". Your source document needs to be free of merged cells, incoherent header-row entries, and countless other snags. The import wizard may reveal some problem with the source data.

Finally, if the source worksheet is empty (none of the cells have values), the import will halt at that point. (This is not the behavior you are reporting, but it's worth a mention.)

Upvotes: 1

Related Questions