Declaring a variable as a range in another worksheet

[peturg]

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.

 

[Barb Reinhardt]

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.

 

[peturg]

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.












- 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?

 

[peturg]

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.












- Show quoted text -

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

 

[Bob Phillips]

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)