excel - How do I overwrite a workbook switching the vba projects? - Stack Overflow

admin管理员组

文章数量:1296451

I have written a macro that works like an update function for a specific workbook. This macro exists in a workbook "A" and in a workbook "B".

When I start the update from workbook "A", it opens workbook "B". Afterwards I'm changing the focus from the vba project of workbook "A" to workbook "B" by using

Application.Run ("'" & wbkWorkbookA.Name & "'!UpdateMe"), wbkWorkbookA, wbkWorkbookB

After the macro has transfered all data from workbook "A" to workbook "B" (via the macro "UpdateMe"), I'm trying to save Workbook "B" in the same folder like workbook "A". I want to close and delete workbook "A". The following sub is also in the vba project of workbook "B":

Public Sub prcSaveNewWorkbook()
    
    Dim strFilepath As String
    Dim lngError As Long

'I've changed my code a little bit for better testing:
    set wbkWorkbookA = Application.Workbooks(1)
    Set wbkWorkbookB = Application.Workbooks(2)

    strFilepath = wbkWorkbookA.Path & "\" & wbkWorkbookA.name
        
    On Error Resume Next
    
    wbkWorkbookB.SaveAs _
        strFilepath, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
        AddToMru:=True
    
    lngError = Err.Number
    
    Application.EnableEvents = False

    On Error GoTo 0
    
    If lngError <> 0 Then
        wbkWorkbookA.Close False
        VBA.Kill strFilepath
        
        wbkWorkbookB.SaveAs _
            strFilepath, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            AddToMru:=True
        
    End If

End Function

But when I try this, the close event of workbook "A" gets triggerd and the further execution of the code ends within the close event of workbook "A". It doesn't get back to the macro "prcSaveNewWorkbook" in workbook "B".

I would be happy, if there is any idea how I get this running.

I have written a macro that works like an update function for a specific workbook. This macro exists in a workbook "A" and in a workbook "B".

When I start the update from workbook "A", it opens workbook "B". Afterwards I'm changing the focus from the vba project of workbook "A" to workbook "B" by using

Application.Run ("'" & wbkWorkbookA.Name & "'!UpdateMe"), wbkWorkbookA, wbkWorkbookB

After the macro has transfered all data from workbook "A" to workbook "B" (via the macro "UpdateMe"), I'm trying to save Workbook "B" in the same folder like workbook "A". I want to close and delete workbook "A". The following sub is also in the vba project of workbook "B":

Public Sub prcSaveNewWorkbook()
    
    Dim strFilepath As String
    Dim lngError As Long

'I've changed my code a little bit for better testing:
    set wbkWorkbookA = Application.Workbooks(1)
    Set wbkWorkbookB = Application.Workbooks(2)

    strFilepath = wbkWorkbookA.Path & "\" & wbkWorkbookA.name
        
    On Error Resume Next
    
    wbkWorkbookB.SaveAs _
        strFilepath, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
        AddToMru:=True
    
    lngError = Err.Number
    
    Application.EnableEvents = False

    On Error GoTo 0
    
    If lngError <> 0 Then
        wbkWorkbookA.Close False
        VBA.Kill strFilepath
        
        wbkWorkbookB.SaveAs _
            strFilepath, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            AddToMru:=True
        
    End If

End Function

But when I try this, the close event of workbook "A" gets triggerd and the further execution of the code ends within the close event of workbook "A". It doesn't get back to the macro "prcSaveNewWorkbook" in workbook "B".

I would be happy, if there is any idea how I get this running.

• excel
• vba

Share Improve this question Follow edited Feb 11 at 21:10 Sergeij_Molotow asked Feb 11 at 20:53 Sergeij_MolotowSergeij_Molotow 8377 bronze badges 4

• 2 If workbookA is open there's no point even trying to save workbookB over workbookA - that's never going to work. Turn events off if you don't want to trigger the close event of WorkbookA when you close it. – Tim Williams Commented Feb 11 at 21:02
• Hi Tim, I've tried your idea. Now the close event isn't triggered, but it stops right after the line wbkWorkbookA.Close False, no further code is executed. I guess I have to get back the focus from the vba project from workbook "A" back to workbook "B". – Sergeij_Molotow Commented Feb 11 at 21:13
• Is prcSaveNewWorkbook called directly from UpdateMe? Try calling it using OnTime with a 1-sec delay, to "decouple" it from the original call from WorkbookA, so that can be completed before you try to close WbA. – Tim Williams Commented Feb 11 at 21:26
• Hi Tim, yes, prcSaveNewWorkbook is called from UpdateMe directly, both macros are in workbook "B". I've added your suggestion, but it has no effect on the macro. It still ends the execution after WorkbookA.close False (but now without triggering the close event of workbook "A"). Might it be helpful, if I say, that programmatically workbook "B" isn't saved before closing Workbook "A"? It shall be saved after workbook "A" was deleted. – Sergeij_Molotow Commented Feb 12 at 12:40

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This works for me in a regular module (in both workbooks)

In a regular module in both workbooks:

Private wb As Workbook 'Represents a workbook being replaced by
                       '   a workbook created from the newer template

'This runs in the workbook which might get replaced...
Sub CheckForUpdate()
    Dim wbToRun As Workbook

    'check if the template has been updated
    If TemplateIsNewer(configuredTemplatePath) Then
        'open the template for the file which will replace this workbook
        Set wbToRun = Workbooks.Open("C:\Temp\VBA\Replacer.xltm")
    
        'demo purposes: put a flag in the new version...
        wbToRun.Worksheets(1).Range("A1").Value = "Replaced " & Now()
    
        Application.Run "'" & wbToRun.Name & "'!StartUpdate", 
ThisWorkbook
        Exit Sub 'in case later code in this Sub needs to get skipped
    End If 'template version is newer
    
    'Don't do anything else here if the above version check ended
    '  up running the update! There should be no VBA running in
    '  the calling workbook which could block it from being closed
    '  by code running in `wbToRun`
End Sub

'This is running in the replacement workbook
Sub StartUpdate(wbToReplace As Workbook)
    Debug.Print "Called to replace workbook: " & wbToReplace.FullName
    Set wb = wbToReplace
    
    '##################################################
    'Do whatever copying/config/etc needs to be 
    '   done between the calling workbook and this one
    '##################################################
    
    'This OnTime call  should be the *last* thing that happens in `StartUpdate`
    '  it allows control to return to `CheckForUpdate` in the
    '  calling workbook, so all code there is done before you try
    '  to close it. 
    Application.OnTime Now + TimeSerial(0, 0, 1), "FinishUpdate"
End Sub


'Final step of the "update and replace" process
'   Close `wb` and save ThisWorkbook over it
Sub FinishUpdate()
    Dim p As String
    p = wb.FullName
    wb.Close False
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs p, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
End Sub

本文标签： excelHow do I overwrite a workbook switching the vba projectsStack Overflow