Welcome!

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

Thursday, April 27, 2006

Self Query Excel

This is just a simple but useful function to self query Excel.
This mean that once you insert a named range you can query it,
with this Array Function:

Function SQL2Range(MySQL As String)
'Example: =SQL2Range("SELECT * FROM MyRange")
'On Error Resume Next ' Enable error-handling routine.
     Dim objRsExcel As Object, sConn As String, strExcelPath As String, strExcelFileName As String, output
     Set objRsExcel = CreateObject("ADODB.Recordset")

     strExcelPath = ThisWorkbook.Path & "\"
     strExcelFileName = ThisWorkbook.Name

     objRsExcel.CursorLocation = 3

' objRsExcel.CursorType = 0 'adOpenForwardOnly
     objRsExcel.CursorType = 0 'adOpenStatic
' objRsExcel.CursorType = 0 'adOpenForwardOnly
' objRsExcel.CursorType = 0 'adOpenForwardOnly
' objRsExcel.LockType = 1

     sConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & strExcelPath & strExcelFileName
     objRsExcel.Open MySQL, sConn

     output = objRsExcel.GetRows()

     objRsExcel.Close
     Set objRsExcel = Nothing

SQL2Range = Application.WorksheetFunction.Transpose(output)
End Function

Array Function are the one inserted in a range with Shift+Control+Enter

# posted by Lorenzo @ 12:02 AM
Comments: Post a Comment



<< Home