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 possible? John Wirt |
#2
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
Excel likes to other excel workbooks are always automatic.
Beyond that, if you can do it manually, turn on the macro recorder and you have your code. -- Regards, Tom Ogilvy John Wirt wrote in message ... 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 possible? John Wirt |
#3
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
Unfrotunately, no, I do not think that link updating can be turned off using
any available menu selections. John "Tom Ogilvy" wrote in message ... Excel likes to other excel workbooks are always automatic. Beyond that, if you can do it manually, turn on the macro recorder and you have your code. |
#4
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford,
England, says that there is an Excel 4 command that works: - - - - - - - - - VBA does not have a method of its own to change the update method of a link. 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)" - - - - - - However, this macro will not run as a VBA procedure, no matter how I try to define V as a variable. What are Excel4Macros? John Wirt - - - - - "Tom Ogilvy" wrote in message ... Excel likes to other excel workbooks are always automatic. Beyond that, if you can do it manually, turn on the macro recorder and you have your code. -- Regards, Tom Ogilvy |
#5
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
What can't be done manually generally can't be done with code when it comes
to settings and so forth. reference changing a link from automatic to manual - manual grayed out in the menu, can't be done. -- Regards, Tom Ogilvy "John Wirt" wrote in message ... Unfrotunately, no, I do not think that link updating can be turned off using any available menu selections. John "Tom Ogilvy" wrote in message ... Excel likes to other excel workbooks are always automatic. Beyond that, if you can do it manually, turn on the macro recorder and you have your code. |
#6
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
That isn't really what he said. He said he recorded a macro when he did it
manually. You can only manually change an OLE link to manual - so good chance that you are not using OLE links (based on your post in links). therefore passing it a link source that can't be changed would raise an error in any version of VBA/Excel or even the native Macro 4 macro language. Also, macros don't fire before the prompt so your concept of doing it on opening is flawed as well. I suggested a method you could use in Links. -- Regards, Tom Ogilvy "John Wirt" wrote in message ... In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford, England, says that there is an Excel 4 command that works: - - - - - - - - - VBA does not have a method of its own to change the update method of a link. 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)" - - - - - - However, this macro will not run as a VBA procedure, no matter how I try to define V as a variable. What are Excel4Macros? John Wirt - - - - - "Tom Ogilvy" wrote in message ... Excel likes to other excel workbooks are always automatic. Beyond that, if you can do it manually, turn on the macro recorder and you have your code. -- Regards, Tom Ogilvy |
#7
|
|||
|
|||
Writing a macro to change external links to manual updating in Excel 2000
John Wirt wrote:
In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford, England, says that there is an Excel 4 command that works: I assumed you were talking about remote links (to non-Excel files) rather than external links (to cells in other workbooks). As Tom has said, external links are always automatic. I also agree with Tom in that the only solutions open to you a - Excel 2002 or later which allows you to specify that the Update Links question be not asked at startup. - In Excel 2000 you could replace formulas containing the links with values (I would suggest doing this when first saving the workbook rather than on the first open - at which time you can't prevent the Update Links question) - Use another workbook to open the real workbook, specifying UpdateLinks:=0 Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|