Unanswered: open Excel workbook method fails in VBA from Access
I am trying to open an Excel spreadsheet from Access using the following code in Access:
Private mobjXL As Excel.Application
Private Sub cmdImportFiles_Click()
Dim wrk As Excel.Workbook
Set mobjXL = New Excel.Application
.ScreenUpdating = False
.Visible = false
.DisplayAlerts = False
Set wrk = mobjXL.Workbooks.Open("Excel file path and name")
' Do something with the Excel spreadsheet
' error handler
I keep getting an error message of 'Method 'Open' of object Workbook failed' everytime Excel tries to open the spreadsheet (and it can be any spreadsheet). Any suggestions on how to open the Excel spreadsheet in VBA without the error message?
One of the important things when automating, is closing and releasing the object variables again, after they've been used. We don't see any of those. It might be that you have extra instances of Excel running providing the anomalities. Do you have any extra instances of Excel running (check Task Manager)?
I prefer using createobject in stead of instantiating with the New keyword.