joseluism0
New Member
- Joined
- Feb 4, 2021
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
Hello every one,
I´m trying to use a variable from excel in a word macro but it just doesn't work, the variable I´m trying to move is nname. Hope someone can give me a hand with this.
Excel macro
Option Explicit
Public msword As Object
Public nname As String
Sub prueba()
nname = Range("e3").Value
MsgBox nname
Set msword = CreateObject("word.application")
With msword
.Visible = True
.documents.Open "c:\procedure\template 2.docm"
.Activate
.Run "replace1", nname
End With
Set msword = Nothing
End Sub
Word Macro
Sub replace1()
Dim rngStory As Word.Range
Dim lngJunk As Long
Dim oShp As Shape
TryAgain:
'Fix the skipped blank Header/Footer problem
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
'Iterate through all story types in the current document
For Each rngStory In ActiveDocument.StoryRanges
'Iterate through all linked stories
Do
SearchAndReplaceInStory rngStory, pFindTxt, pReplaceTxt
On Error Resume Next
Select Case rngStory.StoryType
Case 6, 7, 8, 9, 10, 11
If rngStory.ShapeRange.Count > 0 Then
For Each oShp In rngStory.ShapeRange
If oShp.TextFrame.HasText Then
SearchAndReplaceInStory oShp.TextFrame.TextRange, _
pFindTxt, pReplaceTxt
End If
Next
End If
Case Else
'Do Nothing
End Select
On Error GoTo 0
'Get next linked story (if any)
Set rngStory = rngStory.NextStoryRange
Loop Until rngStory Is Nothing
Next
End Sub
Sub SearchAndReplaceInStory(ByVal rngStory As Word.Range, ByVal strSearch As String, ByVal strReplace As String)
With rngStory.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "wellname"
.Replacement.Text = nname
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub
I´m trying to use a variable from excel in a word macro but it just doesn't work, the variable I´m trying to move is nname. Hope someone can give me a hand with this.
Excel macro
Option Explicit
Public msword As Object
Public nname As String
Sub prueba()
nname = Range("e3").Value
MsgBox nname
Set msword = CreateObject("word.application")
With msword
.Visible = True
.documents.Open "c:\procedure\template 2.docm"
.Activate
.Run "replace1", nname
End With
Set msword = Nothing
End Sub
Word Macro
Sub replace1()
Dim rngStory As Word.Range
Dim lngJunk As Long
Dim oShp As Shape
TryAgain:
'Fix the skipped blank Header/Footer problem
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
'Iterate through all story types in the current document
For Each rngStory In ActiveDocument.StoryRanges
'Iterate through all linked stories
Do
SearchAndReplaceInStory rngStory, pFindTxt, pReplaceTxt
On Error Resume Next
Select Case rngStory.StoryType
Case 6, 7, 8, 9, 10, 11
If rngStory.ShapeRange.Count > 0 Then
For Each oShp In rngStory.ShapeRange
If oShp.TextFrame.HasText Then
SearchAndReplaceInStory oShp.TextFrame.TextRange, _
pFindTxt, pReplaceTxt
End If
Next
End If
Case Else
'Do Nothing
End Select
On Error GoTo 0
'Get next linked story (if any)
Set rngStory = rngStory.NextStoryRange
Loop Until rngStory Is Nothing
Next
End Sub
Sub SearchAndReplaceInStory(ByVal rngStory As Word.Range, ByVal strSearch As String, ByVal strReplace As String)
With rngStory.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "wellname"
.Replacement.Text = nname
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
End Sub