If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
Can a VBA routine be written to change external links to manual updating
after they have been initially created in a workbook and that workbook has been closed? I would like to write a VBA procedure that can be included in a workbook template so that external links created in any workbooks created from the template can be manually changed from manual to autmatic updating. Is this possilbe |
#2
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
John Wirt wrote:
Can a VBA routine be written to change external links to manual updating after they have been initially created in a workbook and that workbook has been closed? Great question! 1) VBA does not have a method of its own to change the update method of a link 2) You can't change a file which is closed. However, you can use a VBA statement that executes an Excel 4 macro that will do the job for you while the workbook is open (I discovered this by recording a macro while changing a link's update method) V = ThisWorkbook.LinkSources(xlOLELinks) ' change to manual updating ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)" I would like to write a VBA procedure that can be included in a workbook template so that external links created in any workbooks created from the template can be manually changed from manual to autmatic updating. Now you've got me confused. You are now talking about changing to automatic updating. If you can't work out how to do what you want with the above help then please clarify at what stage you want the macro to run and whether it is changing the links to manual or automatic? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
Bill,
Thank you for replying. It looks like you may have it here. I want the macro to change updating from automatic to manual in a workbook after that workbook has been created by an author and closed, but before it is opened by someone else for purposes of review.. I want the macro to autorun when the workbook is opened by someone other than the author and change the updating to manual before the links created by the author are automatically updated. The sequence is this: 1. The workbooks I am concerned about are created by authors from a template I give them. Often, the authors link their workbooks to external data sources. 2. The workbooks submitted by the authors are reviewed by others. Generally, the data sources that are linked to these workbooks are not submitted with the workbooks. The data source files are too big. 3. The problem is that when the workbooks are opened by others, they sometimes forget to answer "No" to the update links prompt.. When this happens, the data in the workbook can become corrupted (lot's of #REF cell values, etc.). To eliminate this problem, I want to change the template on which these workbooks are based (or manually run the macro on all workbooks that are submitted) so that any external links created in them by the authors only update manually. 4. We use Excel 2000 and I see no way of creating links that can be set to update manually. Automatic updated seems to be wired into Excel 2000. Edit-:Links has a options for Automatic and Manual updating but the Manual option is dimmed out -- even in the workbook when the links are created. 4. It would be feasible to manually run the macro I am looking for in all workbooks when they are submitted by authors to change the updating to manual. 5. As a last restor, I suppose we could change recalculation to manual but that would be very inconvenient for most people. This is the design problem: a. Excel 2000. b. Write a macro stored in a module in the template used to create many workbooks that can be manually run to switch the updating of external links created in the workbook to manual. c. While one is at it, it probably would be a good idea to have another macro for toggling link updating back to automatic. I know, I know, one solution to the problem is to buy Excel 2003. Not feasible at this time. John Wirt John "Bill Manville" wrote in message ... John Wirt wrote: Can a VBA routine be written to change external links to manual updating after they have been initially created in a workbook and that workbook has been closed? I meant here that the workbook has been closed by the person who created it and the external links. I want the macro to change the updating to manual before the next person opens the workbook. Great question! 1) VBA does not have a method of its own to change the update method of a link 2) You can't change a file which is closed. However, you can use a VBA statement that executes an Excel 4 macro that will do the job for you while the workbook is open (I discovered this by recording a macro while changing a link's update method) V = ThisWorkbook.LinkSources(xlOLELinks) ' change to manual updating ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)" I would like to write a VBA procedure that can be included in a workbook template so that external links created in any workbooks created from the template can be manually changed from manual to autmatic updating. Now you've got me confused. You are now talking about changing to automatic updating. If you can't work out how to do what you want with the above help then please clarify at what stage you want the macro to run and whether it is changing the links to manual or automatic? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
Bill,
In your code, I do not understand how to dimension the variable, V. If I dimension it as an array, the VBA editor complains that "V" is not an array variable in the first statement. If I omit any dimension statement, VAB complains "Type Mismatch" on the second statement (as it should). John V = ThisWorkbook.LinkSources(xlOLELinks) ' change to manual updating ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)" "Bill Manville" wrote in message ... |
#5
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
If manual is dimmed in Edit=Links, then you won't be able to change it with
code. Note that Bill's code was for OleLinks, not Excel Links. Your idea is flawed, because the prompt to update links is done before any macro is run. You can use a master workbook that opens the other workbooks using code - then you can open them with the UpdateLinks option set in the open method. That is the only feasible solution I see. -- Regards, Tom Ogilvy "John Wirt" wrote in message ... Bill, Thank you for replying. It looks like you may have it here. I want the macro to change updating from automatic to manual in a workbook after that workbook has been created by an author and closed, but before it is opened by someone else for purposes of review.. I want the macro to autorun when the workbook is opened by someone other than the author and change the updating to manual before the links created by the author are automatically updated. The sequence is this: 1. The workbooks I am concerned about are created by authors from a template I give them. Often, the authors link their workbooks to external data sources. 2. The workbooks submitted by the authors are reviewed by others. Generally, the data sources that are linked to these workbooks are not submitted with the workbooks. The data source files are too big. 3. The problem is that when the workbooks are opened by others, they sometimes forget to answer "No" to the update links prompt.. When this happens, the data in the workbook can become corrupted (lot's of #REF cell values, etc.). To eliminate this problem, I want to change the template on which these workbooks are based (or manually run the macro on all workbooks that are submitted) so that any external links created in them by the authors only update manually. 4. We use Excel 2000 and I see no way of creating links that can be set to update manually. Automatic updated seems to be wired into Excel 2000. Edit-:Links has a options for Automatic and Manual updating but the Manual option is dimmed out -- even in the workbook when the links are created. 4. It would be feasible to manually run the macro I am looking for in all workbooks when they are submitted by authors to change the updating to manual. 5. As a last restor, I suppose we could change recalculation to manual but that would be very inconvenient for most people. This is the design problem: a. Excel 2000. b. Write a macro stored in a module in the template used to create many workbooks that can be manually run to switch the updating of external links created in the workbook to manual. c. While one is at it, it probably would be a good idea to have another macro for toggling link updating back to automatic. I know, I know, one solution to the problem is to buy Excel 2003. Not feasible at this time. John Wirt John "Bill Manville" wrote in message ... John Wirt wrote: Can a VBA routine be written to change external links to manual updating after they have been initially created in a workbook and that workbook has been closed? I meant here that the workbook has been closed by the person who created it and the external links. I want the macro to change the updating to manual before the next person opens the workbook. Great question! 1) VBA does not have a method of its own to change the update method of a link 2) You can't change a file which is closed. However, you can use a VBA statement that executes an Excel 4 macro that will do the job for you while the workbook is open (I discovered this by recording a macro while changing a link's update method) V = ThisWorkbook.LinkSources(xlOLELinks) ' change to manual updating ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)" I would like to write a VBA procedure that can be included in a workbook template so that external links created in any workbooks created from the template can be manually changed from manual to autmatic updating. Now you've got me confused. You are now talking about changing to automatic updating. If you can't work out how to do what you want with the above help then please clarify at what stage you want the macro to run and whether it is changing the links to manual or automatic? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
John Wirt wrote:
In your code, I do not understand how to dimension the variable, V. Dim V '[As Variant] Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|