Workbook Open Event Problem

[hrayani]

Hello Friends,

Please look at this small code I am using in a blank workbook named as UPDATE_FILE. The code works fine when this workbook is open directly.

Private Sub Workbook_Open()

Workbooks("REPORTS.xlsm").Close savechanges:=False

FileCopy "C:\OneDrive\Documents\REPORTS.xlsm", "C:\ITEX DATA\REPORTS.xlsm"

Workbooks.Open "C:\ITEX DATA\REPORTS.xlsm"

Workbooks("UPDATE_FILE").Close savechanges:=False

End Sub

But when I try to open this workbook (UPDATE_FILE) from another workbook (REPORTS), it just pauses on this line of the code

Workbooks("REPORTS.xlsm").Close savechanges:=False

After that it does nothing. Can someone please guide me what is going wrong.

Regards,

Humayun

 

[GWteB]

But when I try to open this workbook (UPDATE_FILE) from another workbook (REPORTS), it just pauses on this line of the code

There is no pause, the code stops. This is a logical consequence of reciprocal references and code calls. The code stops because the code that calls the other code disappears from memory when the workbook containing that code is closed.
Try to figure out for yourself step by step what each code line does and you will arrive at the same conclusion.

 

[Jaafar Tribak]

When opening UPDATE_FILE file from REPORTS.xlsm via code , the calling routine never returns because the REPORTS.xlsm workbook is already closed by then.

You should be able to workaround this by briefly delaying the FilCopying operation as follows:

Option Explicit

Private Sub Workbook_Open()
    Application.OnTime Now, Me.CodeName & ".DoFileCopy"
    Workbooks("REPORTS.xls").Close savechanges:=False
End Sub

Private Sub DoFileCopy()
    FileCopy "C:\OneDrive\Documents\REPORTS.xlsm", "C:\ITEX DATA\REPORTS.xlsm"
    Workbooks.Open "C:\ITEX DATA\REPORTS.xlsm"
    Workbooks("UPDATE_FILE").Close savechanges:=False
End Sub

 

[hrayani]

Hi Jaafar,

Thanks for the reply & it really works the way I wanted it to. Thanks once again

But again its hard for me to understand. I am telling file # 1 (REPORTS) to just open file # 2 (UPDATE_FILE) and nothing else. The all the work has to be done from file # 2. So the code starts here that file # 2 will first close file # 1 and then will proceed with the copying stuff.

Now why a close file # 1 is causing the code to stop and to say further that closed file # 1 has nothing to do with the running of the code. coz the code is running from the open file which is file # 2.

Its driving me crazy... the problem is solved though