Solved

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

Posted on 2014-07-24
6
1,862 Views
Last Modified: 2014-07-29
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
0
Comment
Question by:Fritz Paul
6 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40217179
Could you send us the whole file for understanding your question easier ?

Duncan
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40217220
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
 

Author Comment

by:Fritz Paul
ID: 40217305
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:Jerry_Justice
ID: 40217457
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
 

Accepted Solution

by:
Fritz Paul earned 0 total points
ID: 40217654
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
 

Author Closing Comment

by:Fritz Paul
ID: 40226182
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question