I wrote the function below, but I feel it could be improved and so I am open to your wise suggestions.
The use case for this is to manipulate Excel data. As a freelance dev, I cannot always choose the architecture nor the source of data I have to manipulate :-/

Sub getAdoRsFromExcel_test()
'with error retrieval'
    Dim rs As ADODB.Recordset, fName As String, sSql As String
    fName = ThisWorkbook.FullName 
    sSql = "select * from [countries$a:b] where country like '%a%'"
    Set rs = getAdoRsFromExcel(fName, sSql)
    If Not rs Is Nothing Then
        Debug.Print Now, rs.EOF, rs.BOF, rs.RecordCount
        Debug.Print Now, "Shit happens"
    End If
End Sub

Function getAdoRsFromExcel(fileName As String, sSql As String) As ADODB.Recordset
'given Excel full file name and SQL query (in appropriate syntax), returns an ADODB recordset
'in case of error, returns Nothing

    Dim con As ADODB.Connection, rs As ADODB.Recordset, sConn As String, sConn2 As String

    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
    sConn2 = ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    On Error GoTo hell
    Set con = New ADODB.Connection
    con.Open sConn & sConn2
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSql, con, adOpenKeyset 'rather use this so RecordCount works
    Set getAdoRsFromExcel = rs
    'Debug.Print Format$(Now, "hh:mm:ss"), rs.RecordCount, sSql
    Set rs = Nothing
    Exit Function
    Debug.Print Format$(Now, "hh:mm:ss"), Err.Number, Err.Description
    Select Case Err.Number
        Case -2147217904: Debug.Print Format$(Now, "hh:mm:ss"), "Wrong field name ?"
        Case -2147467259: Debug.Print Format$(Now, "hh:mm:ss"), "Wrong file name ?"
        Case Else: Debug.Print Format$(Now, "hh:mm:ss"), "Shit happens - learn from mistakes"
    End Select
    'On Error Resume Next
    Set getAdoRsFromExcel = Nothing
    Resume adios
End Function
  • 4
    \$\begingroup\$ I'm missing something - if you're going to the trouble of using SQL why not have the data be in SQL Server? If you're getting data in Excel, load it into your SQL Server database? \$\endgroup\$ – this Jul 30 at 15:32
  • \$\begingroup\$ @this I cannot always choose my architecture. If my source is an Excel sheet and my output has to be an Excel sheet, I will take the straight line. Also when your client is a bank for instance, they will not always give you access to their db server. \$\endgroup\$ – Patrick Honorez Jul 31 at 8:10
  • \$\begingroup\$ Just one more if I may - I'm sure you are aware that you are using Access engine to do the SQL querying for you. In which case, why not have the data be in Access database, even if temporarily? Two advantages: 1) you can easily validate the data because Access is strict on types in comparison to Excel and 2) driving the queries from Access means you don't need to have XSLM files; the VBA code stays in ACCDB and you can ship XLSX files easily. \$\endgroup\$ – this Jul 31 at 12:43
  • \$\begingroup\$ @this I love Access, but when you are asked to make an Excel macro, you make an Excel macro, unless you can sell another option with serious argument. The function above is one among many in my 'toolbox'. I did not post it here to discuss about the use cases; I am just looking for improvement suggestions. \$\endgroup\$ – Patrick Honorez Jul 31 at 13:11
  • \$\begingroup\$ I completely understand and I apologize for derailing - I'm asking because I want to understand the scenario - it might be useful to give more details about the why as that can help a lot with the code review as we would be aware with the constraints you are dealing with. \$\endgroup\$ – this Jul 31 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.