How to format data inserted from SQL Server into Excel table using VBA?

Question

I'm using the code below to import tables from SQL Server into Excel. So the old data be removed and new be inserted. I need to insert table to related data. I find something called ListObject but I didn't know how to apply it. Is there any way to insert table after the data are inserted?

Option Explicit

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Sub Connect_To_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)
Dim strConn As String
Dim wsReport As Worksheet
Dim col As Integer
Dim tbl As ListObject

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Server=" & Server_Name & ";"
strConn = strConn & "Database=" & Database_Name & ";"
strConn = strConn & "Trusted_Connection=yes;"

Set conn = New ADODB.Connection
With conn
        .Open ConnectionString:=strConn
        .CursorLocation = adUseClient
End With

Set rst = New ADODB.Recordset
With rst
        .ActiveConnection = conn
        .Open Source:=SQL_Statement

End With
'// here i selected the sheet where the data will be written 
Set wsReport = Sheets("RDWH")
With wsReport
'// here I clear the old data and insert new set
        wsReport.Select
        Selection.Clear
        For col = 0 To rst.Fields.Count - 1
                .Cells(1, col + 1).Value = rst.Fields(col).Name
        Next col

        .Range("A2").CopyFromRecordset rst


End With

Set wsReport = Nothing

Call Close_Connections

End Sub

Private Sub Close_Connections()

If rst.State <> 0 Then rst.Close
If conn.State <> 0 Then conn.Close

'// Release Memory
Set rst = Nothing
Set conn = Nothing

End Sub

Sub Run_Report()
Dim Server_Name As String

Server_Name = "NL-1012716\SQLEXPRESS"

Call Connect_To_SQLServer(Server_Name, "project", "SELECT * FROM [2_RDWH_CAST]")
End Sub

