Copy one page to a new page without losing the formulas or relationships

Started by jebuxx, January 23, 2015, 07:34:23 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

jebuxx

I have a graphics template on one page that will change size via impute on a order entry page.
I would like to copy this template to a new page and not lose the formulas or relations for the order enter page.
When I do copy this all the references are lost. example below....

=IF(OR(STRSAME(Pages[Data]!Sheet.333!Prop.ES,),"SF-06-TTN-A",1),STRSAME(REF(),"SF-07-TTN-A",1),STRSAME(REF(),"SF-09-TTN-A",1)),Sheet.6!PinY+Sheet.6!Height/2-Height/2-SETATREFEXPR(EVALTEXT((REF())))-SETATREFEXPR(EVALTEXT(REF()))/2,
IF(STRSAME(REF(),"SF-11-TTN-A",1),Sheet.85!PinY-Sheet.85!Height/2-Height/2,
IF(STRSAME(REF(),"SF-12-TTN-B",1),Sheet.63!PinY-Sheet.63!Height/2-Sheet.119!Height/2,
IF(STRSAME(REF(),"SF-12-TTN-BC",1),Sheet.56!PinY-Sheet.56!Height/2-Sheet.119!Height/2,
IF(STRSAME(REF(),"SF-06-TTN-AB",1),Sheet.75!PinY-Sheet.75!Height/2-Sheet.119!Height/2,
IF(STRSAME(REF(),"SF-07-TTN-AB",1),Sheet.80!PinY-Sheet.80!Height/2-Sheet.119!Height/2,
IF(STRSAME(REF(),"SF-09-TTN-AB",1),Sheet.91!PinY-Sheet.91!Height/2-Sheet.119!Height/2,Sheet.6!PinY+Sheet.6!Height/2-Height/2-SETATREFEXPR(EVALTEXT(REF())))))))))

Here what it needs to be...
=IF(OR(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-06-TTN-A",1),STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-07-TTN-A",1),STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-09-TTN-A",1)),Sheet.2!PinY+Sheet.2!Height/2-Height/2-SETATREFEXPR(EVALTEXT((Pages[Data]!Sheet.77!TheText)))-SETATREFEXPR(EVALTEXT(Pages[Data]!Sheet.396!TheText))/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-11-TTN-A",1),Sheet.106!PinY-Sheet.106!Height/2-Height/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-12-TTN-B",1),Sheet.84!PinY-Sheet.84!Height/2-Sheet.55!Height/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-12-TTN-BC",1),Sheet.77!PinY-Sheet.77!Height/2-Sheet.55!Height/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-06-TTN-AB",1),Sheet.96!PinY-Sheet.96!Height/2-Sheet.55!Height/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-07-TTN-AB",1),Sheet.101!PinY-Sheet.101!Height/2-Sheet.55!Height/2,
IF(STRSAME(Pages[Data]!Sheet.333!Prop.ES,"SF-09-TTN-AB",1),Sheet.112!PinY-Sheet.112!Height/2-Sheet.55!Height/2,Sheet.2!PinY+Sheet.2!Height/2-Height/2-SETATREFEXPR(EVALTEXT(Pages[Data]!Sheet.77!TheText)))))))))


I really not now want to have too re0write all the formulas over
HELP


wapperdude

I agree, re-writing those formulas wouldn't be fun.  I don't think there's a shapesheet solution for the off-page reference, i.e., "data".  Compounding the problem, you reference different shapes on that page, so a simple replacement of the "ref()" with pages[data]!sheet.abc! can't be used.  Probably needs a macro to create the formula.

If you did copy/paste, not sure why the other, on-page shape ID numbers are wrong.  Did you copy everything?  It may be that you'll have to auto-generate the diagram thru code.

Just some thoughts.

Wapperdude
Visio 2019 Pro

vojo

I am not sure about your specific problem, but I believe the following is true
   - formulas containing references to other shapes on same page are preserved if ALL shapes of the page are copied to new page
     (i.e make all shapes on given page one big group....copy to new page....then ungroup)
   - formulas containing references to other shapes on other pages are broken
     (i.e. if new pages is inserted somewhere before the other page reference page, I believe it changes the page numbers...thus breaks reference)

