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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.