Solved

Closing Excel Properly From Access VBA Module

Posted on 2009-05-14
14
500 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)
Comment Utility
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
Comment Utility

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
Comment Utility
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Expert Comment

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

9 Experts available now in Live!

Get 1:1 Help Now