excel - retrieve data from a table in SQL Server into your worksheet, sort it, and display an "as of" date

If you need to select data from SQL Server (or other ODBC database), you can alter the code in the following to suit your needs. It connects to the database, executes a SQL statement (an insert, just as an example of how-to ) and then selects from dbo.employee, then sorts the results.
Note: I commented out the  ActiveSheet.Range("H25").CurrentRegion.Delete part because it will delete also
the headers you added into the excel template ( CopyFromRecordset does not set a column header, you have to do that yourself) Any questions let me know.

credit to code copied from Steven's question (who copied it from MSDN)
http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba


Sub getdatabasedata()

    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        objMyConn.ConnectionString = "Driver={SQL Server}; Server=brucemdev\ssisdev; Database=DEV;"
        objMyConn.Open

    'Insert a dummy row'
        On Error Resume Next

        objMyConn.Execute "insert into employee(name,title) values('bob','fannysuruncle')"

        If objMyConn.Errors.Count > 0 Then
            MsgBox "Error inserting into employee" & ": " & Err.Description
            Exit Sub
        End If

        On Error GoTo 0

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "select * from employee"
        objMyCmd.CommandType = adCmdText

    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

    'Copy Data to Excel'
        'ActiveSheet.Range("H25").CurrentRegion.Delete'
        ActiveSheet.Range("H25", "K999").Delete


        ActiveSheet.Range("H25").CopyFromRecordset objMyRecordset

    'Sort the resultset on name'
       
       With ActiveSheet.Sort
           With .SortFields
               .Clear
               .Add Key:=ActiveSheet.Range("I24"), _
                       SortOn:=xlSortOnValues, _
                       Order:=xlAscending, _
                       DataOption:=xlSortTextAsNumbers
   
           End With
           .SetRange ActiveSheet.Range("H24").CurrentRegion
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
       End With

        ActiveSheet.Range("H23").Value = "Date refreshed: " & Now

End Sub


you will end up with something looking like: