Posts Tagged ‘macros’

h1

Word: Macro to insert a landscape page

May 14, 2019

A client wanted an easy way for her staff to insert a landscape page into a report without messing up the headers and footers. I recorded a macro that does just that, but it relies on headers and footers being laid out using the alignment tabs, not borderless tables. Alignment tabs are much better than inserting tabs yourself as they automatically adjust if you change the page layout.

Note: If you use borderless tables for your headers and footers, this macro will still work, but you’ll have to manually turn off the Link to Previous options for the landscape page’s headers and footers and also for the following portrait page. I might see if I can tweak the macro to do that—if so, I’ll write it up as a separate blog post.

Step 1: Set up alignment tabs for the headers and footers

  1. Open a new Word document (or the template if you want to set this up in your template).
  2. Double-click in the header area to open the header. The cursor will be at the left margin.
  3. Optional: Add any text you want positioned at the left.
  4. In the Header & Footer Tools tab > Position group, click Insert Alignment Tab.
  5. By default the next position is Center. Make sure it’s selected, then click OK.
  6. Optional: Add any text you want positioned in the centre.
  7. Click Insert Alignment Tab again.
  8. Select Right then click OK.
  9. Optional: Add any text you want positioned at the right.
  10. In the Header & Footer Tools tab > Navigation group, click Go to Footer.
  11. Repeat steps 3 to 9 above for the footers.
  12. Double-click in the main body of the document to close the header/footer area.

Step 2: Install and run the macro

The macro you will use to insert a landscape section is listed below these steps.

  1. Go to the View tab >Macros group, then select View Macros from the drop-down list.
  2. In the Macro Name field, type InsertLandscape (if you have other macros listed, the one listed first will be in that field—just type over it)
  3. Click Create. This opens the Visual Basic for Applications (VBA) window, with these lines already inserted:
    Sub InsertLandscape()
    ‘ InsertLandscape macro
    End Sub
  4. Copy the macro below this set of steps.
  5. Paste it into the blank area above End Sub.
  6. Save the change and close the VBA window. (If you’re working in a template and only want this macro available to documents based on that template, then select the template name from the Macros in field.)
  7. Test the macro by running it:
    • Go to the View tab >Macros group, then select View Macros from the drop-down list.
    • Select the InsertLandscape macro.
    • Click Run.
  8. A landscape section should be inserted into your document, and the headers and footers should all align correctly for the landscape section.

Macro

' Inserts a landscape section, adjusts headers and footers
' Created by Rhonda Bracey, CyberText Consulting, May 2019
'
Selection.TypeParagraph
Selection.InsertBreak Type:=wdSectionBreakNextPage
Selection.TypeParagraph
Selection.TypeParagraph
Selection.InsertBreak Type:=wdSectionBreakNextPage
Selection.MoveUp Unit:=wdLine, Count:=2
If Selection.PageSetup.Orientation = wdOrientPortrait Then
Selection.PageSetup.Orientation = wdOrientLandscape
Else
Selection.PageSetup.Orientation = wdOrientPortrait
End If
ActiveWindow.ActivePane.SmallScroll Down:=-3
ActiveWindow.ActivePane.LargeScroll Down:=1

What this macro does:

  • Inserts an empty paragraph.
  • Inserts a Next Page section break.
  • Inserts two more empty paragraphs.
  • Inserts another Next Page section break.
  • Moves the cursor back up to one of the empty paragraphs between the two section breaks.
  • Changes the area between the section breaks to Landscape orientation.
h1

Word: Macro-enabled templates stripped by Dropbox?

May 2, 2019

Well, well, well! I haven’t encountered this before, but it looks as though Dropbox.com stripped the macros from two Word templates I created for a client earlier this week.

I’d emailed her the templates (dotm files) on Tuesday, and sent some follow-up notes on Wednesday. Today (Thursday) she contacted me to ask if I’d sent the templates because she hadn’t received them. I thought that maybe her email provider’s security settings were stopping them getting through, so I zipped them up and emailed her the zip file. The zip file didn’t get to her either, so I created a folder for her in my Dropbox.com account and put the individual template files there and sent her the link to the folder.

