none
SQL Data Into Excel with Visual Studio 2015 RRS feed

  • Question

  • Hi,

    I have created an Excel 2016 VSTO Workbook and I'm trying to populate the first sheet "Sheet1" with a SQL Query. Here is the below code:

    Private Sub Sheet1_Startup() Handles Me.Startup
            Using cn As New SqlConnection With
                {
                    .ConnectionString = "..."
                }
                Using cmd As New SqlCommand With
                    {
                    .Connection = cn,
                    .CommandText = "...",
                    .CommandType = CommandType.Text
                    }
    
                    Dim dt as New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    cn.Close()
                    cn.Dispose()
                    cmd.Dispose()
                    Sheet1.Range("A1").CopyFromRecordset(dt)
                End Using
            End Using
        End Sub

    I'm having a problem getting this working, if anyone could provide some advice it would be very much appreciated.

    Thank you.


    Thursday, August 4, 2016 10:14 AM

Answers

  • I'm sorry I misread this post.  Try this...

    Sub TestMacro()
    
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    
    ' Provide the connection string.
    Dim strConn As String
    
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    
    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=NORTHWIND.MDF;"
    
    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    
    'Now open the connection.
    cnPubs.Open strConn
    
    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    
    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open "SELECT * FROM Categories"
        ' Copy the records into cell A1 on Sheet1.
        Sheet1.Range("A1").CopyFromRecordset rsPubs
        
        ' Tidy up
        .Close
    End With
    
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
    End Sub
    

    Or... this...

    Option Explicit
    
    Sub Working2()
    
    'USE [Northwind]
    'GO
    'DECLARE @return_value int
    'EXEC    @return_value = [dbo].[TestNewProc]
    '        @ShipCountry = NULL
    'SELECT  'Return Value' = @return_value
    'GO
    
    Dim con As Connection
    Dim rst As Recordset
    Dim strConn As String
    
    Set con = New Connection
    strConn = "Provider=SQLOLEDB;"
    strConn = strConn & "Data Source=LAPTOP\SQL_EXPRESS;"
    strConn = strConn & "Initial Catalog=Northwind;"
    strConn = strConn & "Integrated Security=SSPI;"
    
    con.Open strConn
    
    'Put a country name in Cell E1
    Set rst = con.Execute("Exec dbo.TestNewProc '" & ActiveSheet.Range("E1").Text & "'")
    
    'The total count of records is returned to Cell A5
    ActiveSheet.Range("A5").CopyFromRecordset rst
    
    rst.Close
    con.Close
    
    End Sub
    
    
    Sub Working()
    
    'USE [Northwind]
    'GO
    'DECLARE @return_value int
    'EXEC    @return_value = [dbo].[Ten Most Expensive Products]
    'SELECT  'Return Value' = @return_value
    'GO
    
    Dim con As Connection
    Dim rst As Recordset
    
    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;"
    
    Set rst = con.Execute("Exec dbo.[Ten Most Expensive Products]")
    'Results of SProc are returned to Cell A1
    ActiveSheet.Range("A1").CopyFromRecordset rst
    
    rst.Close
    con.Close
    End Sub
    


    MY BOOK

    • Marked as answer by Foddy Thursday, February 13, 2020 3:52 PM
    Friday, August 19, 2016 3:52 AM

