Solved

Closing Excel Properly From Access VBA Module

Posted on 2009-05-14
14
511 Views
Last Modified: 2013-11-27
I modified some code I found on Experts to control Excel, open a workbook, and run a macro embedded in the workbook from within an Access VBA Module.  I believe I have the code almost doing what I want but I don't think I'm closing everything down properly.  As it stands, I can run the code completely the first time, but running the same code immediately after generates an error indicating that the macro can't be found.  I also think that the workbook I'm referencing is still open in the background since it sometimes opens in "Read-Only" state.  I think I'm having a syntax problem and could really use some help.  It would also be good to know if there's a more efficient way of doing this task.  I've listed the entire code below:
Sub xcelFromAccess()
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    exApp.Visible = True
    Workbooks.Open "C:\tst_a.xls"
    exApp.Run ("ThisWorkbook.trnsData")
    Workbooks("tst_a.xls").Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
    
End Sub

Open in new window

0
Comment
Question by:axla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24392818
Try the below version instead. It makes use of the exWB variable, as well as directly referencing the newly Excel Application object.
Sub xcelFromAccess()
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    exApp.Visible = True
    Set exWB = Workbooks.Open("C:\tst_a.xls")
    exApp.Run ("ThisWorkbook.trnsData")
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
    
End Sub

Open in new window

0
 

Author Comment

by:axla
ID: 24393047

Webtubbs,

Thanks for the help, but I'm still running into the same behavior.  The code works as designed the first time, the second time it generates the error message that it can not find the macro, the third time it runs but the workbook is opened in "Read-Only" mode.  Here's the modified code I'm using from your revision.

axla

Sub xcelFromAccessRev()

    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook

    Set exApp = New Excel.Application
    exApp.Visible = True
    Set exWB = Workbooks.Open("C:\test_new.xls")
    exApp.Run ("ThisWorkbook.trnsData")
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
   
End Sub


0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24393056
Assuming the macro you want to run actually resides within "tst_a.xls" then I would also prefix the Run argument with the macro name:

Richard
'taken from Wayne's code:
 
    Set exWB = Workbooks.Open("C:\tst_a.xls")
    exApp.Run exWB.Name & "!ThisWorkbook.trnsData"

Open in new window

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24393072
As the macro is in the ThisWorkbook module, you might also try calling the macro like this....

    exWB.trnsData

I'm not 100% certain that's the cause of the problems, but give it a go and see if it helps.

Wayne
0
 

Author Comment

by:axla
ID: 24393138
Richard,

I still get the same behavior after incorporating your code update.

Wayne,

When I change the call to the macro to "exWB.trnsData" (no quotes included in code module) I get this error message:   Run-time error '438':  Object doesn't support this property or method.  Any ideas on what I should try next?

axla


0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24393177
Try changing to:

Richard
Sub xcelFromAccessRev()
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    exApp.Visible = True
    Set exWB = exApp.Workbooks.Open("C:\test_new.xls")
    exApp.Run exWB.Name & "!ThisWorkbook.trnsData"
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
    
End Sub

Open in new window

0
 

Author Comment

by:axla
ID: 24393242
Richard,

Thanks for your help, your changes now allow me to rerun the code multiple times.  However, each time the workbook in question opens up in Read-Only" mode.  Do you know why this occurs?  Is there a way to correct this behavior or should I just add a workaround to the module that adds a "Save As" option for the workbook.  Although I don't quite understand why the call to the workbook via code prevents the normal save operation.

axla

0
 
LVL 16

Expert Comment

by:RichardSchollar
ID: 24393310
I *suspect* that the file is opening up read only because it hasn't been released from memory (so there is already a write copy open as you suggested).  Why this would be I am not certain.  I will look into it (as you don't really want multiple copies hogging system resources).
0
 
LVL 16

Accepted Solution

by:
RichardSchollar earned 325 total points
ID: 24393370
This is a stab in the dark, but does it work properly using this:


Sub xcelFromAccessRev()
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    exApp.Visible = True
    Set exWB = exApp.Workbooks.Open("C:\test_new.xls")
    exApp.Run exWB.Name & "!ThisWorkbook.trnsData"
    exWB.Close
    Set exWB = Nothing
    
    exApp.Quit
    Set exApp = Nothing
    
End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:DK_User
ID: 24394339
Here is a solution thats should work.

Its this line thats interesting:
exApp.Run "TransData"

Every macro in Excel is actually placed in a Module, so by using the Application Object you can call
Public procedures from Modules.

I have added Error handling, so that wont run into the "Read only" problem in case of error.

On Error GoTo Err_handler
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    Set exWB = exApp.Workbooks.Open("C:\test_new.xls")
    exApp.Visible = True
   
    exApp.Run "TransData"
    
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
Exit Sub
Err_handler:
    MsgBox Err.Description, vbOKOnly
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing
End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:DK_User
ID: 24394367
Ups - forgot to remove the Quit sentence:-)



On Error GoTo Err_handler
 
    Dim exApp As Excel.Application
    Dim exWB As Excel.Workbook
 
    Set exApp = New Excel.Application
    Set exWB = exApp.Workbooks.Open("C:\test_new.xls")
    exApp.Visible = True
   
    exApp.Run "TransData"
   
    Set exWB = Nothing
    Set exApp = Nothing
Exit Sub
Err_handler:
    MsgBox Err.Description, vbOKOnly
    exWB.Close
    exApp.Quit
   
    Set exWB = Nothing
    Set exApp = Nothing

Open in new window

0
 

Author Comment

by:axla
ID: 24398451
Richard,

Your revised code works like a charm.  Each time the Excel Application and Workbook are called they open, perform the macro, and close, allowing the Workbook to open in edit mode each time.

DK_User,

Unfortunately, I was unable to implement your code without producing the "macro not found" error on my workstation.
0
 
LVL 3

Assisted Solution

by:DK_User
DK_User earned 25 total points
ID: 24400392
Yep -  I see I made a typo in my exampel.
Im naming my macro TransData, and in the original code its called trnsdata.
0
 

Author Closing Comment

by:axla
ID: 31581813
Richard and DK_User,
Thanks to the both of you for your assistance.  I was a little unsure of how to do the points allocation since Richard's solution is what I implemented in my code.  I ended up awarding DK_User 25 points since he provided valuable information even though I used Richard's solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

636 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