Looking for a command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leo - 21 Sep 2004 22:33 GMT

Hi -

Simple question but long story below...

I am writing a code that imports specific 'Ranges' for an
excel spreadsheet into a column in Access.

I am using the following command:

!Name = xlssheet.Range("Name")

However, in some cases, the range "Name" may not always be
a range available in the excel spreadsheet.

Thus, I am trying to write an 'If' statement that will
take care of this.

I tried the following:

If xlssheet.Range("Name") = "" Then
!Name = 0
Else
!Name = xlssheet.Range("Name")
End If

I know this will not work because excel is trying to find
something that doesn't EXIST to begin with, thus there is
an error.

I am looking for a command (or something) I can use to
address this problem.

Please Advise.

Thanks!!

Reply to this Message

John Nurick - 23 Sep 2004 06:48 GMT

Hi Leo,

>!Name = xlssheet.Range("Name")

It's usually a bad idea to name things "Name", because so many objects
in both Access and Excel either have a Name property or are themselves
called Name.

That said, you can either check that the name exists before trying to
use it, as shown in the sample procedure:

Sub test()
Dim naN As Excel.Name
Dim strName As String

strNameofRange = "TestName"

For Each naN In ActiveWorkbook.Names
If naN.Name = strNameOfRange Then 'Name Exists
Debug.Print strNameOfRange, naN.RefersToRange.Cells(1).Value
Exit For
End If
Next
End Sub

Or you can use the nonexistent name and trap the resulting error with
somethig like this air code:

Dim varValue as Variant

On Error Resume Next
varValue = xlssheet.Range("TestName").Cells(1).Value
Select Case Err.Number
Case 0 'Success
'Do something with varValue
Case 1004 'expected error if name not found
'do something else
Case Else 'unexpected error
Err.Raise Err.Number, , Err.Description _
& "Range name " & strNameOfRange & " not found."
End Select
On Error Goto 0

>Hi -
>
[quoted text clipped - 31 lines]
>
>Thanks!!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message