All replies

  • Can you please elaborate a bit? What is not working? Any error messages?


    Best regards, George

    Thursday, August 4, 2016 12:55 PM
  • Sorry, the error which I can getting is:

    Error BC30469 Reference to a non-shared member requires an object reference.

    This is referencing this line

    Sheet1.Range("A1").CopyFromRecordset(dt)


    Thursday, August 4, 2016 1:45 PM
  • Change from

      Dim dt as New DataTable

    to:

      Dim dt as Object

    Working?


    Best regards, George

    Thursday, August 4, 2016 2:03 PM
  • Getting the below regarding the same line:

    Error BC30469 Reference to a non-shared member requires an object reference.

    Error BC32016 'Public Overloads ReadOnly Property Range As Worksheet_RangeType' has no parameters and its return type cannot be indexed.

    Thursday, August 4, 2016 2:10 PM
  • Hi Foddy,

    First of all i want to confirm with you that your connection string and your query are correct because you did not mentioned that in your post.

    other thing I want to tell you that did you try to debug the code? if not please try it first and check DT have data in it.

    if it not work then try to make a object of dataset and try to fetch data in dataset. then try to assign it in sheet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 5, 2016 6:13 AM
    Moderator
  • if you use can vb.net or c# as programming language, this link might be of help

    http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database

    Tuesday, August 16, 2016 7:59 AM
  • You can easily import from an Access database into Excel.  You can just as easily export from Excel to Access.

    Imports System.IO
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    Imports System.Net.Mime.MediaTypeNames
    
    Public Class Form1
    
    
        Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
            Dim DBImportConn As New OleDbConnection
            Dim DBImportCmd As New OleDbCommand
            Dim ImpAccessData As Application
            Dim FilePath As String
    
            ImpAccessData = Nothing
    
            'FilePath = TextBox2.Text
            FilePath = "C:\Users\Excel\Desktop\AllCustomers.xls"
    
            If DBImportConn.State = ConnectionState.Open Then DBImportConn.Close()
            DBImportConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
            DBImportConn.Open()
    
            DBImportCmd = DBImportConn.CreateCommand
            DBImportCmd.CommandText = "SELECT * INTO [MS Access;Database=" & "C:\Users\Excel\Desktop\Northwind.mdb" & "].[AllCustomers] FROM [Sheet1$]"
            DBImportCmd.ExecuteNonQuery()
            DBImportConn.Close()
        End Sub
    
    
        Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Excel\Desktop\Northwind.mdb;")
    
            AccessConn.Open()
    
            'New sheet in Workbook
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 12.0 Xml;DATABASE=C:\Users\Excel\Desktop\AllCustomers.xls;HDR=Yes;].[Sheet1] from [AllCustomers]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()
        End Sub
    
    End Class
    


    MY BOOK

    Tuesday, August 16, 2016 5:41 PM
  • I'm sorry I misread this post.  Try this...

    Sub TestMacro()
    
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    
    ' Provide the connection string.
    Dim strConn As String
    
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    
    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=NORTHWIND.MDF;"
    
    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    
    'Now open the connection.
    cnPubs.Open strConn
    
    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    
    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        ' Extract the required records.
        .Open "SELECT * FROM Categories"
        ' Copy the records into cell A1 on Sheet1.
        Sheet1.Range("A1").CopyFromRecordset rsPubs
        
        ' Tidy up
        .Close
    End With
    
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
    End Sub
    

    Or... this...

    Option Explicit
    
    Sub Working2()
    
    'USE [Northwind]
    'GO
    'DECLARE @return_value int
    'EXEC    @return_value = [dbo].[TestNewProc]
    '        @ShipCountry = NULL
    'SELECT  'Return Value' = @return_value
    'GO
    
    Dim con As Connection
    Dim rst As Recordset
    Dim strConn As String
    
    Set con = New Connection
    strConn = "Provider=SQLOLEDB;"
    strConn = strConn & "Data Source=LAPTOP\SQL_EXPRESS;"
    strConn = strConn & "Initial Catalog=Northwind;"
    strConn = strConn & "Integrated Security=SSPI;"
    
    con.Open strConn
    
    'Put a country name in Cell E1
    Set rst = con.Execute("Exec dbo.TestNewProc '" & ActiveSheet.Range("E1").Text & "'")
    
    'The total count of records is returned to Cell A5
    ActiveSheet.Range("A5").CopyFromRecordset rst
    
    rst.Close
    con.Close
    
    End Sub
    
    
    Sub Working()
    
    'USE [Northwind]
    'GO
    'DECLARE @return_value int
    'EXEC    @return_value = [dbo].[Ten Most Expensive Products]
    'SELECT  'Return Value' = @return_value
    'GO
    
    Dim con As Connection
    Dim rst As Recordset
    
    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;"
    
    Set rst = con.Execute("Exec dbo.[Ten Most Expensive Products]")
    'Results of SProc are returned to Cell A1
    ActiveSheet.Range("A1").CopyFromRecordset rst
    
    rst.Close
    con.Close
    End Sub
    


    MY BOOK

    • Marked as answer by Foddy Thursday, February 13, 2020 3:52 PM
    Friday, August 19, 2016 3:52 AM