CODEWITHSUNDEEP
vbaexcel
Bat_Dad
Bat_Dad

Reputation: 23

VBA Using ODBCConnection.RefreshDate Resuts in Error 1004

Due to the way workbooks I'm working with are set up, there are many different connections created within them, but only one of the connections is valid. The valid connection will be the only connection with a refresh date, so I'm trying to set up a null check using an Excel macro to find this. The code I have so far is:

For Each c2 In ActiveWorkbook.Connections
If Not IsNull(c2.ODBCConnection.RefreshDate) Then  
Blah Blah  
End If

At the null check I encounter Run-time error '1004'; Application-defined or object-defined error. I'm wondering if this has to do with a missing reference? Or possibly something else.

Any help would be greatly appreciated! Thanks!

Upvotes: 2

Views: 2775

Answers (2)

Geoff
Geoff

Reputation: 8850

I think this is caused by there being no clean null value for the Date type. Instead, MS throws an error when you try to access it.

One way to deal with this is to retrieve the value into a variable, but wrap that retrieval in an error handler block:

Dim d As Date
For Each c2 In ActiveWorkbook.Connections
    On Error Resume Next
    d = c2.OLEDBConnection.RefreshDate
    If Err = 1004 Then
        MsgBox "No date"
    ElseIf Err <> 0 Then
        MsgBox "Unknown error (" & Err.Number & "): " & Err.Description
    Else
        MsgBox CStr(d)
    End If
    On Error GoTo 0
Next

The On Error Resume Next indicates that the following code shouldn't break on an error; the On Error Goto 0 indicates that normal (system) error handling should resume.

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91336

Not all connections are ODBC, how about:

For Each c2 In ActiveWorkbook.Connections
    If c2.Type = xlConnectionTypeODBC Then
        If Not IsNull(c2.ODBCConnection.RefreshDate) Then
        ''Blah Blah
        End If
    End If
Next

However, I am not sure that it will ever be null: http://msdn.microsoft.com/en-us/library/office/ff837641(v=office.14).aspx

Upvotes: 1

Related Questions