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
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 2, 12:51 am, "NickHK" <TungChe...@Invalid.com> 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
> >
> > <pet...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > 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- 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?
>
>
|
Similar Threads