Do the order of columns in the pre-formatted table align with the fields from the query. Specifying the field names with SELECT f1,f2,f3 is preferable to SELECT * so the script is not affected by changes to the database table. Should the db records replace or append to existing rows in the excel table ? Is the excel table named or is there only one table on the specific sheet ? Is the specific sheet name constant or is it to be set by a parameter ? — CDP1802
– CDP1802, Commented Feb 12, 2020 at 12:14
Yes the db records should replace the existing rows, and there is only one table for each sheet. the excel table has no name and there is no parameters for sheets — RAZ
– RAZ, Commented Feb 12, 2020 at 12:56
So for the query that you show "SELECT * FROM [2_RDWH_CAST]" what is the sheet name the results should be written to ?. Would it be the same sheet if the query were "SELECT * FROM [another table] " ? Or is the excel sheet name and the db table name the same ? — CDP1802
– CDP1802, Commented Feb 12, 2020 at 13:07
Actually i have many tables in db and i was thinking to create one macro for each table i want to import. what happened now (for this table [2_RDWH_CAST]) the results be written to sheet1, and when i run it again it be written to sheet2 ... etc`. What i want to do is every time i run the code the data in specific sheet (2_RDWH_CAST in this case) be updated (so clear the old data and add new ) — RAZ
– RAZ, Commented Feb 12, 2020 at 13:20
@CDP1802, Could you please look at the code again : ) i have adjusted the code (see the comments) so now i can clear the old data and insert new set into specific sheet ( i named it RDWH). So now i just need one more thing. How i can format the table. i need to insert table to related data. i find something called ListObject but i couldn't know how to apply it. Could you please help me with that — RAZ
– RAZ, Commented Feb 12, 2020 at 14:35

CDP1802 · Accepted Answer · 2020-03-07 13:26:19Z

1

It is easier if the sheet name matches the table name and just delete any sheet with that name. Then recreate the sheet with a new table. This example shows how multiple tables can be downloaded in one operation with each query creating a new sheet.

-- Update ; Added sheet name and position parameters to sub Run_Report.


    Option Explicit

    Sub Run_Reports()

        Dim conn As ADODB.Connection
        Const SERVER_NAME As String = "NL-1012716\SQLEXPRESS"
        Const DB_NAME = "test"

        Dim sTable As String
        sTable = ThisWorkbook.Sheets("Control").Range("H6").Value

        ' connect to DB, run reports and disconnect
        Set conn = Connect_To_SQLServer(SERVER_NAME, DB_NAME)
        Call Run_Report(conn, sTable, "Sheet3", "A3")
        conn.Close
        Set conn = Nothing

    End Sub


    Sub Run_Report(ByRef conn As ADODB.Connection, ByVal TABLE_NAME As String, _
                   SHEET_NAME As String, START_CELL As String)

        If Len(TABLE_NAME) = 0 Then
            MsgBox "TABLE_NAME missing", vbCritical, "ERROR"
            Exit Sub
        End If

        Dim rst As ADODB.Recordset
        Dim wb As Workbook, ws As Worksheet, i As Integer
        Dim wsReport As Worksheet, tblResult As ListObject

        ' query
        Dim SQL As String
        SQL = "SELECT * FROM [" & TABLE_NAME & "]"

        ' execute query
        Set rst = New ADODB.Recordset
        With rst
            .ActiveConnection = conn
            .Open Source:=SQL
        End With

        ' output
        Set wb = ThisWorkbook

        ' clear sheet
        Set wsReport = wb.Sheets(SHEET_NAME)
        wsReport.Cells.Clear

        With wsReport.Range(START_CELL)
            ' write headers
            For i = 0 To rst.Fields.Count - 1
                .Offset(0, i).Value = rst.Fields.Item(i).Name
            Next
            ' write data
            .Offset(1, 0).CopyFromRecordset rst
            ' create table
            Set tblResult = wsReport.ListObjects.Add(xlSrcRange, .CurrentRegion, , xlYes)
            ' add table name
            .Offset(-1, 0) = TABLE_NAME
        End With
        MsgBox "Rows written = " & rst.RecordCount, vbInformation, TABLE_NAME

        rst.Close
        Set rst = Nothing

    End Sub

    Function Connect_To_SQLServer(SERVER_NAME As String, DB_NAME As String) As ADODB.Connection

        Dim strConn As String
        strConn = "Provider=SQLOLEDB;" & _
                  "Server=" & SERVER_NAME & ";" & _
                  "Database=" & DB_NAME & ";" & _
                  "Trusted_Connection=yes;"

        Set Connect_To_SQLServer = New ADODB.Connection
        With Connect_To_SQLServer
            .Open ConnectionString:=strConn
            .CursorLocation = adUseClient
        End With

    End Function

edited Mar 7, 2020 at 13:26

answered Feb 12, 2020 at 17:30

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RAZ Over a year ago

That was a magic . You are genius. Thanks a lot for your effort, .that exactly what i want. You really saved my day. :))))

RAZ Over a year ago

Hi, Can i make the table start from A3 not A1?

CDP1802 Over a year ago

@RAZ You can but it means a few changes, Try adding wsResults.Rows("1:2").Insert Shift:=xlDown just before the rst.close

RAZ Over a year ago

didn't work i got an error object required. The code works as following: 1- looking for the sheet name 2- delete it 3- add new sheet with new data. It's works good but i wanted to make it more dynamic. changed it like: Dim tbl As String tbl = Sheets("Control").Range("h6").Value Set conn = Connect_To_SQLServer(SERVER_NAME, DB_NAME) Call Run_Report(conn, "" & tbl & ""). so i can select the table name dynamically from drop list. Can i clear the contents of one sheet and insert new data. so only in one page (not delete and add again) is that possible ? thank you for your patience

CDP1802 Over a year ago

See update, change "Sheet3" in Run_Report(conn, sTable, "Sheet3", "A3") to whatever sheet you want the results on, "A3" is the start position for the table.

|

Collectives™ on Stack Overflow

How to format data inserted from SQL Server into Excel table using VBA?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related