We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

VBA: Closing a workbook causes code to stop running

Medium Priority
274 Views
Last Modified: 2012-05-06
Hi,

Scenario:

In the first of two workbooks ("OpeningWkb"), I have code in the Workbook.Open procedure that opens a second workbook ("OpenedWkb").

The second workbook also has a Workbook.Open procedure that closes the initial wkb and then resumes with other code.

The problem is that in the second workbook, no code runs after the code line that closes the initial wkb.

Is there a trick to make this code run to completion?

The example is in the attached workbooks. You'll need to alter the paths to where you've saved the workbooks on your computer (the OpeningWkb file will draw an error the first time you open it).

I've simplified the code as much as possible in the attached workbooks. The ultimate purpose is in updating a file with a new file downloaded from the internet (after transferring info from the initial file, the downloaded file will kill the initial file and then SaveAs into the initial files location).

Thanks!
-Michael
OpeningWkb.xls
OpenedWkb.xls
Comment
Watch Question

I figured out a workaround (see the code below).

The only problem is that it doesn't delete the temporary file for the same reason cited above.
I can delete the file later, in different code -- but it would be nice to do it here. Any ideas?

Thanks,
Michael

' Placed in the 'OpenedWkb.xls' file
 
Private Sub Workbook_Open()
 
Workbooks("OpeningWkb.xls").SaveAs "C:\Users\Bishop\Desktop\ _
v4Testing\OpeningWkbtemp.xls"   'Close False"
 
 
Application.DisplayAlerts = False
  ThisWorkbook.SaveAs "C:\Users\Bishop\Desktop\ _
v4Testing\OpeningWkb.xls"
Application.DisplayAlerts = True
 
 
MsgBox "Need code in this procedure to run after other workbook is closed."  
 
Workbooks("OpeningWkbtemp.xls").Close , False
Kill "C:\Users\Bishop\Desktop\v4 Testing\OpeningWkbtemp.xls" 
 
End Sub

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.