missing code in XL range example

[Accessed]

Hi All
I need to check that a Range name exists in an XL worksheet so I can
import data into it from Access VB code.

( I have found it is easiest to do a Transferspreadsheet to a named
range called "data"
eg.
DoCmd.TransferSpreadsheet acExport, 8, "qryXLExport",
"myworksheet.xls", True, "data"
DoEvents)

On the microsoft website, and other various sites I have found a code
snippet :-
-----------------------------------------------------------
Sub Find_Names()
' Loop through all names in workbook.
For Each n In ActiveWorkbook.Names
.........

Next
MsgBox "No More Names!"
' Display message when finished.
End Sub
--------------------------------------------------------
I really need to know how to define N as an XL range in VB code
ie.
For Each N In ........

I cannot find an example of how to declare N
eg
dim n as range
dim n as xl.range

Thanks for any ideas

Ray

[Gord]

On Feb 4, 6:11 pm, "Accessed" <[e-mail]:

Hi All
I need to check that a Range name exists in an XL worksheet so I can
import data into it from Access VB code.

( I have found it is easiest to do a Transferspreadsheet to a named
range called "data"
eg.
DoCmd.TransferSpreadsheet acExport, 8, "qryXLExport",
"myworksheet.xls", True, "data"
DoEvents)

On the microsoft website, and other various sites I have found a code
snippet :-
-----------------------------------------------------------
Sub Find_Names()
' Loop through all names in workbook.
For Each n In ActiveWorkbook.Names
.........

Next
MsgBox "No More Names!"
' Display message when finished.
End Sub
--------------------------------------------------------
I really need to know how to define N as an XL range in VB code
ie.
For Each N In ........

I cannot find an example of how to declare N
eg
dim n as range
dim n as xl.range

Thanks for any ideas

Ray

Did you try...

Dim N As Excel.Name

....?

[Accessed]

Good suggestion, but it doesnt recognize that in Access 2003 at
least . Compiling produces ...
"User defined Type not defined"

( was that an access 97 version you were quoting ?)

I tried

Dim xl As Object
Dim N As Object
Set xl = CreateObject(Class:="Excel.application")
Set N = CreateObject(Class:="Excel.name")
and
Set N = CreateObject(Class:="Excel.range")

with no success too

Thanks for the suggestion though

Did you try...

Dim N As Excel.Name

[Gord]

On Feb 4, 8:31 pm, "Accessed" <[e-mail]:

Good suggestion, but it doesnt recognize that in Access 2003 at
least . Compiling produces ...
"User defined Type not defined"

( was that an access 97 version you were quoting ?)

No, Access 2000.

The declaration I suggested uses early binding which means that it
needs a reference, as in...

Tools References...

....in the VBA editor window. In my case it was

Microsoft Excel 9.0 Object Library

Your version number may be different (i.e., "11" for Office 2003).

[Accessed]

Adding the Library reference "worked" in as far as I can declare a
variable
but ...
Dim n As Excel.Name ' works ok
For Each n In .ActiveWorkbook.Names
' Check to see if the name refers to the ActiveSheet.
Debug.Print n.Name
Next
produces an error message in the
[For Each n In .ActiveWorkbook.Names] line ..
"Object doesnt support this property or method"

Obviously I am mismatching functionality here.
I will have a go using the library functions now that I have the
reference in my app.

Thanks for the followup.

The declaration I suggested uses early binding which means that it
needs a reference, as in...

Tools References...

...in the VBA editor window. In my case it was

Microsoft Excel 9.0 Object Library

Your version number may be different (i.e., "11" for Office 2003).