none
Insert SQL Server query results into an Excel Spreadsheet

    Question

  • I want to return two columns (start date and end date) in a SQL Server Table to  side-by-side cells in Excel 2007.  The data is in 1 row in the SQL Server table.  I'm using VBA

    I can connect to SQL and run the SELECT command.  How do I insert the results into the spreadsheet (i.e. (A1).value =)

    I've tried using ActiveSheet.ListObjects.Add but when I do that it inserts two new columns at cell A1.  Is there is a parameter to just place the data (and not insert two new columns)?  By adding the two new columns, it destroys the remaining layout of the spreadsheet.

    I just want to return the two values and place them in two cells (i.e. A2 and B2).

    I'd appreciate any assistance.

    MEG


    • Edited by AGI_MEG Wednesday, June 22, 2011 12:58 PM clarity of issue
    Wednesday, June 22, 2011 12:06 AM

Answers

  • You can create a list from the sql query and then use excel interop to enter into particular excel cells. I threw together some code from separate projects here. In this case I used a 'Dates' class to load the query results into but maybe theres another way.  Hope this helps.

     

    Const ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Dates\Dates.mdb"
     Function GetAll() As List(Of Dates)
     Dim list As New List(Of Dates)
     Dim conn As OleDbConnection = Nothing
     Dim reader As OleDbDataReader = Nothing
     Dim table As DataTable = Nothing
     Try
     conn = New OleDbConnection(ConnString)
     conn.Open()
    
     Dim cmd As New OleDbCommand("Select StartDate, EndDate FROM DateTable", conn)
     reader = cmd.ExecuteReader()
    
     table = New DataTable
     table.Load(reader)
    
     Dim count As Integer
     For count = 0 To table.Rows.Count - 1
     Dim obj As Dates 
     obj.StartDate = table.Rows(count).Item(0)
     obj.EndDate = table.Rows(count).Item(1)
    
     'add the object to a list
     list.Add(obj)
     Next
    
    
     Finally
     If reader IsNot Nothing Then
     reader.Close()
     End If
     If conn IsNot Nothing Then
     conn.Close()
     End If
     End Try
    
     Return list
     End Function 
    

    Public Sub Create(ByVal list As List(Of Dates))
     'Define
     Dim xlApp As Excel.Application
     Dim xlWorkBook As Excel.Workbook
     Dim xlWorkSheet As Excel.Worksheet
    
    
     xlApp = New Excel.ApplicationClass
     xlWorkBook = xlApp.Workbooks.Open("C:\Dates.xlsx")
     xlWorkSheet = xlWorkBook.Sheets("Dates")
    
    
     ''i' represents the rows in the list
     For i = 2 To list.Count + 1
     ''o' represents number of fields (or columns in the list or excel sheet)
     For o = 1 To 2
     Select Case o
     'copies each field (or property) of each list row to each excel colunm
     Case 1
     xlWorkSheet.Cells(i, o).value = list.Item(i - 2).StartDate
     Case 2
     xlWorkSheet.Cells(i, o).value = list.Item(i - 2).EndDate
     End Select
     Next
     Next
    
     'saves the excel file
     xlWorkSheet.SaveAs("C:\Dates.xlsx")
     NewFileName = "C:\Dates.xlsx"
    
    
     xlWorkBook = xlApp.Workbooks.Open(NewFileName)
     xlWorkSheet = xlWorkBook.Sheets("Dates")
     xlApp.Visible = True
     xlWorkBook.Activate()
    
     End Sub
    
    

    Public Class Dates
     Private m_StartDate As String
     Private m_EndDate As String
    
     Public Property StartDate() As String
     Get
     Return m_StartDate
     End Get
     Set(ByVal value As String)
     m_StartDate = value
     End Set
     End Property
     Public Property EndDate() As String
     Get
     Return m_EndDate
     End Get
     Set(ByVal value As String)
     m_EndDate = value
     End Set
     End Property
    End Class
    


     



    Friday, June 24, 2011 3:56 PM
  • I haven’t used this code in over one year, but the last time I ran it, it worked quite well:

    Sub Rectangle1_Click()
    'TRUSTED CONNECTION
        On Error GoTo errH
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim strFirstName, strLastName As String
        Dim server, username, password, table, database As String
        With Sheets("Sheet1")
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
                If con.State <> 1 Then
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
                End If
                'this is the TRUSTED connection string
                Set rs.ActiveConnection = con
                'delete all records first if checkbox checked
                If .CheckBox1 Then
                    con.Execute "delete * from tbl_demo"
                End If
                'set first row with records to import
                'you could also just loop thru a range if you want.
                intImportRow = 10
                Do Until .Cells(intImportRow, 1) = ""
                    strFirstName = .Cells(intImportRow, 1)
                    strLastName = .Cells(intImportRow, 2)
                    'insert row into database
                    con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
                    intImportRow = intImportRow + 1
                Loop
                MsgBox "Done importing", vbInformation
                con.Close
                Set con = Nothing

        End With

      

    Exit Sub

    errH:

        MsgBox Err.Description

    End Sub

    This works (I actually just tried this code; works fine):

    Sub ADOExcelSQLServer()
       
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
       
        Server_Name = "LAPTOP\SQL_EXPRESS" ' Enter your server name here
        Database_Name = "Northwind" ' Enter your  database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM Orders" ' Enter your SQL here
       
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
       
        rs.Open SQLStr, Cn, adOpenStatic
      
        With Worksheets("Sheet1").Range("A2:Z500")
            .ClearContents
            .CopyFromRecordset rs
        End With
      
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub



    Wednesday, June 29, 2011 3:38 AM

