VBA to open Excel spreadsheet from Access does not work. Do you see the problem?

I have the code below to open an Excel spreadsheet from a form in Access. I use Access 2010 and Excel 2010.
Some times it works, say 1 in 5 tries. Other times it gives different error messages. The line where it breakes is indicated such.

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>CODE FAILS ON NEXT LINE

Open in new window


The file that is supposed to open is named
Agreement Terms 2014 Q1.xls
and as I say sometimes it opens.

The Locals Window just before the error occurs is shown at the bottom.

Private Sub OpenExcelWorkbook(WorkbookToRead As String)
'   Open Excel Workbook
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim Flag As String

'    Set wb = Nothing
'    Set excelApp = Nothing


On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")  'http://support.microsoft.com/kb/288902 - If Excel is not open, an error occurs and Err.Number > 0
                Flag = 1
If Err.Number <> 0 Then 'There is an error if Err.Number <> 0
    Set excelApp = CreateObject("Excel.Application") 'Create Excel.Application
End If

On Error GoTo Err_Handler
                Flag = 2
'    WorkbookToRead = Me.txtOrigATD      'A textbox on my form

    With excelApp
        .Visible = True  'Excel becomes visible. Confirmed.

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>CODE FAILS ON NEXT LINE
        Set wb = .Workbooks.Open(WorkbookToRead, ReadOnly:=True)
    End With
                '    wb.ActiveSheet.Range("d1").Value = 100     'Passes a value to a cell on the spreadsheet.
                Flag = 3
    Set wb = Nothing
    Set excelApp = Nothing
    
Exit_Sub:
    Exit Sub

Err_Handler:
    Set wb = Nothing
    Set excelApp = Nothing
    MsgBox Err.Description
    MsgBox "An error occurred while in Private Sub OpenExcelWorkbook(), called from cmdOpenExcelWorkbook_Click() | Flag = " & Flag
    Me.SetFocus
    Resume Exit_Sub

End Sub

Open in new window


Locals window just before the error
Fritz PaulAsked:
Who is Participating?
 
Fritz PaulConnect With a Mentor Author Commented:
Thanks,

I found it worked once and the second time a whole new error came up: "Unable to get the Open property of the Workbooks class" and then thereafter all the regular errors, depending whether I first close Excel etc.

I really appreciate your help. I have now found similar code in one of my apps and it works every time except when the workbook is already open. I will just have to find code to check whether the workbook is already open and then return a message and terminate. The only difference is that all the code is in the On Click event and is not in a called subroutine. I can't think why.

As below
"
Private Sub cmdOrigATD_Click()
' Browse for single files

Dim sPath As String
Dim sFile As String
Dim WorkbookToRead As String
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim Flag As String

    priBrowseForFile sPath, sFile   ' Refers to Public Function priBrowseForFile_Click(sPath As String, sFile As String)
                                    ' http://stackoverflow.com/questions/14915179/ms-access-browse-for-file-and-get-file-name-and-path
    
    Me.txtOrigATD = sPath & sFile
    WorkbookToRead = Me.txtOrigATD

On Error Resume Next

Set excelApp = GetObject(, "Excel.Application")  'http://support.microsoft.com/kb/288902 - If Excel is not open, an error occurs and Err.Number > 0
                Flag = 1
If Err.Number <> 0 Then Set excelApp = CreateObject("Excel.Application") 'There is an error if Err.Number <> 0     Create Excel.Application

On Error GoTo Err_Handler
                Flag = 2

    With excelApp
        .Visible = True  'Excel becomes visible. Confirmed.
        Set wb = .Workbooks.Open(WorkbookToRead, ReadOnly:=True)
    End With
                Flag = 3
                
                
    Set wb = Nothing
    Set excelApp = Nothing

Exit_Sub:
    Exit Sub

Err_Handler:
    Set wb = Nothing
    Set excelApp = Nothing
    MsgBox Err.Description
    MsgBox "An error occurred while in Private Sub OpenExcelWorkbook(), called from cmdOpenExcelWorkbook_Click() | Flag = " & Flag
    Me.SetFocus
    Resume Exit_Sub

End Sub

Open in new window

0
 
duncanb7Commented:
Could you send us the whole file for understanding your question easier ?

Duncan
0
 
Rey Obrero (Capricorn1)Commented:
try changing this two lines

    Set wb = Nothing
    Set excelApp = Nothing

with
   excelApp.Quit

before you run the codes,
close Access application
close all Excel application

open access application..
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fritz PaulAuthor Commented:
Hi Rey,
I get the same problem except that no, when the workbook opens, it closes immediately again.
It seems I will have to prepare a small database and submit it or consult someone directly.
Thanks for your help.

by the way errors that I get are

Method 'Open' of object 'Worksheets' failed
 or
Automation error
The remote procedure call failed.
 or
Excel cannot complete this task with available resources. Choose less data or close other applications.
0
 
Jerry_JusticeCommented:
Dim excelApp As Object
Dim wb As Object

You need to declare these as Object rather than to the excel.application

When you create an object with "late binding":

Set excelApp = GetObject(, "Excel.Application")  


You always Dim the variable as "Object"

Change those two lines and it works fine.
0
 
Fritz PaulAuthor Commented:
I have tested this code now over and over and it does not break
I sincerely appreciate the suggestions that I received, but cannot accept anyone as a solution.
0
All Courses

From novice to tech pro — start learning today.