Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling worksheet-level names using insert Autotext in Word
On Apr 2, 12:51 am, "NickHK" wrote:
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- Hide quoted text - - 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling worksheet-level names using insert Autotext in Word
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 wrote in message oups.com... On Apr 2, 12:51 am, "NickHK" wrote: 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- Hide quoted text - - 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate new Word file and insert autotext | Excel Discussion (Misc queries) | |||
How do I insert AutoText in an Excel worksheet? | Excel Discussion (Misc queries) | |||
autotext from word to excel | Excel Discussion (Misc queries) | |||
Duplicating worksheet-level names | Excel Discussion (Misc queries) | |||
AUTOTEXT Feature of Word in Excel | Excel Worksheet Functions |