She was able to download the templates, but when she tried to run them, she got error messages about the macros. She contacted her IT people and me. I’ve just got off the phone with her IT guy—he also had trouble with the templates and had macro error messages, and when I walked him through where the macros should be, they weren’t there. I emailed him a zip file, but he didn’t get that either. I also put the zip file into the Dropbox.com folder, and he was able to get that. Once he’d extracted the files locally, he was able to open a new document based on the template successfully AND run the macros without error. He also did some quick Googling and found that Dropbox.com DOES strip macros from Word templates. But not from those files stored in zip files—yet.

What are we to do? I use Word macros to save myself and authors enormous amounts of time. This is part of my job. My clients want quick and easy ways to apply corporate formatting to imported Excel tables and the like, to add landscape pages without angst, and so on. I create or modify macro-enabled templates for my clients and need to send the revised versions to them. If email settings and now Dropbox.com are either not allowing the templates through, or are stripping the macros out of them, how are those of us who live remotely from our clients meant to do our jobs?

h1

Word: Macro to add left and right padding to all table cells in a document

January 18, 2019

Here’s an issue I found in a document I was editing this week—someone had set most of the tables to have 0 cm padding for the left and right margins of each cell (the default is 0.19 cm for metric users). This meant the text butted right up against the cell borders (it was most noticeable on the left as I was using ragged right justification). I needed to change the cell padding back to 0.19 cm.

This is easy enough to do if you’ve only got one or two tables to fix (select the table, right-click and select Table Properties; on the Table tab, click Options, then set the left and right margins to 0.19 cm; click OK to save and exit).

But this was a nearly 300-page document with hundreds of tables, many of which had their margins set to 0 cm. Off to Google to see if someone had a quicker way. They did. I tested the macro and modified it a bit for my purposes, then ran it on a copy of my big document and fixed the problem on all tables in my document in seconds.

Notes:

  • This macro will set the left and right margin padding for ALL tables in your document. In most cases that’s what you’ll want, but if you want some tables to have different padding, change those tables or cells manually after running this macro.
  • ALWAYS test on a copy of your document before running the macro on your main document!

Here’s the macro (I suggest you copy it from here so that you get all of it—on some devices, the text may go off the screen):

Sub TablePadding()
'
' TablePadding Macro
' Adapted from a macro by Greg Maxey: https://answers.microsoft.com/en-us/msoffice/forum/all/vba-code-to-set-all-word-tables-left-cell-margin/420672d4-d294-40a9-8832-7bebb3ab9bf0
' Set left and right cell padding for ALL table cells to 0.19 cm
'
Dim oTbl As Word.Table
 For Each oTbl In ActiveDocument.Tables
    oTbl.LeftPadding = CentimetersToPoints(0.19)
    oTbl.RightPadding = CentimetersToPoints(0.19)
 Next

Thanks to Greg Maxey for the original macro that I modified. If you want to change the padding to be smaller or larger, change the 0.19 value to a smaller or larger number.

h1

Word: Macro to set the language for ALL styles

September 21, 2018

One of the issues with setting the language for a Word document is that DOESN’T change the language set for the styles. If you’re lucky, your styles use the same language as your default language, but sometimes they don’t (especially if the document has come from authors in other countries). This can result in some strange behaviour under specific circumstances.

I have a macro for setting the language for all ‘ranges’ in a document, but I needed something to change the language settings for ALL styles in one command. After a bit of internet sleuthing, I came across an answer that looked promising and modified it to suit my purposes. It works! I tested it on a sample document, where I’d set the language for Normal to Alsatian, for Heading 1 to Afrikaans, and for Heading 2 to English (US). The only text I had in the document used Normal style, but that didn’t matter—the language settings for the styles still changed to the one I’d specified in the macro. In my case, that’s English (Australian) [in VBA code that’s wdEnglishAUS].

