VBA: Closing a workbook causes code to stop running

Posted on 2009-02-08
Medium Priority
Last Modified: 2012-05-06


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).

Question by:bishop3000
1 Comment

Accepted Solution

bishop3000 earned 0 total points
ID: 23587129
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?


' 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\ _
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


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

830 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