Replies

See: http://www.cpearson.com/Excel/GetPrinters.aspx

Thanks. That is very helpful code. I ran across that yesterday and thought it was a bit complicated, but it does work great. I also ran across some suggestions here: http://www.ozgrid.com/forum/showthread/?t=10081. I combined the two a bit to come up with my test code to determine if the printer is installed.

However, I may have been overthinking this or asking the wrong question. I thought I needed to determine if a printer was installed before proceeding, but after messing around with all the above code I started thinking that maybe all I needed was something like this:

Code:

If ActivePrinter = “\DTCHYB-NCCH008PRT22789-RIC5210” Then
        GoTo SkipPrinter
    Else
        GoTo SkipLoop
    End If

I think when I switch between office and home all I need to do is check to see if the above printer is the active printer. If it’s not, then I am home. I’m gonna try this later at home (at the office right now).

Thanks!

-Rich

Instead of:
strNm = Split(Replace(Replace(.Name, “edocs “, “eDocs “), “- version”, “Version”), “-“)(0)
you could use:
strNm = “eDocs ” & Split(.Name, “-“)(1) & Split(.Name, “-“)(2)

Works perfectly. Thanks!!

-Rich B

There is no need to select anything, or to use SeekView or any of the associated code for moving about the document.

I hate to bug you, but your code is so much more elegant than mine. However, the StrNm line doesn’t grab the correct filename. It just inserts EDOCS into the footer. Here is a sample filename:

EDOCS-#6758166-v2A-Raleigh_NC_(1100_Corporate_Center_Drive)_(BE_141281-20000)_Tenant_Estoppel.docx

I want to change EDOCS to eDocs, get rid of the first hyphen and make it a space, delete the second hyphen, and delete the third hyphen and everything else so that it looks like this:

eDocs #6758166v2A

Can this be done with Split?

By the way, the version number is not always constant. It could be v1, v1A, v20A, v30C, etc. That probably doesn’t matter if you trigger the split by the hyphen, which is always there.

Thanks!

-Rich B

I haven’t tried the other suggested code yet, but if anybody else wants my more complicated code, here it is. I just updated it again this morning because it wasn’t working with a REALLY long filename.

Code:

Sub eDocsFooterNewNorm()
‘
‘ eDocs Filename Footer
‘ 2013-09-09; Corrected 2013-09-12; Modified 2015-10-29; 2015-11-02
‘
‘
Dim vFileNameFull As String
Dim vTrackChanges
Dim vFileNamePartial As String
Dim vNum As Integer
Dim vWhiteSpace
Dim vChar
Dim vBookMarkExists
Dim vViewType
   
    ‘Checks for Track Changes (resets Track Changes at end of macro)
    If ActiveDocument.TrackRevisions = True Then
        ActiveDocument.TrackRevisions = False
        vTrackChanges = 1
    Else
        vTrackChanges = 0
    End If
    
    ‘Checks for display of white space
    If ActiveWindow.View.DisplayPageBoundaries = True Then
        vWhiteSpace = 1
    Else
        vWhiteSpace = 0
    End If

    ‘Set bookmark for return location after macro completes
    With ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:=”ReturnHere”
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With

    ‘Ensures that viewing doc in page view (and saves current view)
    If ActiveWindow.View.SplitSpecial  wdPaneNone Then
        ActiveWindow.Panes(2).Close
    End If
    If ActiveWindow.ActivePane.View.Type = wdNormalView Then
        vViewType = “Normal”
    End If
    If ActiveWindow.ActivePane.View.Type = wdOutlineView Then
        vViewType = “Outline”
    End If
    If ActiveWindow.ActivePane.View.Type = wdMasterView Then
        vViewType = “Master”
    End If
    If ActiveWindow.ActivePane.View.Type = wdPageView Then
        vViewType = “Page”
    End If
    If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
        ActivePane.View.Type = wdOutlineView Or ActiveWindow.ActivePane.View.Type _
         = wdMasterView Then
        ActiveWindow.ActivePane.View.Type = wdPageView
    End If
        
    ‘Moves to top of Doc, Inserts Hard Return & Sets Font
    Selection.HomeKey Unit:=wdStory
    Selection.TypeParagraph
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    Selection.Style = ActiveDocument.Styles(“Normal”)
     
    ‘Inserts full eDocs filename
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
        “FILENAME  * Upper “, PreserveFormatting:=True
        ‘ Changed to upper 2013-10-18 so that sub-versions use upper case letters
    
    ‘Selects filename (from right to left) (modified 2013-10-18)
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
    Selection.Font.Name = “Arial”
    Selection.Font.Size = 8
    vFileNameFull = Selection.Text ‘Assigns filename to variable
    Selection.TypeText Text:=vFileNameFull ‘Inserts filename as plain text
