Referenced cells to another workbook not updating (requires source workbook open, enter destination cell and exit, f2 + enter)

[ClarityNDT]

Good morning everyone.

Firstly I hope everyone is staying safe and well in the current climate.

I have the following issue:

MS access exports some information to a .xlsx workbook stored on users root directory, workbook1.

A template workbook, workbook2 is then used to build documents where some information is taken from workbook1. The is achieved by a hidden sheet in workbook2 where a simple set of formulas pulls the information from workbook1

='C:\RPTTMP\[workbook1.XLSx]worksheet1'!A2

So my issue is that the cells in workbook2 do not update to the current values of workbook1 unless I do one of the following:

Open workbook1
enter the destination cells in workbook2 and exit the cell (essentially f2+enter)

I've tried having an onload vba action to send an f2 and enter command for the affected cells but the process is relatively long and we complete the process regularly throughout the day. I've also tried using a vba sub to find and replace all instances of "=" in workbook2 but again this process seems quite long.

I'm thinking the easiest way may be to have workbook2 load and then kill workbook1 when it opens? (I need to ensure workbook1 is kept closed to allow access to update it next time, this cannot be left to the user).

Does anyone have a more streamlined solution to this issue?

Appreciate any time and knowledge

 

[ClarityNDT]

I'm not sure how to edit my original post.

For anyone that may need it, the solution of launching the source workbook and immediately killing it does work.

Dim wb As Workbook
Set wb = Application.Workbooks.Open("your absolute path and filename including extention")
Workbooks.Open ("your absolute path and filename including extention")
Application.Wait (Now + TimeValue("0:00:1"))
wb.Close

The application.wait allows a second for the file to load before closing it. not sure if it is needed but it can be removed or increased to suit performance.