bradles
01-01-2008, 04:16 AM
I have developed the following function to put the results of a SQL query into a variant array.
Public Function QryXLBookToArray(ByRef sPath As String, sSQL As String) As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
QryXLBookToArray = rst.GetRows(rst.RecordCount)
Else
QryXLBookToArray = ""
End If
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Function
However, when I try to assign this function to a variant variable and there are no records returned in the SQL query I get the following error:
Run-time error '13':
Type mismatch
It works fine when there are records being returned from the SQL query but falls over whenever there are zero records found.
As you can see, I tried setting the function to "" if the RecordCount was 0...to no avail.
Anyone have any ideas how I can handle this function when no records are returned from the SQL query?
Public Function QryXLBookToArray(ByRef sPath As String, sSQL As String) As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
QryXLBookToArray = rst.GetRows(rst.RecordCount)
Else
QryXLBookToArray = ""
End If
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Function
However, when I try to assign this function to a variant variable and there are no records returned in the SQL query I get the following error:
Run-time error '13':
Type mismatch
It works fine when there are records being returned from the SQL query but falls over whenever there are zero records found.
As you can see, I tried setting the function to "" if the RecordCount was 0...to no avail.
Anyone have any ideas how I can handle this function when no records are returned from the SQL query?