Twitter Updates

    follow me on Twitter

    Tuesday, July 19, 2005


    I wrote a small VBS script which will execute any SELECT statement or stored procedure against a SQL database and returns the result in an Excel sheet.

    'Runs a query against a SQL database and returns
    'the result in an Excel sheet
    'Frederik Vandeputte -
    'Based on Scripted Server Snapshot by Roy Carlson
    'See also
    'Thanks to Robert Paquette for the copyFromRecordSet tip
    'On Error Resume Next
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Dim c
    Dim r
    Dim sql
    Dim conString

    'Change the value of the following vars
    sql = "select * from sysdatabases"
    conString = "Provider = SQLOLEDB;Data Source=(local);" & _
    "Trusted_Connection=Yes;Initial Catalog=Master;"

    ' making the connection to your sql server
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open conString

    ' creating the Excel object application
    Set objExcel = CreateObject("Excel.Application")

    objExcel.Visible = True

    Set objWorkbook = objExcel.Workbooks.Add()
    Set objWorksheet = objWorkbook.Worksheets(1)

    ' Execute the query
    objRecordSet.Open sql, objConnection, adOpenStatic, adLockOptimistic
    'Get the column headers
    c = 1
    If Not objRecordSet.EOF Then
    For Each col In objRecordSet.Fields
    objExcel.Cells(1, c).Value = col.Name
    objExcel.Cells(1, c).Font.Bold = True
    c = c + 1
    objExcel.Cells(1, c).Value = "Query returned no results"
    End If
    'Get the rows
    objWorksheet.Range("A2").CopyFromRecordset objRecordSet
    'Use the loop below if CopyFromRecordset gives problems
    'r = 2
    'Do While Not objRecordSet.EOF
    ' c = 1
    ' For Each col In objRecordSet.Fields
    ' objExcel.Cells(r, c).Value = objRecordSet.Fields.Item(c - 1).Value
    ' c = c + 1
    ' Next
    ' r = r + 1
    ' objRecordSet.MoveNext

    ' automatically fits the data to the columns
    Set objRange = objWorksheet.UsedRange

    ' cleaning up

    1 comment:

    peterw said...

    "will execute any SELECT statement or stored procedure against a SQL database"

    OK if you work with a database but what if you work on Ingres?