VBA: Closing a workbook causes code to stop running

Posted on 2009-02-08
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

    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now