Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
Hello,
I like to have a macro that made a copy of the actual sheet into a new workbook (create a copy - new book). Then the macro has to save directly the file into the directory C:/ Apps with the name "Template-date-time.xls" (date and time as actual). After that the action should be taken: menuaction "Edit - Links - Change Source" and then change the source to the before saved filename. Why this action.... because my file has buttons on it. When I copy the sheet with the buttons on it, it will get "links" to the original file because of the VBA codes on it. Possible ?? or another solution ? greetings from John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
I'm not sure what your buttons do, but you may want to replace the buttons from
the Forms toolbar with commandbuttons from the control toolbox toolbar. You'll have to move the code (and modify it (probably)) to make it work. But since the commandbutton's code is within the sheet module that owns the commandbutton, it'll be copied when the sheet is copied. johan wrote: Hello, I like to have a macro that made a copy of the actual sheet into a new workbook (create a copy - new book). Then the macro has to save directly the file into the directory C:/ Apps with the name "Template-date-time.xls" (date and time as actual). After that the action should be taken: menuaction "Edit - Links - Change Source" and then change the source to the before saved filename. Why this action.... because my file has buttons on it. When I copy the sheet with the buttons on it, it will get "links" to the original file because of the VBA codes on it. Possible ?? or another solution ? greetings from John -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
Hoi,
I'm using Excell 2003. I'd made a worksheet with on it a couple of buttons created by the menu-option view - toolbars - forms - button and then linked to the module which is written in the VBA screen of the sheet known as "microsoft excel objects" - Sheet1(template). When I copy the sheet as written before (select sheettab - left mouse button - create copy - To new book) then the complete sheet inclusive the buttons are copied to a new file. This works oke. In this new file also the VBA code is copied with it. So this is also oke. But,... the buttons refers not to the VBA code in this new workbook, but refers to the VBA code in the original file. Now you have a "link" as you can see by menu-option Edit - Links. Now it is possible to solve the problem by hand: or- 1) relink the buttons to the correct VBA code in this new file (select the button - left mousebutton - assign macro - select the code within this copied workbook). or- 2) save this new workbook, don't close it, and then go to menu- option edit - links - change source and select this file. My wish is to get a solution automatic. I wish to copy the worksheet into a new workbook, save the file as mentioned befo "Template-date- time.xls" (date and time as actual). and fulfill the menu-option edit - links - change source and select this file. hopely somebody can give me the solution. regards, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
I understood.
Did you reject the other suggestion? johan wrote: Hoi, I'm using Excell 2003. I'd made a worksheet with on it a couple of buttons created by the menu-option view - toolbars - forms - button and then linked to the module which is written in the VBA screen of the sheet known as "microsoft excel objects" - Sheet1(template). When I copy the sheet as written before (select sheettab - left mouse button - create copy - To new book) then the complete sheet inclusive the buttons are copied to a new file. This works oke. In this new file also the VBA code is copied with it. So this is also oke. But,... the buttons refers not to the VBA code in this new workbook, but refers to the VBA code in the original file. Now you have a "link" as you can see by menu-option Edit - Links. Now it is possible to solve the problem by hand: or- 1) relink the buttons to the correct VBA code in this new file (select the button - left mousebutton - assign macro - select the code within this copied workbook). or- 2) save this new workbook, don't close it, and then go to menu- option edit - links - change source and select this file. My wish is to get a solution automatic. I wish to copy the worksheet into a new workbook, save the file as mentioned befo "Template-date- time.xls" (date and time as actual). and fulfill the menu-option edit - links - change source and select this file. hopely somebody can give me the solution. regards, -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
Dave,
I'm sorry. I didn't understood the first part of the description. Now I'm understand it (use of embedded buttons) and....... it works oke. No links anymore after copying the sheet into a new workbook. This is really a simple solution. THANKS. regards, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change link-source on file after copy the sheet into new a newworkbook
There are benefits to each kind of button/commandbutton. And problems with
each, too. But in your case, it just seemed to scream out for the commandbutton suggestion. Glad it worked for you. johan wrote: Dave, I'm sorry. I didn't understood the first part of the description. Now I'm understand it (use of embedded buttons) and....... it works oke. No links anymore after copying the sheet into a new workbook. This is really a simple solution. THANKS. regards, -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Link Source | Excel Discussion (Misc queries) | |||
Link source - Need change? | Excel Programming | |||
Change Link Source | Excel Programming | |||
How change link source in long formula when source moved | Excel Programming | |||
Change Source of Link in Protected Sheet | Excel Discussion (Misc queries) |