‘    Selection.HomeKey Unit:=wdLine ‘Move IP to beginning of line
    Selection.HomeKey Unit:=wdStory ‘Move to top of doc for really long filename 2015-11-02
    Selection.Delete Unit:=wdCharacter, Count:=6 ‘Deletes “edocs ”
    Selection.TypeText Text:=”eDocs ” ‘Inserts “eDocs ” instead
    Selection.MoveLeft Unit:=wdWord, Count:=1 ‘Moves cursor before eDocs
    Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend ‘Selects eDocs
    Selection.NoProofing = True ‘Turns off spellcheck for eDocs (won’t save in custom .DIC as eDocs)
    Selection.MoveRight Unit:=wdCharacter, Count:=1 ‘Moves cursor right to turn off selection
        
    If Selection.Type = wdSelectionIP Then ‘Selects text to next hyphen (hyphen before version)
            vFileNamePartial = Selection.MoveEndUntil(Cset:=”-“)
            Selection.MoveRight Unit:=wdCharacter, Count:=1 ‘ Moves IP to right (just before hyphen)
    End If
    
    Selection.Delete Unit:=wdCharacter, Count:=1 ‘ Deletes hypen before “v” (version)
    Selection.TypeText Text:=”v”    ‘Enters a lower case “v” Modified 2013-10-18
    Selection.Delete Unit:=wdCharacter, Count:=1 ‘ Deletes the upper case “V”
    
    If Selection.Type = wdSelectionIP Then ‘ Selects text to hyphen after version)
            vFileNamePartial = Selection.MoveEndUntil(Cset:=”-“)
            Selection.MoveRight Unit:=wdCharacter, Count:=1 ‘ Moves IP to last hyphen before filename
    End If
       
    If Selection.Type = wdSelectionIP Then ‘Selects text to right of IP & up to CR
        vChar = Selection.MoveEndUntil(Cset:=vbCr, Count:=300) ‘ Extend selection to CR; Increased Count 2015-11-02
    End If
    
    Selection.Delete Unit:=wdCharacter, Count:=1 ‘ Deletes the remainder of the filename

    ‘Selects remaining filename, adds Bookmark, deletes filename & blank line at top of doc
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
    vFileNamePartial = Selection.Text ‘Assigns filename to variable
    Selection.Cut
    Selection.Delete Unit:=wdCharacter, Count:=1
    
    If ActiveDocument.Bookmarks.Exists(“eDocsName”) Then
        vBookMarkExists = 1
        ActiveDocument.Bookmarks(“eDocsName”).Select
        Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.InsertAfter vFileNamePartial
        ActiveDocument.Bookmarks.Add Range:=Selection.Range, Name:=”eDocsName”
        Selection.Font.Name = “Arial”
        Selection.Font.Size = 8
        GoTo ReturnToDocEditing
    Else
        vBookMarkExists = 0
        GoTo NoBookmark
    End If
     
NoBookmark:

