Solved

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

Posted on 2014-07-24
6
1,726 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
Comment Utility
Could you send us the whole file for understanding your question easier ?

Duncan
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Expert Comment

by:Jerry_Justice
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now