All replies

  • You can create a list from the sql query and then use excel interop to enter into particular excel cells. I threw together some code from separate projects here. In this case I used a 'Dates' class to load the query results into but maybe theres another way.  Hope this helps.

     

    Const ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Dates\Dates.mdb"
     Function GetAll() As List(Of Dates)
     Dim list As New List(Of Dates)
     Dim conn As OleDbConnection = Nothing
     Dim reader As OleDbDataReader = Nothing
     Dim table As DataTable = Nothing
     Try
     conn = New OleDbConnection(ConnString)
     conn.Open()
    
     Dim cmd As New OleDbCommand("Select StartDate, EndDate FROM DateTable", conn)
     reader = cmd.ExecuteReader()
    
     table = New DataTable
     table.Load(reader)
    
     Dim count As Integer
     For count = 0 To table.Rows.Count - 1
     Dim obj As Dates 
     obj.StartDate = table.Rows(count).Item(0)
     obj.EndDate = table.Rows(count).Item(1)
    
     'add the object to a list
     list.Add(obj)
     Next
    
    
     Finally
     If reader IsNot Nothing Then
     reader.Close()
     End If
     If conn IsNot Nothing Then
     conn.Close()
     End If
     End Try
    
     Return list
     End Function 
    

    Public Sub Create(ByVal list As List(Of Dates))
     'Define
     Dim xlApp As Excel.Application
     Dim xlWorkBook As Excel.Workbook
     Dim xlWorkSheet As Excel.Worksheet
    
    
     xlApp = New Excel.ApplicationClass
     xlWorkBook = xlApp.Workbooks.Open("C:\Dates.xlsx")
     xlWorkSheet = xlWorkBook.Sheets("Dates")
    
    
     ''i' represents the rows in the list
     For i = 2 To list.Count + 1
     ''o' represents number of fields (or columns in the list or excel sheet)
     For o = 1 To 2
     Select Case o
     'copies each field (or property) of each list row to each excel colunm
     Case 1
     xlWorkSheet.Cells(i, o).value = list.Item(i - 2).StartDate
     Case 2
     xlWorkSheet.Cells(i, o).value = list.Item(i - 2).EndDate
     End Select
     Next
     Next
    
     'saves the excel file
     xlWorkSheet.SaveAs("C:\Dates.xlsx")
     NewFileName = "C:\Dates.xlsx"
    
    
     xlWorkBook = xlApp.Workbooks.Open(NewFileName)
     xlWorkSheet = xlWorkBook.Sheets("Dates")
     xlApp.Visible = True
     xlWorkBook.Activate()
    
     End Sub
    
    

    Public Class Dates
     Private m_StartDate As String
     Private m_EndDate As String
    
     Public Property StartDate() As String
     Get
     Return m_StartDate
     End Get
     Set(ByVal value As String)
     m_StartDate = value
     End Set
     End Property
     Public Property EndDate() As String
     Get
     Return m_EndDate
     End Get
     Set(ByVal value As String)
     m_EndDate = value
     End Set
     End Property
    End Class
    


     



    Friday, June 24, 2011 3:56 PM
  • I haven’t used this code in over one year, but the last time I ran it, it worked quite well:

    Sub Rectangle1_Click()
    'TRUSTED CONNECTION
        On Error GoTo errH
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim strFirstName, strLastName As String
        Dim server, username, password, table, database As String
        With Sheets("Sheet1")
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
                If con.State <> 1 Then
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
                End If
                'this is the TRUSTED connection string
                Set rs.ActiveConnection = con
                'delete all records first if checkbox checked
                If .CheckBox1 Then
                    con.Execute "delete * from tbl_demo"
                End If
                'set first row with records to import
                'you could also just loop thru a range if you want.
                intImportRow = 10
                Do Until .Cells(intImportRow, 1) = ""
                    strFirstName = .Cells(intImportRow, 1)
                    strLastName = .Cells(intImportRow, 2)
                    'insert row into database
                    con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
                    intImportRow = intImportRow + 1
                Loop
                MsgBox "Done importing", vbInformation
                con.Close
                Set con = Nothing

        End With

      

    Exit Sub

    errH:

        MsgBox Err.Description

    End Sub

    This works (I actually just tried this code; works fine):

    Sub ADOExcelSQLServer()
       
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
       
        Server_Name = "LAPTOP\SQL_EXPRESS" ' Enter your server name here
        Database_Name = "Northwind" ' Enter your  database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM Orders" ' Enter your SQL here
       
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
       
        rs.Open SQLStr, Cn, adOpenStatic
      
        With Worksheets("Sheet1").Range("A2:Z500")
            .ClearContents
            .CopyFromRecordset rs
        End With
      
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub



    Wednesday, June 29, 2011 3:38 AM