‘   Need to determine what to do if there is no tab, which messes up some docs.
‘   Also, could use called functions because inserting the filename
‘   is the same code.

    ‘Moves insertion point to footer
    ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    If Selection.HeaderFooter.IsHeader = True Then
        ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
    Else
        ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    End If
    
    ‘Selects text to first tab (including the WF date stamp if it exists)
    If Selection.Type = wdSelectionIP Then ‘Selects text to right of IP & up to Tab
        vChar = Selection.MoveEndUntil(Cset:=vbTab, Count:=100) ‘ Modified 2013-10-03; extend selection to Tab
        ‘Selection.MoveEndWhile Cset:=”abcdefghijklmnopqrstuvwxyWBFD0123456789~`!@#$%^&*()-_+{}|[];:,./? “, Count:=wdForward
    End If
    
    ‘Deletes the selected text
    Selection.TypeBackspace
    
    Selection.PasteAndFormat (wdFormatOriginalFormatting)   ‘Pastes short filename
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend    ‘Selects filename
    With ActiveDocument.Bookmarks                       ‘Assigns bookmark name
        .Add Range:=Selection.Range, Name:=”eDocsName”
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    
ReturnToDocEditing:

    ‘Returns insertion point to main doc
    If vBookMarkExists = 1 Then
        ActiveWindow.ActivePane.Close   ‘Closes footer editing window
    Else
        ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    End If
    
‘   Original PageView Code
‘    If ActiveWindow.View.SplitSpecial = wdPaneNone Then ‘Returns to PageView
‘        ActiveWindow.ActivePane.View.Type = wdPageView
‘    Else
‘        ActiveWindow.View.Type = wdPageView
‘    End If
    
    If ActiveWindow.View.SplitSpecial = wdPaneNone Then ‘Returns to PageView
‘        ActiveWindow.ActivePane.View.Type = wdPageView
        If vViewType = “Normal” Then
            ActiveWindow.ActivePane.View.Type = wdNormalView
        End If
        If vViewType = “Outline” Then
            ActiveWindow.ActivePane.View.Type = wdOutlineView
        End If
        If vViewType = “Master” Then
            ActiveWindow.ActivePane.View.Type = wdMasterView
        End If
        If vViewType = “Page” Then
            ActiveWindow.ActivePane.View.Type = wdPageView
        End If
        
    Else
        If vViewType = “Normal” Then
            ActiveWindow.View.Type = wdNormalView
        End If
        If vViewType = “Outline” Then
            ActiveWindow.View.Type = wdOutlineView
        End If
        If vViewType = “Master” Then
            ActiveWindow.View.Type = wdMasterView
        End If
        If vViewType = “Page” Then
            ActiveWindow.View.Type = wdPageView
        End If
    End If
    
    ‘Returns insertion point to last edited location bookmark
    Selection.GoTo What:=wdGoToBookmark, Name:=”ReturnHere”
    ActiveDocument.Bookmarks(“ReturnHere”).Delete
    With ActiveDocument.Bookmarks
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    
    ‘Turns track changes back on if applicable
    If vTrackChanges = 1 Then
        ActiveDocument.TrackRevisions = True
    Else
        ActiveDocument.TrackRevisions = False
    End If
    
    ‘Turns white space display on or off as applicable
    If vWhiteSpace = 1 Then
        ActiveWindow.View.DisplayPageBoundaries = True
    Else
        ActiveWindow.View.DisplayPageBoundaries = False
    End If
       
End Sub

I’m glad you got it to work. Macros dealing with headers/footers are tricky because each Section of a document will have three headers and three footers (whether displayed or not). The total number of independent headers or footers depends on the link-to-previous setting (which can be different for each footer and each header in each section). A single page can have multiple sections. You will have to loop through the footers for each section.

A suggestion would be to implant your number as a document variable or property and use a field to display it. That would make it far harder for the casual user to change it or even find it. That field could be in a building block or AutoText and your macro could insert that. I believe document properties can be accessed from outside a document.

I understand that there might be multiple footers. A macro still seems the easiest way to go. This is mostly used by me and I’m sophisticated enough to know what to do if it doesn’t work correctly (I just ran across a bug in my code this morning because the macro couldn’t handle a really LONG filename). I would use a field if the field didn’t include the full filename. All I want is the file number applied to the file in the system. I can’t parse that out of a field. Is there a way to only get part of the filename in a field? Thanks.

There is no need to select anything, or to use SeekView or any of the associated code for moving about the document.

Wow. I will check this out. I figured there was a much shorter way to do what I am trying to do, but I am not an expert at Word macros so I just use what I know about and often what I can record.

Thanks!

-Rich B

Thoughts?

This is not a job for a macro. It may be a job for a template.
See the footer samples here.

It may also be a job for AutoText or a Building Block. Here are samples of these methods.

Sorry, I have not analyzed your code because I feel like you are trying to reinvent the wheel.

Thanks, but those options don’t get me where I want to be. We have a doc management system that generates a file name. It’s a long file name consisting of the doc number and other text. My macro strips out a lot of the file name and just leaves the number. I want to be able to hit a hotkey and have the file name added or updated in any document, whether we generate the doc or some third-party generates the doc. I don’t want to have to click in the footer and use AutoText or a Building Block. A template doesn’t really help because I get docs from other parties, re-save to my system, and add the file name. A template isn’t going to update the file name in the third-party document.

I probably shouldn’t have included the entire code. My only issue is with the wdSeekMainDocument error. I just wanted to know how to get around that. However, as is often the case, after I posted the question I found a solution. My macro code enters the footer two different ways depending upon whether or not a bookmark already exists. For one way, my original code worked. For the other way, I had to close the footer editing window differently. The code now works. I am still tweaking another issue, though. Once I’m done I will probably post the code in case others find it useful.

Thanks.

-Rich

I think I found the answer. Using the following works (vBox is my Shape):

Code:

   With vBox
        .RelativeHorizontalPosition = wdRelativeHorizontalPositionOuterMarginArea
        .Left = wdShapeRight
        .RelativeVerticalPosition = wdRelativeVerticalPositionPage
        .Top = wdShapeTop
    End With

As sometimes happens, I figured it out after posting the question. 😉

Thanks.

Fantastic. Thanks for the tip. I tried something like .Paragraphs after scouring the web, but couldn’t get it to work. Must have had my syntax wrong (obviously). Thanks.

I still have one issue, but I think I’ve resolved just about all my other issues. Here is what my draft stamp looks like now:

38530-TextBox2

Here is my code:

Code:

Sub CtrlMPeriod()
‘
‘ Ctrl+M,. — Draft Stamp 2014-11-26
‘
    Dim DraftNum As String
    Dim DraftWord As String
    Dim Result
    Dim TrackChanges
    Dim vBox As Shape
‘    Dim vShape As Shape    ‘Used with code to delete ALL Shapes in Doc
    Dim myShape As Shape, tmp As Shape  ‘Used with code to delete specific Text Box
    Dim vLeftMargin As String
          
    If ActiveDocument.TrackRevisions = True Then
        ActiveDocument.TrackRevisions = False
        TrackChanges = 1
    Else
        TrackChanges = 0
    End If
    
    Result = MsgBox(“Redlined draft?”, vbYesNo + vbQuestion)
    If Result = 6 Then
        DraftWord = “Wells Fargo Redlined Draft #”
        vLeftMargin = “4.9”
    Else
        DraftWord = “Wells Fargo Draft #”
        vLeftMargin = “5.5”
    End If
    
    DraftNum = InputBox$(“Enter draft number.”)
    
    If ActiveWindow.View.SplitSpecial  wdPaneNone Then
        ActiveWindow.Panes(2).Close
    End If
    
    If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
        ActivePane.View.Type = wdOutlineView Or ActiveWindow.ActivePane.View.Type _
         = wdMasterView Then
        ActiveWindow.ActivePane.View.Type = wdPageView
    End If
    
    ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    
    ‘Set bookmark to return to after macro completes
    With ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:=”ReturnHere”
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With

    For Each tmp In ActiveDocument.Shapes   ‘Searches collection for named text box
        If LCase(tmp.Name) = “drafttextbox1” Then
            Set myShape = tmp
            Exit For
        End If
    Next
    
    If Not (myShape Is Nothing) Then    ‘If collection is not empty, then deletes text box
        myShape.Delete
    End If
    
‘    For Each vShape In ActiveDocument.Shapes    ‘Deletes all Text Boxes
‘        If vShape.Type = msoTextBox Then vShape.Delete
‘    Next vShape
    
    Set vBox = ActiveDocument.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, _
        Left:=InchesToPoints(vLeftMargin), Top:=InchesToPoints(0.25), _
        Width:=InchesToPoints(3.5), Height:=InchesToPoints(0.5))
    
    With vBox
        .Name = “DraftTextBox1”
        .LockAspectRatio = msoFalse
        .LockAnchor = True
        .TextFrame.AutoSize = True
        .TextFrame.WordWrap = False
‘        .ShapeStyle = msoLineStylePreset3  ‘Didn’t work in all docs
        .Line.Weight = 2
        .Line.ForeColor = RGB(190, 75, 72)
        With .Shadow
            .Style = msoShadowStyleOuterShadow
            .Size = 100
            .Blur = 8.5
            .Visible = msoTrue
        End With
    End With
    
    With vBox.TextFrame.TextRange
        .Font.Name = “Calibri”
        .Font.Size = 12
        .Font.Bold = True
        .Paragraphs.Alignment = wdAlignParagraphCenter
        .Text = DraftWord + DraftNum + ” — ” + Format(Now(), “yyyy-mm-dd”) _
            + vbCr + “FOR DISCUSSION PURPOSES ONLY”
    End With
    
    ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    
    On Error GoTo SkipBookMark
    Selection.GoTo What:=wdGoToBookmark, Name:=”ReturnHere”
    ActiveDocument.Bookmarks(“ReturnHere”).Delete
    With ActiveDocument.Bookmarks
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    
SkipBookMark:
    If TrackChanges = 1 Then ActiveDocument.TrackRevisions = True
    
End Sub

The only thing I haven’t been able to figure out in formatting the text in the text box. I can insert one font & point size, but I can’t adjust these for the second line. It may look fine the way it is, but if there’s a way to adjust it, I’d like to know for future reference. Thanks.

Oh, and I have one more issue: How do I change the formatting of the second line (paragraph) in the text box? I can set the formatting for all of it, but if I use TextRange.Text, it overwrites what’s already in there. I suppose there’s some way to select the empty text in the box and use selection.text instead to insert text and format it rather than using a range. Any suggestions on how to do this?

I will keep poking around here and the web to see if I can find anything, but if anyone has the quick answer that would be great.

Thanks.

Thanks for the suggestions, Andrew. I may have mislead you (and others). I don’t want the draft stamp in the header. That’s what I do now, just with text. I want to convert to using a floating text box on the first page only. I have continued to poke around the web searching for macros with TextFrame in them and found some good stuff. This is what my draft stamp looks like so far:

38527-DraftStamp

This is the code I’ve come up with so far:

Code:

Sub CtrlMPeriod()
    Dim DraftNum As String
    Dim DraftWord As String
    Dim Result
    Dim TrackChanges
    Dim vBox As Shape
    Dim CurrentDate As String
        
    CurrentDate = Format(Now(), “yyyy-mm-dd”)
            
    If ActiveDocument.TrackRevisions = True Then
        ActiveDocument.TrackRevisions = False
        TrackChanges = 1
    Else
        TrackChanges = 0
    End If
    
    Result = MsgBox(“Redlined draft?”, vbYesNo + vbQuestion)
    If Result = 6 Then DraftWord = “Wells Fargo Redlined Draft #” Else DraftWord = “Wells Fargo Draft #”
    DraftNum = InputBox$(“Enter draft number.”)
    If ActiveWindow.View.SplitSpecial  wdPaneNone Then
        ActiveWindow.Panes(2).Close
    End If
    If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
        ActivePane.View.Type = wdOutlineView Or ActiveWindow.ActivePane.View.Type _
         = wdMasterView Then
        ActiveWindow.ActivePane.View.Type = wdPageView
    End If
    
    On Error GoTo SkipBox
    ActiveDocument.Shapes(1).Delete
    
SkipBox:
    
    Set vBox = ActiveDocument.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, _
        Left:=InchesToPoints(4.7), Top:=InchesToPoints(0.3), _
        Width:=InchesToPoints(3.5), Height:=InchesToPoints(0.5))
    
    With vBox
        .LockAspectRatio = msoFalse
        .LockAnchor = False
        .TextFrame.AutoSize = True
        .TextFrame.WordWrap = False
        .Line.Weight = 2
    End With
    
    With vBox.TextFrame.TextRange
        .Font.Name = “Calibri”
        .Font.Size = 12
        .Font.Bold = True
        .Paragraphs.Alignment = wdAlignParagraphCenter
        .Text = DraftWord + DraftNum + ” — ” + CurrentDate _
            + vbCr + “FOR DISCUSSION PURPOSES ONLY”
    End With
       
    If TrackChanges = 1 Then ActiveDocument.TrackRevisions = True
    
End Sub

This is far from final. I’m working with my header macro code and some of that is mostly inapplicable. I probably need to go get my code that saves a bookmark, goes to the top of the file, inserts the textbox, and then jumps back to the bookmark, but I’ll add that later.

In any event, what I can’t figure out is how to select the formatting options for the text box. I’ve been using TextFrame, but maybe I need to use Shapes per your code? I want it to have a red border and be shaded. Is there a way to select this formatting option from the ribbon bar, maybe with some type of “style” selection?

38528-BoxStyle

And what about shadow? I’d like to apply this shadow:

38529-Shadow

I’m going to keep poking around, but if you have any quick suggestions, that would be great.

When I’m done, I’ll post all my code here for others to use in case someone has the same issues.

Thanks!

Great. That helps. Although I like to have the outside of the text boundaries appear (which is how it works in Word 2010). Oh well. Thanks. -Rich

Both docs were open in Word 2013 when I took the screen shots. The “lines” appear after EVERY hard return in the “squiggly” screen shot. They might be “text boundaries”; I don’t know for sure. Turning off “text boundaries” doesn’t seem to be an option. I only have this issue in Word 2013. If I open these files in Word 2012, they both look the same when I turn on all display formatting options. I know that the red squiggly lines are spell check lines. If they are text boundaries, how do I turn those off? Any ideas? And why don’t they show in the other file? The .DOC file (the Union County doc) is open in compatibility mode. The other file is a DOCX file so it opens normally. If I re-save the DOC file as a DOCX file, the lines between EVERY paragraph reappear. Why? Is this some “function” of Word 2013 to display DOCX files? If so, it is really announying (but then again when has MS really cared if it annoys people). See add’l screen shots. Anyway, any thoughts on how to turn these off??

Thanks.

37619-Squiggly-Lines-Display237620-Non-Squiggly-Lines-Display2

Fantastic! Thank you very much!