manual open and workbooks.open macro producing different results

[jdubyaIII]

Hello,

I have two worksbooks, call them A and B. workbook A references cells in workbook B. When I manually open workbook B, the cells in workbook A update. When I insert code to open workbook B using Workbooks.Open, workbook B does open, but the cells in A do NOT update. I'm at a loss as to what to try. Here is my code:

Private Sub Workbook_Open()
Call ReadDataFromClosedFile
End Sub


Sub ReadDataFromClosedFile()


Application.ScreenUpdating = False

Dim src1 As Workbook

Set src1 = Workbooks.Open(".....", False, False)


End Sub

thank you in advance.

Regards

 

[JoeMo]

Untested, but you can try this modification

Private Sub Workbook_Open()
    Call ReadDataFromClosedFile
Application.Calculate
End Sub

 

[jdubyaIII]

Untested, but you can try this modification

Private Sub Workbook_Open()
    Call ReadDataFromClosedFile
Application.Calculate
End Sub

Hi Joe,
thanks for the response, unfortunately this did not get the job done.

 

[JoeMo]

Have you confirmed that you have installed the workbook_open event macro correctly (goes in a Thisworkbook module, not a standard module), and that you have not inadvertently disabled events?

To check the latter, change the event macro to this:

Private Sub Workbook_Open()\
MsgBox "Workbook_Open event has fired correctly"
    Call ReadDataFromClosedFile
End Sub

If you don't get a pop-up message when the workbook is first opened, events are disabled.
To enable them:
Open the VBE and press ctrl + g to open the Immediate Window.
In the Immediate Window type (w/o quote marks): "Application.EnableEvents True" and press enter.
Save and close the workbook, then open it again and you should get the pop-up message.

 

[jdubyaIII]

Hi Joe, yes, events are enabled. I was able to solve my problem in a roundabout way by copying the data from workbook B into the workbook A into a new sheet. Then the values I want to update draw from this new sheet. This works.

Private Sub Workbook_Open()
Call ReadDataFromClosedFile
MsgBox "Updated!"
End Sub


Sub ReadDataFromClosedFile()


Application.ScreenUpdating = False

Dim src1 As Workbook
Dim curWorkbook As Workbook

Set curWorkbook = ThisWorkbook
Set src1 = Workbooks.Open("..............", False, False)

curWorkbook.Worksheets("sls").Range("A1:D1000").Formula = src1.Worksheets("Sheet1").Range("A1:D1000").Formula


src1.Close False

End Sub