Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
I have a workbook A comprising one worksheet that is linked to another
workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
maybe you could use some code like this to create your link based on the sheet
name instead of hard coding it sheets(1) is the first sheet, so if the order is always the same, the name won't matter. nm = Sheets(1).Name Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4" if the sheet name (nm) changes, the formula still works -- Gary "Spike" wrote in message ... I have a workbook A comprising one worksheet that is linked to another workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
Sounds very good to me, i think i follow what you say which is very clever,
would you then get the macro to stick that (adapted of course for each link etc) = "='" & nm & "'!" & "A4" (relevant cell ref) into each cell which currently has an Excel formula in namely = path & workbookname & sheet etc & A4 -- with kind regards Spike "Gary Keramidas" wrote: maybe you could use some code like this to create your link based on the sheet name instead of hard coding it sheets(1) is the first sheet, so if the order is always the same, the name won't matter. nm = Sheets(1).Name Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4" if the sheet name (nm) changes, the formula still works -- Gary "Spike" wrote in message ... I have a workbook A comprising one worksheet that is linked to another workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
post back if you run into any issues
-- Gary "Spike" wrote in message ... Sounds very good to me, i think i follow what you say which is very clever, would you then get the macro to stick that (adapted of course for each link etc) = "='" & nm & "'!" & "A4" (relevant cell ref) into each cell which currently has an Excel formula in namely = path & workbookname & sheet etc & A4 -- with kind regards Spike "Gary Keramidas" wrote: maybe you could use some code like this to create your link based on the sheet name instead of hard coding it sheets(1) is the first sheet, so if the order is always the same, the name won't matter. nm = Sheets(1).Name Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4" if the sheet name (nm) changes, the formula still works -- Gary "Spike" wrote in message ... I have a workbook A comprising one worksheet that is linked to another workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
in some applications, i need to build the formulas on the fly, so they're in an
auto_open macro. when the workbook is opened, the relevant formulas are created. -- Gary "Spike" wrote in message ... Sounds very good to me, i think i follow what you say which is very clever, would you then get the macro to stick that (adapted of course for each link etc) = "='" & nm & "'!" & "A4" (relevant cell ref) into each cell which currently has an Excel formula in namely = path & workbookname & sheet etc & A4 -- with kind regards Spike "Gary Keramidas" wrote: maybe you could use some code like this to create your link based on the sheet name instead of hard coding it sheets(1) is the first sheet, so if the order is always the same, the name won't matter. nm = Sheets(1).Name Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4" if the sheet name (nm) changes, the formula still works -- Gary "Spike" wrote in message ... I have a workbook A comprising one worksheet that is linked to another workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update links macro
Thank you very much for your help works ok from this end, really grateful,
off for the weekend now will jproperly code it all up on monday and i am sure will be fine, but appreciate your help. -- with kind regards Spike "Gary Keramidas" wrote: in some applications, i need to build the formulas on the fly, so they're in an auto_open macro. when the workbook is opened, the relevant formulas are created. -- Gary "Spike" wrote in message ... Sounds very good to me, i think i follow what you say which is very clever, would you then get the macro to stick that (adapted of course for each link etc) = "='" & nm & "'!" & "A4" (relevant cell ref) into each cell which currently has an Excel formula in namely = path & workbookname & sheet etc & A4 -- with kind regards Spike "Gary Keramidas" wrote: maybe you could use some code like this to create your link based on the sheet name instead of hard coding it sheets(1) is the first sheet, so if the order is always the same, the name won't matter. nm = Sheets(1).Name Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4" if the sheet name (nm) changes, the formula still works -- Gary "Spike" wrote in message ... I have a workbook A comprising one worksheet that is linked to another workbook B of six sheets; there are several links from A to each sheet in B. Workbook B is generated by a macro on another workbook and saved down with the same name so it overwrites the previous workbook B. However the sheet names change slightly to reflect the date of creation; so the first sheet will change from Sheet1Name+old date to Sheet1Name+new date and Sheet2Name+old date to Sheet2Name +new date and so on. I have workbook A open as i am running a macro already on that workbook and then go to the other workbook to run a macro to create a new workbook B. How do i get workbook A to update all the links automatically to the new workbook B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link to:' Is it possible to use a macro to select each sheet in turn in the dialogue box. If it is possible i will be really grateful for the relevant code. Many thanks -- with kind regards Spike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update links when Macro ends | Excel Programming | |||
Excel Macro - Auto Update Links | Excel Programming | |||
Macro after Update of Links | Excel Programming | |||
Update links to other workbooks while running macro | Excel Programming | |||
How do I set up a macro to automatically update links upon openin. | Excel Programming |