S
stuartbisset
Hello All
I am working in Excel 2003. Scenario is this ... I have a workbook
which has links to 50 other workbooks. All of the workbooks SHOULD
have been recalculated before saving but it appears that this is not
always the case. I need to write some code to do the following:
1. Check the status of the links (vba equivalent of Edit > Links >
Check Status and grab the "status" property which is either 'unknown',
'ok', 'Warning: Values not updated', or 'Warning: source not
recalculated"). Is there a way of getting the 'status' property
programatically? I can't seem to find a way.
2. If the status from above is not 'OK' then the code opens the
workbook, recalculates it, saves it then closes it. If I can't get
the status above I may have to open, calc, save, close all files so
the macro would be quite slow.
3. Ensure that the open, calc, save and close events are all hidden
from the user. Can I open the files in the background somehow? I
imagine that doing it in the background is quicker anyway.
I already know about Activeworkbook.Linksources, workbook.open,
workbook.save, workbook.close and application.calculate. My main area
for needing help is (a) grabbing the status property, (b) opening
files and working on them in the background and (c) any other hints/
tips on working with linked files in the quickest way possible.
Kind regards
Stuart
I am working in Excel 2003. Scenario is this ... I have a workbook
which has links to 50 other workbooks. All of the workbooks SHOULD
have been recalculated before saving but it appears that this is not
always the case. I need to write some code to do the following:
1. Check the status of the links (vba equivalent of Edit > Links >
Check Status and grab the "status" property which is either 'unknown',
'ok', 'Warning: Values not updated', or 'Warning: source not
recalculated"). Is there a way of getting the 'status' property
programatically? I can't seem to find a way.
2. If the status from above is not 'OK' then the code opens the
workbook, recalculates it, saves it then closes it. If I can't get
the status above I may have to open, calc, save, close all files so
the macro would be quite slow.
3. Ensure that the open, calc, save and close events are all hidden
from the user. Can I open the files in the background somehow? I
imagine that doing it in the background is quicker anyway.
I already know about Activeworkbook.Linksources, workbook.open,
workbook.save, workbook.close and application.calculate. My main area
for needing help is (a) grabbing the status property, (b) opening
files and working on them in the background and (c) any other hints/
tips on working with linked files in the quickest way possible.
Kind regards
Stuart