Workbook Open Event Problem

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,022
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

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

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

Regards,

Humayun
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,112
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,022
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top