Calling worksheet-level names using insert Autotext in Word

[peturg]

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").Insert _
Where:=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

 

[NickHK]

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

 

[peturg]

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














- Show quoted text -

Thanks for that suggestion, Nick.

The For Each command worked, but I only want to call a few of the
names.
Conversely, I am not having any luck with Application.GoTo. I've
tried a number of different formats for the reference, and changed the
names from local to global. I just keep getting run-time errors or
object errors.

Any thoughts? Anyone?

 

[NickHK]

Petur,
It is not normally necessary to .select objects before you use them. For a
WB level name, you can:

myCell = myWB.Range("Rangename").Value

NickHK