Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Closing Excel Properly From Access VBA Module

Posted on 2009-05-14
14
Medium Priority
?
517 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1300 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 100 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

730 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