Update Links (v2000)

Topic

New Reply

I have an Excel Workbook with lookup functions referencing linked data. When some of my users open the workbook, the links take several minutes to refresh. When I open the source tables then open the main workbook, the links refresh quickly. As a result, here’s what I’m hoping to do:
* When the main workbook opens, have VB code open the source tables hidden in the background and return focus to the main workbook
* The first step should happen before the user is asked whether to refresh the links
* When the user closes the main workbook, the source tables should also close
I think this will speed the time it takes for the links to refresh without asking the users to open the source tables. I’m not familiar with Excel code and how to get something to trigger OnOpen.
Thanks!

Reply | Quote

Replies

I would do this by saving the files as a workspace file:

Save a group of workbooks in a customized workspace
You can open a group of workbooks in one step by creating a workspace file. A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen positions. When you open a workspace file by using the Open command (File menu), Microsoft Excel opens each workbook saved in the workspace. The workspace file does not contain the workbooks themselves, and you must continue to save changes you make to the individual workbooks.

Open the workbooks you want to open as a group.

Size and position the workbook windows as you want them to appear the next time you use the workbooks.

On the File menu, click Save Workspace.

In the File name box, enter a name for the workspace file.
Tip To open the workbooks each time you start Microsoft Excel, save the workspace file in the XLStart folder in your Excel folder. Save only the workspace file, not the workbook files, in the XLStart folder.

Reply | Quote

Thanks Ban. I’ve never used workspaces before so that was a great learning.

Reply | Quote

Thanks Ban. I’ve never used workspaces before so that was a great learning.

Reply | Quote

I would do this by saving the files as a workspace file:

Save a group of workbooks in a customized workspace
You can open a group of workbooks in one step by creating a workspace file. A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen positions. When you open a workspace file by using the Open command (File menu), Microsoft Excel opens each workbook saved in the workspace. The workspace file does not contain the workbooks themselves, and you must continue to save changes you make to the individual workbooks.

Open the workbooks you want to open as a group.

Size and position the workbook windows as you want them to appear the next time you use the workbooks.

On the File menu, click Save Workspace.

In the File name box, enter a name for the workspace file.
Tip To open the workbooks each time you start Microsoft Excel, save the workspace file in the XLStart folder in your Excel folder. Save only the workspace file, not the workbook files, in the XLStart folder.

Reply | Quote

AFAIK, the links update takes place before the open event is fired (or any other event). Therefore, what you described would not work. What you would have to do is create another dummy workbook that the user would open. The open event routine in this workbook could then open all of the linked files and hide them, then open the workbook with the links.

Reply | Quote

Thanks. That is what I suspected. I appreciate the feedback.

Reply | Quote

Thanks. That is what I suspected. I appreciate the feedback.

Reply | Quote

AFAIK, the links update takes place before the open event is fired (or any other event). Therefore, what you described would not work. What you would have to do is create another dummy workbook that the user would open. The open event routine in this workbook could then open all of the linked files and hide them, then open the workbook with the links.

Reply | Quote