You might be able to do the following if you really have to edit all this
1:  Create user cells that contain the references to the desire cells elsewhere
2:  build this shape's formulas off those user cells
3:  If there are copy paste problems....just update the user cells....other cells fall in line

In your case
   user.ref1 = pages[data]!sheet.333!prop.es
   if(or(strsame(user.ref1,"SF_06_TTN_A) <blah blah blah blah>

I have found this handy for things like
- needing to add many connection points and want to change spacing quickly
       User.slice
       user.scope
       connection.x1 = width*0+1*user.slice/user.scope
                         2                  2
- have many child shapes where each uses a few parent shape attributes
        this becomes a simple copy paste exercise
- etc.

JuneTheSecond

I am using Visio 2013 that has no problem by manual copy & Paste or by a Macro, when I examined with a simple drawing.
What version are you using?

Macro is simple.

Option Explicit

Sub test()
    Dim myselection As Visio.Selection
    ActiveWindow.SelectAll
    Set myselection = ActiveWindow.Selection
    myselection.Copy
    ThisDocument.Pages.Add
    ActivePage.Paste
End Sub
Best Regards,

Junichi Yoda
http://june.minibird.jp/

Nikolay

I think you could post a sample file illustrating the issue, this would be helpful.
Normally the references which can be preserved are preserved by Visio copy/paste.

Meanwhile, you could try the Duplicate Page command (Visio 2013+). Right click the page -> Duplicate.
Or (in previous versions of Visio) right click -> "Copy" without selecting anything, and then "Paste" to the new page.
Maybe there are some hidden shapes or shapes protected from selection, this should copy them as well.

jebuxx

JuneTheSecond - I am using Visio 2010 - I did try the macro you supplied and it did copy the page to another however the shape sheet referances were lost.

Nikolay, the file is to large to post here. I will email the files to you and JuneTheSecond and whom ever else would like to take a crack at this.

thanx again!!

jebuxx

Hi WapperDude,  I am using Visio 2010
I did select all when I copied the template to a new page to duplicate is and as mentioned all the shapesheet formula referances were lost.
I did try the macro that JuneTheSecond supplied and is worked well to copy to a new page but here too the shape sheet referances were lost.
I would like to post the file but it is too large. 1.45mb   Who ever would like to take a stab it this I can email the file. Please supply your address.
I believe you will find this very interesting.
Thanx again for all the help everyone!!!

Jim


jebuxx

OK, I was able to save the file down to a loadable size. Now attached. There are 3 pages
Summary, Data and Template.
The item info that drives the template is the Data page. Note the the Fold Style is a drop down list. (now you see why I do not want to
re-create all the formulas as it would be a daunting task. When the sizes are changed on the data page the template adjusts to those sizes.
I need to have a duplicate page of the template that still works off the Data Page. I have tried copying the entire template and pasting to a new page but as
mentioned all the shape sheet formulas lose their referances.
I have tried what both JumeTheSecond and Nikolay have suggested but no deal.
Thanx again all for your help.

Jim

JuneTheSecond

Ive tried duplicate the page Template of your Visio drawing, but could not find broken reference.
I am using Visio 2013.
I cannot remember exactly, but I have an experience that the reference broken on copy & paste in older version.
But now in Visio 2013,  I cannot find this problem.
If you like, you can try to install trial version of Visio 2013.
If your problem does not happen, buy Visio 2013.
Best Regards,

Junichi Yoda
http://june.minibird.jp/

Croc

You may test a first version of macro.
1. Import module "CopyModule" into VBA project.
2. Open "Template" page.
3. Run macro "CopyPageWithRef".
For the time being macro is very raw and need be optimized.
(Visio 2007)

Nikolay

1. It seems to work fine with Visio 2013. In Visio 2007/2010, it looks like the formulas in your case are actually broken on paste for whatever reason.
You could download Visio 2013 Trial, then use "Duplicate page" (or just copy-paste), then save back as Visio 2007/2010 (vsd)

2. You could have tried to use GUARD() function to protect formulas which are not supposed be changed by user UI, i.e. wrap your formulas in GUARD, then copy-paste should probably also work with Visio 2007/2010 normal copy-paste.

It looks we have found something which works better in 2013 than in the previous editions?  :o   ;D

jebuxx

I did grab a trail version of Visio 2013 and low and behold the copy and paste worked!!!
Keeping all the shape sheet formulas in tact 
Thank you all for your help