Closing Excel Properly From Access VBA Module

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

axlaAsked:
Who is Participating?
 
RichardSchollarConnect With a Mentor Commented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
axlaAuthor Commented:

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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
RichardSchollarCommented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
axlaAuthor Commented:
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
 
RichardSchollarCommented:
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
 
axlaAuthor Commented:
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
 
RichardSchollarCommented:
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
 
DK_UserCommented:
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
 
DK_UserCommented:
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
 
axlaAuthor Commented:
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
 
DK_UserConnect With a Mentor Commented:
Yep -  I see I made a typo in my exampel.
Im naming my macro TransData, and in the original code its called trnsdata.
0
 
axlaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.