Excel Macro to add a MACRO in WORD?

[hartleyj]

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?

 

[vaskov17]

you can control Word from Excel with pretty much the exact same code as if it's in Word.

 

[hartleyj]

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]

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]

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

 

[vaskov17]

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

Might make distribution harder

 

[Norie]

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]

True, but for companies that don't have their templates in a central location it's a huge pain to distribute normal.dot.

 

[hartleyj]

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!