#1   Report Post  
SueBK SueBK is offline
Junior Member
 
Posts: 0
Default Merging Macros

I'm an avid Word macro user, but usually stick to things I can record, rather than write.
I have a four macros, all the same code with slight tweaks, that open an excel spreadsheet, find the words in column A, replace them with the words in column B, highlight the change, and then close the excel s/s. I found the code for the macro online and I have a reasonable handle on how it works.

I have four separate macros so that each one can use a different highlight colour to draw my attention to different issues:
1 - finds and replaces, highlights yellow
2 - finds, but has no replacement values, highlights green
3 - finds an opening bracket, followed by characters, highlights blue
4 - finds legislation in column A, highlights pink.

What I'd like to do now is merge all four macros, so I can run them off a single button. Ideally, I'd like to actually keep the four separate (so I can also run them individually) and have a 5th macro to batch run. Sounds simple, but I can't get it to work.

The code for the individual macros is:
Sub NAME()
'File name with terms to check
Const strXLFile = "C:\Users\name\Documents\Editing Information\Macro Files\Replacements.xls"
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object
Dim blnStart As Boolean
Dim r As Long
Dim m As Long
' set highligher colour to yellow
Options.DefaultHighlightColorIndex = wdYellow


On Error Resume Next
' Get or start Excel
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
blnStart = True
End If

On Error GoTo ErrHandler

Application.ScreenUpdating = False
' Open workbook
Set xlWbk = xlApp.Workbooks.Open(strXLFile)
' Reference to first worksheet
Set xlWsh = xlWbk.Worksheets(1)
' Get last used row
m = xlWsh.Cells(xlWsh.Rows.Count, 1).End(-4162).Row

With ActiveDocument.Content.Find
' Initialize find/replace settings
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Highlight = True
.MatchCase = True
.MatchWholeWord = True
.MatchWildcards = False
' Loop through rows
For r = 1 To m
' Get text to find
.Text = xlWsh.Cells(r, 1)
' And replacement
.Replacement.Text = xlWsh.Cells(r, 2)
' Replace all
.Execute Replace:=wdReplaceAll
Next r
End With

ExitHandler:
' Clean up
On Error Resume Next
Set xlWsh = Nothing
xlWbk.Close SaveChanges:=False
Set xlWbk = Nothing
If blnStart Then
xlApp.Quit
End If
Set xlApp = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
' Inform user
MsgBox Err.Description, vbExclamation
' And go to cleanup section
Resume ExitHandler

End Sub
  #2   Report Post  
Posted to microsoft.public.word.docmanagement
Stefan Blom[_3_] Stefan Blom[_3_] is offline
external usenet poster
 
Posts: 6,897
Default Merging Macros

In your "last" macro, just call the macros that you want to run (they will run
in the specified order). For example:

Sub MyFirstMacro()
'code here...
End Sub

Sub MySecondMacro()
'code here...
End Sub

Sub RunAllMyMacros()
MyFirstMacro
MySecondMacro
End Sub

--
Stefan Blom
Microsoft Word MVP




"SueBK" wrote in message ...

I'm an avid Word macro user, but usually stick to things I can record,
rather than write.
I have a four macros, all the same code with slight tweaks, that open an
excel spreadsheet, find the words in column A, replace them with the
words in column B, highlight the change, and then close the excel s/s. I
found the code for the macro online and I have a reasonable handle on
how it works.

I have four separate macros so that each one can use a different
highlight colour to draw my attention to different issues:
1 - finds and replaces, highlights yellow
2 - finds, but has no replacement values, highlights green
3 - finds an opening bracket, followed by characters, highlights blue
4 - finds legislation in column A, highlights pink.

What I'd like to do now is merge all four macros, so I can run them off
a single button. Ideally, I'd like to actually keep the four separate
(so I can also run them individually) and have a 5th macro to batch run.
Sounds simple, but I can't get it to work.

The code for the individual macros is:
Sub NAME()
'File name with terms to check
Const strXLFile = "C:\Users\name\Documents\Editing Information\Macro
Files\Replacements.xls"
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object
Dim blnStart As Boolean
Dim r As Long
Dim m As Long
' set highligher colour to yellow
Options.DefaultHighlightColorIndex = wdYellow


On Error Resume Next
' Get or start Excel
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
blnStart = True
End If

On Error GoTo ErrHandler

Application.ScreenUpdating = False
' Open workbook
Set xlWbk = xlApp.Workbooks.Open(strXLFile)
' Reference to first worksheet
Set xlWsh = xlWbk.Worksheets(1)
' Get last used row
m = xlWsh.Cells(xlWsh.Rows.Count, 1).End(-4162).Row

With ActiveDocument.Content.Find
' Initialize find/replace settings
ClearFormatting
Replacement.ClearFormatting
Replacement.Highlight = True
MatchCase = True
MatchWholeWord = True
MatchWildcards = False
' Loop through rows
For r = 1 To m
' Get text to find
Text = xlWsh.Cells(r, 1)
' And replacement
Replacement.Text = xlWsh.Cells(r, 2)
' Replace all
Execute Replace:=wdReplaceAll
Next r
End With

ExitHandler:
' Clean up
On Error Resume Next
Set xlWsh = Nothing
xlWbk.Close SaveChanges:=False
Set xlWbk = Nothing
If blnStart Then
xlApp.Quit
End If
Set xlApp = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
' Inform user
MsgBox Err.Description, vbExclamation
' And go to cleanup section
Resume ExitHandler

End Sub




--
SueBK

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I convert WP10 macros into Word 2007 macros somehow? MTO New Users 3 March 16th 10 05:46 AM
Cannot print document - Macros disabled - no macros! edspyhill01 Microsoft Word Help 3 January 11th 10 08:56 PM
Macros disabled warning in MS Word doc with no macros Earthman Microsoft Word Help 2 December 29th 09 08:17 PM
Word & Enable Macros and Disable macros Theresa Microsoft Word Help 1 September 29th 08 10:38 PM
With macros enabled I still receive "macros disabled" warning. Blakthorne Microsoft Word Help 6 November 1st 05 08:20 PM


All times are GMT +1. The time now is 08:16 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"