Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Variables 2010

  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Variables 2010

    Can anyone tell me if variables work differently from 2003 to 2010.

    I am having problems with 1 issue where I have developed a macro for moving specific pages backwards and forwards. Staff create reports following assessments at clients and if there aere issues noticed raise corrective action reports (CARs). They insert the CARs by clicking a button under Add Ins, which locates the template and inserts at a specific point in the report. The CARs are numbered using the SEQ field. If multiple Cars are inserted, the macro always inserts the CARs after the last one.
    Occasionally staff wish to reorder the CARs to enable this I have created another macro where they are asked which CAR (number) they wish to move and after which CAR they wish to move it to. 2 variables are created, CARToMove and MoveTo.
    In Word 2003 this has worked fine for some 7 or so years. Since the introduction of Win7 and Office 2010 to CARToMove works fine but the MoveTo variable doesn't appear to be recognised anymore!

    Code
    Code:
    Private Sub cmdOK_Click()
    'Modified 23 November 2011 by Phil Carter
    'Modified to prevent opening in multipage view and remove extra line feed
    'when cutting page to move
        Application.ScreenUpdating = False
        
        strGoToCARNum = txtCARNum
        strMoveAfterCARNum = txtMoveTo
        
        intGoToCARNum = Val(txtCARNum)
        intMoveAfterCARNum = Val(txtMoveTo)
        
            ActiveWindow.ActivePane.View.Zoom.PageFit = wdPageFitBestFit
        
        Selection.HomeKey Unit:=wdStory
        Selection.GoTo What:=wdGoToField, Which:=wdGoToNext, Count:=(intGoToCARNum), _
        Name:="SEQ"
        Selection.MoveUp Unit:=wdLine, Count:=3
    '    Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.Find.ClearFormatting
        Selection.Extend
        
        With Selection.Find
            .Text = "^m"
            .Forward = True
            .Wrap = wdFindAsk
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute
            If .Found Then
                Selection.Cut
            End If
        End With
        
    '    Selection.InsertBreak Type:=wdPageBreak
        If intMoveAfterCARNum = 0 Then
            Selection.GoTo What:=wdGoToBookmark, Name:="CAR1"
            Selection.Find.ClearFormatting
            With Selection.Find
                .Text = "^m"
                .Replacement.Text = ""
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With
            Selection.MoveRight Unit:=wdCharacter, Count:=1
        
            Selection.Paste
        Else
            Selection.HomeKey Unit:=wdStory
            Selection.GoTo What:=wdGoToField, Which:=wdGoToNext, Count:=(intMoveAfterCARNum), _
            Name:="SEQ"
            
            Selection.MoveUp Unit:=wdLine, Count:=3
            Selection.HomeKey Unit:=wdLine
            
            Selection.Find.ClearFormatting
            
            With Selection.Find
                .Text = "^m"
                .Forward = True
                .Wrap = wdFindAsk
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                .Execute
                If .Found Then
                    Selection.MoveRight Unit:=wdCharacter, Count:=1
                End If
            End With
            
            Selection.Paste
        End If
        
        Update
        
        Unload frmShiftCAR
    End Sub
    Last edited by Gary Frieder; 2012-02-16 at 14:59. Reason: Added code tags
    cheers

    Phil Carter

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    I'm not aware of any particular change. However, your code seems inefficient. Try something along the lines of:
    Code:
    Private Sub cmdOK_Click()
    Application.ScreenUpdating = False
    Dim RngOld As Range, RngNew As Range
    Application.ScreenUpdating = False
    intGoToCARNum = Val(txtCARNum)
    intMoveAfterCARNum = Val(txtMoveTo)
    With ActiveDocument
      If .Sections.Count < 1 Then Exit Sub
      If .Sections.Count > intGoToCARNum Then intGoToCARNum = .Sections.Count
      If intMoveAfterCARNum >= .Sections.Count Then intMoveAfterCARNum = intMoveAfterCARNum - 1
      If intMoveAfterCARNum = intGoToCARNum Then Exit Sub
      If intMoveAfterCARNum < 0 Then intMoveAfterCARNum = 0
      Set RngOld = .Sections(intGoToCARNum).Range
      Set RngNew = .Sections(intMoveAfterCARNum + 1).Range
      RngNew.Collapse wdCollapseStart
      RngOld.Cut
      RngNew.Paste
      .Fields.Update
    End With
    Set RngOld = Nothing: Set RngNew = Nothing
    Application.ScreenUpdating = True
    Unload frmShiftCAR
    End Sub
    Note: In the above I'm guessing a bit as to what values some of your variables pass.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul hi

    I note in your reply you are working with sections. In my code I search for a value set in the Sequence [SEQ] field, hence the .Fields.Update line to remiber the moved pages. when I amend the macro using your code I get the following error at the Set RngOld = .Sections... line

    MoveMacro.JPG
    cheers

    Phil Carter

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    I mis-read your code - interpreted ^m as a Section break, whereas it's actually a manual page break. I post back with some revised code later - unless you want to change your page breaks to Section breaks ...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Hi Paul thanks again

    I think I'll leave it at page breaks as sections make give problems with moving the pages and page numbering etc.

    Look forward to your code
    cheers

    Phil Carter

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    Try:
    Code:
    Private Sub cmdOK_Click()
    Application.ScreenUpdating = False
    Dim RngOld As Range, RngNew As Range, i As Long
    Application.ScreenUpdating = False
    intFromCARNum = Val(txtCARNum)
    intToCARNum = Val(txtMoveTo)
    With ActiveDocument
      i = .Range.ComputeStatistics(wdStatisticPages)
      If i = 1 Then Exit Sub
      If intFromCARNum > i Then Exit Sub
      If intFromCARNum < 1 Then Exit Sub
      If intToCARNum > i Then Exit Sub
      If intToCARNum < 1 Then Exit Sub
      If intToCARNum = intFromCARNum Then Exit Sub
      If intToCARNum = i Or intFromCARNum = i Then .Range.InsertAfter Chr(12)
      Set RngNew = .GoTo(What:=wdGoToPage, Name:=intToCARNum)
      Set RngOld = .GoTo(What:=wdGoToPage, Name:=intFromCARNum)
      Set RngOld = RngOld.GoTo(What:=wdGoToBookmark, Name:="\page")
      RngNew.Collapse wdCollapseEnd
      RngOld.Cut
      RngNew.Paste
      If intToCARNum = i Or intFromCARNum = i Then
        While .Range.Characters.Last.Previous < Chr(32)
          .Range.Characters.Last.Previous.Delete
        Wend
      End If
      .Fields.Update
    End With
    Set RngOld = Nothing: Set RngNew = Nothing
    Application.ScreenUpdating = True
    Unload frmShiftCAR
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul thanks for your input

    I can't seem to get it work as required. Not sure I understand how (why) you areusing the ComputerStatistics object.

    I have created a document, attached, that explains how the macro was designed and previousely worked. I was thinking (sometime thorugh the night, as you do!) maybe it is a Win 7 addition that is causing the problem.
    Attached Files Attached Files
    cheers

    Phil Carter

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    The macro works on the premise that each CAR occupies its own page. That's why it works with page ranges. Is that premise wrong?

    The ComputerStatistics is used to get the number of pages in the document so that, via the destination CAR#, the user can't specify an invalid destination.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul thnaks again

    Confused me a bit as you had renamed some variables and not defined them!

    The logic appears confused. Firstly Itried to move CAR #4 to position #2 but the macro moved CAR #1 to be the first page of the report. I then tried to move CAR #! to be #3 and it moved the covering letter to page #3.

    I've attached the dummy report report I have been usi nf ofr testin g. It has 4 CARs inserted. Also included is the CAR oage.

    In the past we have had problems with staff deleting or modifying the Normal.dot so we have locked this down by creating our own version, ianznorm.dotm, which is installed as the startup template and contains all the macros required to generate the reports. In Office 2010 this file resides in DC\Users\Public\Templates\Starup\. I tried to upload the dotm file but his type seems to be blocked
    Attached Files Attached Files
    cheers

    Phil Carter

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,
    Confused me a bit as you had renamed some variables and not defined them!
    Au contraire, the only undefined variables were those you already had - I assumed any required definitions for them would have been somewhere else in your existing code.
    The logic appears confused. Firstly Itried to move CAR #4 to position #2 but the macro moved CAR #1 to be the first page of the report. I then tried to move CAR #! to be #3 and it moved the covering letter to page #3.
    The logic was OK, it's just that it didn't allow for your front matter or back matter, which I wasn't aware of. Now that I know about it, it's easily accomodated and the code can be simplified in some respects. Try the following (the only undefined variables now are for your existing 'txtCARNum' and 'txtMoveTo'):
    Code:
    Application.ScreenUpdating = False
    Dim i As Long, LngFromCAR As Long, LngToCAR As Long
    Dim LngFrnt As Long, LngBk As Long, RngOld As Range, RngNew As Range
    '# Pages for front matter & back matter, respectively
    LngFrnt = 4: LngBk = 1
    LngFromCAR = CLng(txtCARNum)
    LngToCAR = CLng(txtMoveTo)
    With ActiveDocument
      'Validate
      i = .Range.ComputeStatistics(wdStatisticPages) - LngFrnt - LngBk
      If i < 2 Then Exit Sub
      If LngFromCAR > i Then Exit Sub
      If LngFromCAR < 1 Then Exit Sub
      If LngToCAR > i Then Exit Sub
      If LngToCAR < 1 Then Exit Sub
      If LngToCAR = LngFromCAR Then Exit Sub
      'Define from & to ranges
      Set RngNew = .GoTo(What:=wdGoToPage, Name:=LngToCAR + LngFrnt - 1)
      Set RngOld = .GoTo(What:=wdGoToPage, Name:=LngFromCAR + LngFrnt - 1)
      Set RngOld = RngOld.GoTo(What:=wdGoToBookmark, Name:="\page")
      'Re-order the ranges
      RngOld.Cut
      RngNew.Paste
      .Fields.Update
    End With
    Set RngOld = Nothing: Set RngNew = Nothing
    Application.ScreenUpdating = True
    Note: As I still haven't seen your userform, I still don't know what the user is meant to input for the 'txtMoveTo' variable. I've assumed it's the CAR# before which to relocate the CAR # being moved. If it's supposed to be the CAR# after which to relocate the CAR # being moved, change 'LngToCAR = CLng(txtMoveTo)' to 'LngToCAR = CLng(txtMoveTo) + 1'
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Thanks Paul

    Seems to have fixed the problem

    The user form
    MoveCAR.JPG

    Now uses the code you created
    cheers

    Phil Carter

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    Your userform implies the second CAR# is being replaced. That's not what the code does. If replacement is required, different code is needed. Alternatively, if the first CAR# is meant to be be moved before/after the 2nd CAR# (which is retained), the prompt should be changed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul sorry

    Yes I will change the prompt as it is intended that the first CAR takes the "position" of but does not replace the second CAR. i.e. CAR #2 of 4, move to become CAR #4 of 4
    cheers

    Phil Carter

  14. #14
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul hi again

    In your latest code you have specified; LngFrnt = 4: LngBk = 1. I am not sure what these do but seem to indcate the number of pages in the document. The reports can range from a basic 4 pages up to 20 plus.

    I tested on the attached and tried the following;
    move CAR 2 to CAR 3 = no change
    move CAR1 to CAR 3 = moved CAR1 to CAR 2
    move CAR 3 to CAR 1 = OK

    I have attached the sanitised report I tried with 4 CARs inserted for you to play with
    Attached Files Attached Files
    cheers

    Phil Carter

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Phil,

    This would be much easier to test, etc, if you posted a copy with the form & code.

    FWIW, 'LngFrnt = 4' tells the macro there are four pages before the first CAR and 'LngBk = 1' tells the macro there is one page after the last CAR. I'd have thought that was obvious from the preceding code comment:
    Code:
    '# Pages for front matter & back matter, respectively
    Last edited by macropod; 2012-02-27 at 01:25.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •