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.
UPDATE 30.04.2025:
Before triggering the "update" make sure to close all userforms in workbook "A", otherwise the macro won't work.
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