title: Method 'Add' of object "workbooks" failed - Importing excel workbook with vba Access 2007
body: <p>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.</p>
  
  <hr>
  
  <p>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.</p>
  
  <p>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 <em>may</em> 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:</p>
  
  <p>edit: current code updated again, related to the accepted post from Siddharth below</p>
  
  <pre><code>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 &amp; "!" &amp; _
          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
  </code></pre>
  
  <p>The failure seems to occur for the client on the line <code>Set excelbook = excelApp.Workbooks.Add</code> with this message:</p>
  
  <p><a href="https://i.stack.imgur.com/C1IHe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/C1IHe.png" alt="&quot;Method &#39;Add&#39; of object &#39;Workbooks&#39; failed&quot;"></a></p>
  
  <p>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?</p>
  
  <p>Thanks for any help you can provide!</p>