The only thing you need to change in this macro is the LanguageID. Here are some common ones for English:

  • wdEnglishAUS
  • wdEnglishCanadian
  • wdNewZealand
  • wdEnglishSouthAfrica
  • wdEnglishUK
  • wdEnglishUS.

Here’s the macro (copy it—some of it may go off the page, so if you type it you may miss some):

Sub ChangeLangStyles()

' Macro to change language in styles
' Adapted from Macropod (17 July 2012)
' http://www.vbaexpress.com/forum/showthread.php?42993-Solved-Macro-to-change-all-styles-to-a-specific-language

Dim oDoc As Document, oSty As Style
Set oDoc = ActiveDocument
    With oDoc
        For Each oSty In .Styles
            On Error Resume Next
            oSty.LanguageID = wdEnglishAUS
            On Error GoTo 0
        Next
    End With
End Sub

I adapted it from one shared by Macropod back in July 2012: http://www.vbaexpress.com/forum/showthread.php?42993-Solved-Macro-to-change-all-styles-to-a-specific-language, and full acknowledgement goes to him.

[Links last checked September 2018]

h1

Word: Macro to convert from one style to another

September 11, 2018

I should have hunted this out years ago. My main client has a habit of changing templates every couple of years, which means the old docs have to go onto the current template when they get revised. It’s a pain and involves quite a few steps (and up to several hours) per document. I still have to do the cover page and headers/footers manually, and all the landscape/portrait sections, but now I’ve got a way to convert styles in the older template to differently named styles in the newer template. For styles with the same name, no such conversion is necessary, but when the old style is called ‘Table Bullet 1’ and the new one is called ‘Table Bullet’ you have to either reapply the correct style everywhere it is used (ugh!), do a global find and replace for the style, or do a ‘select all’ for the style and then click the new style to apply it.

Some caveats:

  • Test first. Test on a COPY of the old document before doing it on the original copy, and any other documents in the set.
  • Make sure you’ve applied the new template to the old document so that the new styles are available in the document. (or, use the Organizer to copy styles across from the old template to the new [longer, slower method]).
  • Make a list of the old styles and the matching, but differently named, new styles—make sure you write down the style names EXACTLY (including any hyphens in the style name) otherwise this macro won’t work.
  • This macro runs a find and replace and replaces ALL. Test first.

Acknowledgements: I modified the macro written by Christina and available from here: https://stackoverflow.com/questions/29953322/changing-styles-in-word-using-vba to suit my client’s situation.

In my modified macro below, several styles used in tables had name changes, as did one for the appendix headings. You can add more for your circumstances—just make sure you add a new ‘Set’ line with a unique name, call that name in a separate ‘With’ statement, and make sure you add that name to the ‘Dim’ statement at the top of the macro.

If you want to use/modify this macro, copy/paste the code below–some of it runs off the page, so if you retype the code you may miss some critical command. Then modify the Set names (if required) and the style names in the double quotes.

Sub FNR_Styles()

' Converts old table and Appendix styles to new using find and replace
' Rhonda Bracey, 11 Sept 2018 (modified from https://stackoverflow.com/questions/29953322/changing-styles-in-word-using-vba [Christina])

Dim objDoc As Document
Dim table1 As Style, table2 As Style, table3 As Style, table4 As Style, app1 As Style

Set objDoc = ActiveDocument
' This code does *NOT* protect against the possibility that these styles don't
' appear in the document. That's probably not a concern with built-in styles,
' but be aware of that if you want to expand upon this for other uses.
' Set up as many of these as you need, giving each a unique name.
' The style name in the quote marks is the NEW style name.
' Make sure you add the name to the second Dim row at the top of the macro as well.

Set table1 = ActiveDocument.Styles("Table Text")
Set table2 = ActiveDocument.Styles("Table Bullet")
Set table3 = ActiveDocument.Styles("Table Heading White")
Set table4 = ActiveDocument.Styles("Table Text Number")
Set app1 = ActiveDocument.Styles("Appendix Heading")

