Excel Macro to add a MACRO in WORD?

hartleyj

New Member
Joined
Jul 28, 2011
Messages
8
Hello All,
I was wondering if there is a way in VBA to copy a macro from range of cells in Excel and insert it into the normal module in word? I have a macro in excel that copies cell's, paste them into word and then calls a macro in word which does some find and replace functions. Users have to manually add the word macro and I was hoping they could just click a user button and it would automatically open word, add the macro and close it. Thoughts?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

vaskov17

Well-known Member
Joined
Apr 27, 2011
Messages
920
you can control Word from Excel with pretty much the exact same code as if it's in Word.
 

hartleyj

New Member
Joined
Jul 28, 2011
Messages
8
thank you for the quick response. I'm not sure how I would do this. My code for excel is

'Build Script
ActiveSheet.Unprotect ("*******") 'removed
Dim WD As Word.Application
Dim ws As Worksheet
Set WD = New Word.Application
WD.Visible = False

If Range("B3").Value = "*****" Then
'Put Standard Route
Set ws1 = Worksheets("BVOIP_PUT_STDRTE")
ws1.UsedRange.AutoFilter Field:=1, Criteria1:="<>"
ws1.UsedRange.Copy
ws1.AutoFilterMode = False
WD.Documents.Add
WD.Run "macro1"


Then in word macro1 has the following

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/26/2010 by hartleyj
'
Selection.PasteSpecial DataType:=wdPasteText
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^t"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "\"
.Replacement.Text = "\^p"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.WholeStory
Selection.Copy

Shell "notepad.exe", vbNormalFocus

SendKeys "^V",true
DoEvents
SendKeys "^{HOME}",true

END SUB
 

vaskov17

Well-known Member
Joined
Apr 27, 2011
Messages
920
Code:
Sub WordTest()
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
    
    Set objWord = CreateObject("Word.Application")
    
    objWord.Visible = True
    
    Set objDoc = objWord.Documents.Add
    
    objDoc.Range.Select
    
    Set objSel = objWord.Selection
    
    objSel.PasteSpecial DataType:=wdPasteText

    With objSel.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "^t"
        .Replacement.Text = ""
        .Forward = True
        .wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
    
    With objSel.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "\"
        .Replacement.Text = "\^p"
        .Forward = True
        .wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
    
    With objSel
        .WholeStory
        .Copy
    End With
        
    Shell "notepad.exe", vbNormalFocus
    
    SendKeys "^V", True
    DoEvents
    SendKeys "^{HOME}", True
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Couldn't you put the code in the normal.dot template?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Any code, whether in Excel or Word would need distribution.

Using the normal.dot was just an idea because a lot of companies store templates in a central location.

Also, whenever a user starts up Word normal.dot starts up too.
 

vaskov17

Well-known Member
Joined
Apr 27, 2011
Messages
920
True, but for companies that don't have their templates in a central location it's a huge pain to distribute normal.dot.
 

hartleyj

New Member
Joined
Jul 28, 2011
Messages
8
Thanks VASKov17! This is working great. It's nice not having to tell every user to go and paste a macro into the word VB editor. especially when there are updates to the macro.

Thanks again!
Jon

Making people dumber 1 script at a time!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top