I am attempting to assign paragraphs in a word document based on the
named ranges in an excel workbook (sheet level names, named
sequentially). I want to iterate through the names, and when one does
not exist, move to another set of names.

I have tried the following in word's VBE:
........................................................
Dim i as integer, j as Integer
Dim strCell as String
Dim rng as Range

Set myWB = GetObject({path}file.xls)

Do Until j>10
strCell = myWB.Sheets(i).Name & "!somename" & j
On Error Resume Next
Set rng = Range(strCell)
If rng Is Nothing Then
MsgBox "Range is Nothing"
Exit Do
Else
'procedure to assign & place paragraphs
End If
j = j+1
Loop
........................................................

Assuming that strCell is a valid name (which I think it is, b/c that
part of the code works when I tried it in Excel's VBE) and exists in
myWB, why won't rng set to Range(strCell)?

Any suggestions for amending the Set rng = Range(strCell) command so
this will work? Any other thoughts?

I'm lost. All help would be greatly appreciated,
Petur G

PS- The code would be simpler if I could use a For Each loop, but I
don't want to loop through every name in the worksheet.

If you're running this via WORD VBE, you'll need to define a reference to
Excel using Tools -> References -> Microsoft Excel 11 Object Library

You'll also need to declare myWS as Excel.worksheet

etc.

If you've already done that, I'm not sure of the problem.

"(E-Mail Removed)" wrote:

> I am attempting to assign paragraphs in a word document based on the
> named ranges in an excel workbook (sheet level names, named
> sequentially). I want to iterate through the names, and when one does
> not exist, move to another set of names.
>
> I have tried the following in word's VBE:
> ........................................................
> Dim i as integer, j as Integer
> Dim strCell as String
> Dim rng as Range
>
> Set myWB = GetObject({path}file.xls)
>
> Do Until j>10
> strCell = myWB.Sheets(i).Name & "!somename" & j
> On Error Resume Next
> Set rng = Range(strCell)
> If rng Is Nothing Then
> MsgBox "Range is Nothing"
> Exit Do
> Else
> 'procedure to assign & place paragraphs
> End If
> j = j+1
> Loop
> ........................................................
>
> Assuming that strCell is a valid name (which I think it is, b/c that
> part of the code works when I tried it in Excel's VBE) and exists in
> myWB, why won't rng set to Range(strCell)?
>
> Any suggestions for amending the Set rng = Range(strCell) command so
> this will work? Any other thoughts?
>
> I'm lost. All help would be greatly appreciated,
> Petur G
>
> PS- The code would be simpler if I could use a For Each loop, but I
> don't want to loop through every name in the worksheet.
>
>

On Apr 6, 11:28 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> If you're running this via WORD VBE, you'll need to define a reference to
> Excel using Tools -> References -> Microsoft Excel 11 Object Library
>
> You'll also need to declare myWS as Excel.worksheet
>
> etc.
>
> If you've already done that, I'm not sure of the problem.
>
>
>
> "pet...@gmail.com" wrote:
> > I am attempting to assign paragraphs in a word document based on the
> > named ranges in an excel workbook (sheet level names, named
> > sequentially). I want to iterate through the names, and when one does
> > not exist, move to another set of names.
>
> > I have tried the following in word's VBE:
> > ........................................................
> > Dim i as integer, j as Integer
> > Dim strCell as String
> > Dim rng as Range
>
> > Set myWB = GetObject({path}file.xls)
>
> > Do Until j>10
> > strCell = myWB.Sheets(i).Name & "!somename" & j
> > On Error Resume Next
> > Set rng = Range(strCell)
> > If rng Is Nothing Then
> > MsgBox "Range is Nothing"
> > Exit Do
> > Else
> > 'procedure to assign & place paragraphs
> > End If
> > j = j+1
> > Loop
> > ........................................................
>
> > Assuming that strCell is a valid name (which I think it is, b/c that
> > part of the code works when I tried it in Excel's VBE) and exists in
> > myWB, why won't rng set to Range(strCell)?
>
> > Any suggestions for amending the Set rng = Range(strCell) command so
> > this will work? Any other thoughts?
>
> > I'm lost. All help would be greatly appreciated,
> > Petur G
>
> > PS- The code would be simpler if I could use a For Each loop, but I
> > don't want to loop through every name in the worksheet.- Hide quoted text -
>
> - Show quoted text -

Thanks, Barb, that is good detective work. Unfortunately I had done
both of those things, so no fix there.

This is all running in a For-Next where i = 1 to n.

Any gurus out there that can give some insight?

On Apr 6, 11:28 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> If you're running this via WORD VBE, you'll need to define a reference to
> Excel using Tools -> References -> Microsoft Excel 11 Object Library
>
> You'll also need to declare myWS as Excel.worksheet
>
> etc.
>
> If you've already done that, I'm not sure of the problem.
>
>
>
> "pet...@gmail.com" wrote:
> > I am attempting to assign paragraphs in a word document based on the
> > named ranges in an excel workbook (sheet level names, named
> > sequentially). I want to iterate through the names, and when one does
> > not exist, move to another set of names.
>
> > I have tried the following in word's VBE:
> > ........................................................
> > Dim i as integer, j as Integer
> > Dim strCell as String
> > Dim rng as Range
>
> > Set myWB = GetObject({path}file.xls)
>
> > Do Until j>10
> > strCell = myWB.Sheets(i).Name & "!somename" & j
> > On Error Resume Next
> > Set rng = Range(strCell)
> > If rng Is Nothing Then
> > MsgBox "Range is Nothing"
> > Exit Do
> > Else
> > 'procedure to assign & place paragraphs
> > End If
> > j = j+1
> > Loop
> > ........................................................
>
> > Assuming that strCell is a valid name (which I think it is, b/c that
> > part of the code works when I tried it in Excel's VBE) and exists in
> > myWB, why won't rng set to Range(strCell)?
>
> > Any suggestions for amending the Set rng = Range(strCell) command so
> > this will work? Any other thoughts?
>
> > I'm lost. All help would be greatly appreciated,
> > Petur G
>
> > PS- The code would be simpler if I could use a For Each loop, but I
> > don't want to loop through every name in the worksheet.- Hide quoted text -
>
> - Show quoted text -

Did you mean anything specific by "etc."... perhaps there's something
else I'm overlooking?

You need to declare rng as Excel.Range, not just Range (It will default to
Word).

You need to qualify this line

Set rng = Range(strCell)

to

Set rng = myWB.Sheets(i).Range(strCell)

so as to get the Excel range.

Also, you don't seem to initialiuse the i variable.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 6, 11:28 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
>> If you're running this via WORD VBE, you'll need to define a reference to
>> Excel using Tools -> References -> Microsoft Excel 11 Object Library
>>
>> You'll also need to declare myWS as Excel.worksheet
>>
>> etc.
>>
>> If you've already done that, I'm not sure of the problem.
>>
>>
>>
>> "pet...@gmail.com" wrote:
>> > I am attempting to assign paragraphs in a word document based on the
>> > named ranges in an excel workbook (sheet level names, named
>> > sequentially). I want to iterate through the names, and when one does
>> > not exist, move to another set of names.
>>
>> > I have tried the following in word's VBE:
>> > ........................................................
>> > Dim i as integer, j as Integer
>> > Dim strCell as String
>> > Dim rng as Range
>>
>> > Set myWB = GetObject({path}file.xls)
>>
>> > Do Until j>10
>> > strCell = myWB.Sheets(i).Name & "!somename" & j
>> > On Error Resume Next
>> > Set rng = Range(strCell)
>> > If rng Is Nothing Then
>> > MsgBox "Range is Nothing"
>> > Exit Do
>> > Else
>> > 'procedure to assign & place paragraphs
>> > End If
>> > j = j+1
>> > Loop
>> > ........................................................
>>
>> > Assuming that strCell is a valid name (which I think it is, b/c that
>> > part of the code works when I tried it in Excel's VBE) and exists in
>> > myWB, why won't rng set to Range(strCell)?
>>
>> > Any suggestions for amending the Set rng = Range(strCell) command so
>> > this will work? Any other thoughts?
>>
>> > I'm lost. All help would be greatly appreciated,
>> > Petur G
>>
>> > PS- The code would be simpler if I could use a For Each loop, but I
>> > don't want to loop through every name in the worksheet.- Hide quoted
>> > text -
>>
>> - Show quoted text -
>
> Did you mean anything specific by "etc."... perhaps there's something
> else I'm overlooking?
>

On Apr 7, 4:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> You need to declare rng as Excel.Range, not just Range (It will default to
> Word).
>
> You need to qualify this line
>
> Set rng = Range(strCell)
>
> to
>
> Set rng = myWB.Sheets(i).Range(strCell)
>
> so as to get the Excel range.
>
> Also, you don't seem to initialiuse the i variable.
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <pet...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Apr 6, 11:28 pm, Barb Reinhardt
> > <BarbReinha...@discussions.microsoft.com> wrote:
> >> If you're running this via WORD VBE, you'll need to define a reference to
> >> Excel using Tools -> References -> Microsoft Excel 11 Object Library
>
> >> You'll also need to declare myWS as Excel.worksheet
>
> >> etc.
>
> >> If you've already done that, I'm not sure of the problem.
>
> >> "pet...@gmail.com" wrote:
> >> > I am attempting to assign paragraphs in a word document based on the
> >> > named ranges in an excel workbook (sheet level names, named
> >> > sequentially). I want to iterate through the names, and when one does
> >> > not exist, move to another set of names.
>
> >> > I have tried the following in word's VBE:
> >> > ........................................................
> >> > Dim i as integer, j as Integer
> >> > Dim strCell as String
> >> > Dim rng as Range
>
> >> > Set myWB = GetObject({path}file.xls)
>
> >> > Do Until j>10
> >> > strCell = myWB.Sheets(i).Name & "!somename" & j
> >> > On Error Resume Next
> >> > Set rng = Range(strCell)
> >> > If rng Is Nothing Then
> >> > MsgBox "Range is Nothing"
> >> > Exit Do
> >> > Else
> >> > 'procedure to assign & place paragraphs
> >> > End If
> >> > j = j+1
> >> > Loop
> >> > ........................................................
>
> >> > Assuming that strCell is a valid name (which I think it is, b/c that
> >> > part of the code works when I tried it in Excel's VBE) and exists in
> >> > myWB, why won't rng set to Range(strCell)?
>
> >> > Any suggestions for amending the Set rng = Range(strCell) command so
> >> > this will work? Any other thoughts?
>
> >> > I'm lost. All help would be greatly appreciated,
> >> > Petur G
>
> >> > PS- The code would be simpler if I could use a For Each loop, but I
> >> > don't want to loop through every name in the worksheet.- Hide quoted
> >> > text -
>
> >> - Show quoted text -
>
> > Did you mean anything specific by "etc."... perhaps there's something
> > else I'm overlooking?- Hide quoted text -
>
> - Show quoted text -

Fantastic, that worked perfectly! This usenet group is the best.
Thank you, Bob and Barb.