' Searches the entire document (but not foot/endnotes, headers, or footers)
' for the old style and replaces it with the new style.

With objDoc.Content.Find
    .ClearFormatting
    .Style = "Table Text - Left"
    ' The style in the quote marks is the OLD style name
    With .Replacement
    .ClearFormatting
    .Style = table1
    End With
    ' Below is the Replace All command.
    ' Could change this to Replace:=wdReplaceOne to replace one only
   
    .Execute Wrap:=wdFindContinue, Format:=True, Replace:=wdReplaceAll
End With

With objDoc.Content.Find
    .ClearFormatting
    .Style = "Table Bullet 1"
    With .Replacement
    .ClearFormatting
    .Style = table2
    End With
    .Execute Wrap:=wdFindContinue, Format:=True, Replace:=wdReplaceAll
End With
With objDoc.Content.Find
    .ClearFormatting
    .Style = "Table Head - Center"
    With .Replacement
    .ClearFormatting
    .Style = table3
    End With
    .Execute Wrap:=wdFindContinue, Format:=True, Replace:=wdReplaceAll
End With
With objDoc.Content.Find
    .ClearFormatting
    .Style = "Table Text - Number"
    With .Replacement
    .ClearFormatting
    .Style = table4
    End With
    .Execute Wrap:=wdFindContinue, Format:=True, Replace:=wdReplaceAll
End With
With objDoc.Content.Find
    .ClearFormatting
    .Style = "App H1"
    With .Replacement
    .ClearFormatting
    .Style = app1
    End With
    .Execute Wrap:=wdFindContinue, Format:=True, Replace:=wdReplaceAll
 End With

End Sub

 

h1

Word: List keyboard shortcuts, autotext, and autocorrect entries

May 3, 2017

There are many things you can do to help automate the writing and editing process. Power users of Word use keyboard shortcuts extensively — either the in-built ones that come with Word (e.g. Ctrl+s to save, Ctrl+c to copy etc.), or ones they’ve created themselves. Power users also use AutoText and AutoCorrect to speed up inserting commonly used text, tables, etc. (See the links below for some of the blog posts I’ve written on all these.)

But how do you know what you’ve got available to use? Some of the more obscure in-built keyboard commands are difficult to find listed anywhere, and how do you remember which ones you’ve created for your own use? How can you get a list of these shortcuts, and a list of your AutoText and AutoCorrect entries? Of course, once you have a list you can then save it or print it out to refer to at any time.

I used Microsoft Word 2010 for Windows when writing this blog post — the same (or very similar) information should apply to later versions of Word for Windows. Mac users — you may be able to do this too, but as I don’t own a Mac, I can’t test on a Mac or write about Mac stuff.

List all in-built Microsoft Word keyboard commands

  1. Open a blank Word document.
  2. Go to the View tab and click the large Macros icon.
  3. Click the drop-down arrow for the Macros in list, then select Word commands.
  4. Select ListCommands in the top box.
  5. Click Run.

List all custom keyboard commands

  1. Open a Word document that is based on the template where you know the custom keyboard commands are stored (if stored in Normal, just open a blank Word document).
  2. Go to the File tab and select Print.
  3. Under Settings, change from the default Print All Pages to Key Assignments.
  4. Choose your printer (e.g. PDF if you want a document to save), then click Print.

List all AutoText entries

  1. Open a blank Word document.
  2. Go to the File tab and select Print.
  3. Under Settings, change from the default Print All Pages to AutoText Entries.
  4. Choose your printer (e.g. PDF if you want a document to save), then click Print.

List all AutoCorrect entries

Use the macro described here: http://wordribbon.tips.net/T009084_Printing_a_List_of_AutoCorrect_Entries

See also:

[Links last checked May 2017]

h1

Word: Macro to add a value at the beginning of each row of a table

May 2, 2017

