Calling worksheet-level names using insert Autotext in Word
Petur,
You can loop the different names collection and/or use the Name.Parent to
tell if a name is WB or WS level.
Private Sub CommandButton1_Click()
Dim nam As Name
Dim WS As Worksheet
For Each nam In ThisWorkbook.Names
Debug.Print nam.RefersTo, nam.Parent.Name
Next
For Each WS In ThisWorkbook.Worksheets
For Each nam In WS.Names
Debug.Print nam.RefersTo, nam.Parent.Name
Next
Next
End Sub
NickHK
wrote in message
oups.com...
I am trying to using the code below to insert paragraphs into Word
documents based on the information in an Excel worksheets
....
Dim myWB As Excel.Workbook
Dim myCell as String
Set myWB = GetObject("{path}\filename.xls")
With myWB.Application
.GoTo Reference:="RangeName"
myCell = .ActiveCell.Value
If myCell = "SomeValue" Then
NormalTemplate.AutoTextEntries("CannedText").Inser t _
Whe=ActiveDocument.Paragraphs(n).Range
Else
End If
End With
...
The code works with workbook-level names. However, I am having
trouble pointing it to worksheet-level names: changing "RangeName" to
"'Sheet'!RangeName" hasn't worked.
(Ultimately, I want to iterate through RangeName 1-n. I also want to
iterate through sheets, generating and saving one word doc per sheet.)
Do I need to activate the sheet before calling the name? How is that
done? Should I be using a different kind of conditional statement?
Thanks in advance,
Petur G
|