Getting hold of Excel data from Word VBA

[Sorcerer13]

I want to populate portions of a Word 2010 document by copying in data from an Excel 2010 spreadsheet.
I can happily open the spreadsheet within the VBA in Word, and I can copy in the data, but what I now want to do is to only copy in the data if it exists as a specifically named range in the Excel spreadsheet.
I'm using the following code,which runs to completion, but never identifies the presence of the named range. Can anyone help?
Function SeeIfNamedRangeExists(strname As String, _
strWBName As String) As Boolean
Dim lngPtr As Long
Dim rngTest As Range
Dim strThisSheetName As String

With xlApplication.Workbooks(strWBName)
On Error Resume Next
'*
'** Loop through all sheets in workbook.
'*
For lngPtr = 1 To .Sheets.Count Step 1
strThisSheetName = .Sheets(lngPtr).Name
'*
'** Try to access the named range.
'*
Set rngTest = .Sheets(lngPtr).Range(strname)
'*
'** If there is no error then the name exists.
'*
If Err.Number = 0 Then
'*
'** Set the function to TRUE & exit
'*
NamedRangeExists = True
Exit Function
Else
Err.Clear
End If
Next lngPtr
End With
End Function



Steve
There's nothing quite so rare as common sense!

 

[Sorcerer13]

P.S. I know the Function name is really "NamedRangeExists" - I was part way through modifying the names when I posted this query.


Steve
There's nothing quite so rare as common sense!

 

[SkipVought]

First reference application properly

    Dim rngTest As [b]Excel.[/b]Range

Return value to function...

            If Err.Number = 0 Then
                '*
                '** Set the function to TRUE & exit
                '*[b]
                SeeIfNamedRangeExists[/b] = True
                Exit Function
            Else
                Err.Clear
            End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Sorcerer13]

It seems that the problem is in the Dim statement.
On testing Err.Number, it's usually 1004, but when stepping through the code looking for a named range which I KNOW exists, I got Err.Number = 13 (type mismatch). I changed the "Dim rngTest as Range" to "Dim varTest as Variant", and everything seems to work!


Steve
There's nothing quite so rare as common sense!

 

[Sorcerer13]

Thanks SkipVought - looks like we both got there - you through skill, and me through luck!


Steve
There's nothing quite so rare as common sense!

 

[SkipVought]

The PROBLEM is that in Word,

Dim rngTest As Range

the compiler thinks that Range is a Word.Range, not an Excel.Range.

If you do not use Excel.Range, then your declaration should be

Dim rngTest As [b]Object[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

You shouldn't have to loop through all the worksheets looking for the named range - simply test the range names directly. For example:

Dim xlName As Excel.Name, xlRng As Excel.Range, StrName As String
StrName = "MyName"
With xlApplication.Workbooks(strWBName)
  For Each xlName In .Names
    If xlName.Name = StrName Then
      Set xlRng = .Names(StrName).RefersToRange
      xlRng.Copy
      'Add code here for whatever you want to do with the copied content
      Exit For
    End If
  Next
End With
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

There are Named Ranges that are at the Workbook level and Named Ranges at the Worksheet level. The latter only occurs when the name already exists.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}