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 j10
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.

" 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 j10
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
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.



" 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 j10
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
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.



" 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 j10
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)

wrote in message
ups.com...
On Apr 6, 11:28 pm, Barb Reinhardt
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.



" 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 j10
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" 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)

wrote in message

ups.com...



On Apr 6, 11:28 pm, Barb Reinhardt
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.

" 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 j10
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.