I had a strange request — someone had a really long table in Microsoft Word (some 50+ pages long…) and wanted to add an asterisk (star) in front of the first character in each row, no matter how many rows and columns there were in the table or what the original first character in each row was (some first characters were numerals, but most were upper or lower case letters). Doing this manually was going to take hours, and he had several tables across various documents that he wanted to do this to.

My first thought was a find and replace with wildcards routine, but I quickly figured out that I didn’t have enough knowledge to do that, even if it was possible. My next thought was to add a new first column, populate it with asterisks, then merge the first and second columns of each row — the problem was that merging required selecting each pair of 1st and 2nd column cells for EACH row, then merging, then removing the added paragraph mark created by the merge process. That was going to take as long or even longer than adding the asterisks manually!

So I contacted my friend Dave Gash (www.davegash.com), a US-based programmer/tech writer, to see if he could help. His forte isn’t VBA, but he does know programming logic (and he knows what to search for), and by the next morning he had a solution for me. Actually he had a solution that was TOO good — his first solution (Macro 2 below) added the asterisk to the beginning of all rows in ALL tables in the document. But that’s not what my client wanted — he wanted to add asterisks to all rows in a specific table, not every table. A bit of tweaking and Dave came back a few minutes later with the solution that suited my client (Macro 1 below). When I ran it in test mode, the changes were almost instant, but they took about 5 mins on the 50+ page table! That was still many hours less than it would’ve taken manually. I owe Dave a beer or three!

Macro 1: Add an asterisk to the beginning of all rows in a specific table

Sub AddAsteriskToTableRow()
   ' From Dave Gash www.davegash.com April 2017 '
   ' Collapse the range to start so as to not have to deal with '
   ' multi-segment ranges. Then check if cursor is within a table. '

   Selection.Collapse Direction:=wdCollapseStart
   If Not Selection.Information(wdWithInTable) Then
      MsgBox "You can only run this when your cursor is within a table."
      Exit Sub
   End If

   ' Process every row in the current table. '
   Dim row As Integer
   Dim rng As Range

   For row = 1 To Selection.Tables(1).Rows.Count
      ' Get the range for the leftmost cell. '
      Set rng = Selection.Tables(1).Rows(row).Cells(1).Range

      ' For each, insert asterisk in leftmost cell.'
      ' Change value in quote marks if you want something other than an asterisk. '
      rng.InsertBefore ("*")
   Next
End Sub

Macro 2: Add an asterisk to the beginning of all rows in ALL tables in a document

Sub AddAsterisksToAllTables()
    
' From Dave Gash www.davegash.com April 2017
' Loop through all tables
For tbl = 1 To ActiveDocument.Tables.Count
 
    'Set up row and range vars
    Dim row As Integer
    Dim rng As Range
 
    'Loop through rows in current table
    For row = 1 To ActiveDocument.Tables(tbl).Rows.Count
        ' Get the range for the leftmost (column 1) cell
        Set rng = ActiveDocument.Tables(tbl).Rows(row).Cells(1).Range
        ' Insert the asterisk before the text in leftmost cell
        ' Change value in quote marks if you want something
        ' other than an asterisk.
        rng.InsertBefore ("*") 
    Next row 
Next tbl 
End Sub

Notes

  • The examples above use an asterisk as the added character, but you can make that character anything you want — one or more letters or numbers or standard punctuation symbols (or a combination), with or without a space. Just change the value in the double-quotation marks in the macro to what you want (e.g. you might have a list of product numbers that need to have ‘MQ’ added in front of them — change “*” to “MQ” [or “MQ-” or “MQ “]). You might want to change the name of the macro too, to reflect what you’re adding (e.g. AddMQToTableRow).
  • Don’t run this twice in the same table, otherwise you’ll end up with two asterisks!
  • Dave asked me to add this acknowledgement: ‘My source was from a StackOverflow (the coder’s friend, believe me!) member (apparently also from Western Australia!). … I modified the code I found there for your purposes.’ Source: http://stackoverflow.com/questions/7226721/how-can-you-get-the-current-table-in-ms-word-vba

[Links last checked May 2017]