Solved

Closing Excel Properly From Access VBA Module

Posted on 2009-05-14
14
503 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 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