Unable to run a saveas in a workbook_beforeclose sub when xl file is closed from another file

JohnFox 0 Reputation points
2023-03-26T08:28:59.1033333+00:00

Hello,

I have 2 xlsm files for trying to solve an issue. The first one (FileA.xlsm) has a Sub Workbook_Open and a Sub Workbook_BeforeClose.

The second one has a very simple code to open FileA.xlsm and close it.

When I manually open FileA.xlsm I then close it manually, it runs properly and does the SaveCopyAs and the SaveAs properly.

BUT, when I open it and close it by VBA from the second file, the CaseCopyAs doesn't work, nore the SaveAs.

There is no error. I tried many differents things that should be too long to write here. I read many things about SaveAs and SaveCopyAs, but without any success.

Is there someone who can help and exeplin me what happend? Thanks by adavnce.

In ThisWorkbook in FileA.xlsm : 

Option Explicit

Private Sub Workbook_Open()
    Call DefVariable
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ValCellule
    Call enregsitre1SaveCopyAs
    Call enregsitre2SaveAs
End Sub


In Module1 in FileA.xlsm: 

Option Explicit

Public mywb As Workbook, myws As Worksheet
Sub DefVariable()
    Set mywb = ThisWorkbook
    Set myws = mywb.Sheets("Variables")
    myws.Cells(2, 2).Value = "ouverture" 'to verify where the code is running
End Sub

Sub ValCellule()
    myws.Cells(2, 2).Value = Now() ''to verify where the code is running
End Sub

Sub enregsitre1SaveCopyAs()
Debug.Print "mywb.SaveCopyAs"
    mywb.SaveCopyAs "D:\essai.xlsm"
    Debug.Print "enregsitre1 : = " & ThisWorkbook.Name
End Sub

Sub enregsitre2SaveAs()
Debug.Print "mywb.SaveAs"
    mywb.SaveAs "D:\essai.xlsm"
    Debug.Print "enregsitre2 : = " & ThisWorkbook.Name
End Sub

In File B I put the following code :

Sub GO()
    Application.Workbooks.Open "D:\FileA.xlsm"
    Workbooks("FileA.xlsm").Close
End Sub
Windows
Windows
A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.
4,740 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,479 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 43,931 Reputation points
    2023-03-27T12:22:25.8566667+00:00

    Hello there,

    You can dig into these articles which explain the scenario which might cause this exact scenario.

    This will occur if both of the following conditions are true:

    -The code in question is contained inside an automatically-running subroutine, such as an Auto_Open or Auto_Close subroutine.

    -The code is not contained in a Visual Basic module, but "behind" a worksheet or the workbook itself.

    VBA code "behind" a worksheet or a workbook may not work in Excel https://support.microsoft.com/en-us/topic/vba-code-behind-a-worksheet-or-a-workbook-may-not-work-in-excel-f2de64d3-a926-7035-e18e-1697f0a32bc5

    Error message when you run a Visual Basic for Applications macro in Excel: "Method 'SaveAs' of object '_Worksheet' failed" https://support.microsoft.com/en-us/topic/error-message-when-you-run-a-visual-basic-for-applications-macro-in-excel-method-saveas-of-object-worksheet-failed-376fcbb2-9941-f34d-1aba-ca602903245f

    Hope this resolves your Query !!

    --If the reply is helpful, please Upvote and Accept it as an answer–

    0 comments