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?