Method 'Add' of object "workbooks" failed - Importing excel workbook with vba Access 2007

Question

RESOLVED: I've accepted an answer below from Siddharth. I greatly appreciate everyone's help and I am amazed at the swift responses. I always learn something new when coming to this community for help, you guys are awesome.

---

Thanks for taking a moment to look at my message. I've put together a script (thanks in no small part to the great help here on SO) that takes an excel workbook and imports each worksheet to a separate table in an Access 2007 database. The script used to work fine on my PC but since a recent recovery from a hardware failure I haven't been able to get the script to run. To top it off, my client is getting different error messages than my own.

A large part of the issue has to do with my object references, when I have the Microsoft Excel 14 Object Library added as a reference from the tools menu, all works fine. However, the client has a different version of Office on their systems and wishes this app to be distributed to others who may have other versions of office installed. I've tried to implement some form of late binding, but I may not be approaching this correctly. Code is below:

edit: current code updated again, related to the accepted post from Siddharth below

Private Sub Command101_Click()
    On Error GoTo Err_Command101_Click

    ' Set up excel object
    Dim excelApp As Object
    Set excelApp = CreateObject("Excel.Application")

    ' Set up workbook object
    Dim excelbook As Object

    ' Set up file selection objects with parameters
    Dim fileSelection As Object
    Dim intNoOfSheets As Integer, intCounter As Integer
    Dim strFilePath As String, strLastDataColumn As String
    Dim strLastDataRow As String, strLastDataCell As String

    ' Prompt user with file open dialog
    Set fileSelection = Application.FileDialog(1)
    fileSelection.AllowMultiSelect = False
    fileSelection.Show

    ' Get the selected file path
    strFilePath = fileSelection.SelectedItems.Item(1)
    ' Open the workbook using the file path
    Set excelbook = excelApp.Workbooks.Open(strFilePath)
    ' Get the number of worksheets
    intNoOfSheets = excelbook.Worksheets.Count
    ' Set up object for current sheet name
    Dim CurrSheetName As String
    ' Disable errors
    DoCmd.SetWarnings False

    ' Loop through each sheet, adding data to the named table that matches the sheet
    For intCounter = 1 To intNoOfSheets
        excelbook.Worksheets(intCounter).Activate

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
        excelbook.Worksheets(intCounter).Name, strFilePath, True, _
        excelbook.Worksheets(intCounter).Name & "!" & _
        Replace(excelbook.Worksheets(intCounter).UsedRange.Address, "$", "")
    Next

    ' Close Excel objects
    excelbook.Close
    excelApp.Quit
    Set excelApp = Nothing
    ' Confirmation message
    MsgBox "Data import Complete!", vbOKOnly, ""
    DoCmd.SetWarnings True

Err_Command101_Click:
    MsgBox Err.Description
End Sub

The failure seems to occur for the client on the line Set excelbook = excelApp.Workbooks.Add with this message:

My question is somewhat twofold: a) Have I implemented late binding properly? and b) How can I resolve this error while making sure to keep the script independent of a specific Office release?

Thanks for any help you can provide!

Answer 1

I believe the error is in this line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, ActiveSheet.Name, _
    strFilePath, True, _
    excelbook.Worksheets(intCounter).Name & "!" & _
    Replace(excelbook.Worksheets(intCounter).UsedRange.Address, "$", "")

ActiveSheet.Name <+++++ This is causing the error.

Change that to excelbook.Worksheets(intCounter).Name

In Latebinding the code will not understand what Activesheet is

FOLLOWUP

You are getting a compile error because you did not add " _" at the end of the first line in DoCmd.TransferSpreadsheet

Copy the below code and paste it as it is in your code.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
excelbook.Worksheets(intCounter).Name, _
strFilePath, True, _
excelbook.Worksheets(intCounter).Name & "!" & _
Replace(excelbook.Worksheets(intCounter).UsedRange.Address, "$", "")