Declaring a variable as a range in another 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?
|