Put data from Excel cells into SQL Server 2005 database using VBA

Posted on 2007-10-05
Last Modified: 2013-12-25
I've got a Excel spreadsheet and want to connect it to SQL server database and put data into it using a INSERT INTO statement
There's examples on internet of theory but I'd like to see one that gives a demo, something like this ficticous example

Excel cells:  
A1 = Mr A B C
A2  = 123 Any street
A3 = Somewhere
SQL Server 2005 database:   database table name: dbo.People
Database name :        Members
Host pc   SQLServer\PC144
connection via  Windows authentication

I'm using Excel 2002

Thanks in advance for help with code.
Question by:rwallacej
    LVL 46

    Assisted Solution

    by:Wayne Taylor (webtubbs)
    Hi rwallacej,

    Take a look at this PAQ -> http:/Q_22832416.html



    Author Comment

    I put this code in

    Sub CommandButton2_Click()
    Dim sSQL As String

    ' Create a connection object.
    Dim cnPMCustomers As ADODB.Connection
    Set cnPMCustomers = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String

    'Now open the connection.
    strConn = "Data Source=(local);Initial Catalog=Score;Integrated Security=True"
    cnPMCustomers.Open strConn
    Debug.Print ("HELLO")
    ' Create a recordset object.
    Dim rsPMCustomers As ADODB.Recordset
    Set rsPMCustomers = New ADODB.Recordset


    and get the error
    Run time error '-2147217887 (80040e21)' Automation Error
    at line
    cnPMCustomers.Open strConn

    It sometimes give:
    system error -2147217887 &h80040e21

    Can you assist further?
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    LVL 33

    Accepted Solution

    You also might try this:
    You could use this scenario:

    - In the Excel sheet, select the section that represents the table.
    - From the menu select Insert - Name - Define and enter a name for the table (YourArea for example)
    - Close the Excel file

    - In SQL server run the following query:
    Insert into YourTable
    SELECT  *
    (       'Microsoft.Jet.OLEDB.4.0'
    ,       'Data Source="C:\Path\To\File.xls";Extended Properties=Excel 8.0')...[YourArea]

    Hope this helps ...

    Author Comment

    Hi roos01
    I've want to do things in Excel as VBA macro, or I could use your method (your method is better). Excel is reading data from a remote place; after the data is read in Excel I'll call the macro to put data into SQL server (I can't get the data direct to SQL server - it must go through Excle first to do calculations etc. and Excel provides connect to remote data source and the Excel spreadsheet can't be changed.

    I can connect to Access database fine. Its just SQL server thats the problem

    The connection string in VS to SQL server database is
    Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TheDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

    Maybe I'm not translating this right ?  I've tried both a direct link to file for "strConn" as well as naming the database as it is in SQL Management Studio

    Thanks for help / more ideas
    LVL 33

    Expert Comment

    The string I posted is to get data using MS SQL from Excel.
    I noticed that that is much an easier way to obtain data then programming it using VBA in excel.
    LVL 18

    Assisted Solution

    by:Sham Haque
    >>'Now open the connection.
    strConn = "Data Source=(local);Initial Catalog=Score;Integrated Security=True"
    cnPMCustomers.Open strConn

    you have not provided the provider information (which driver to use) - and the True i don't believe is a valid criterion for the last argument.

    try this instead:

    strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Score;Integrated Security=SSPI;"
    cnPMCustomers.Open strConn

    Author Comment

    I took Integrated Security=True;

    from what  Visual Studio put into the web.config application when connecting user their wizard :-)

    "Provider=sqloledb;Data Source=(local);Initial Catalog=Score;Integrated Security=SSPI;"
    doesn't give any strange errors; instead login fails (dont' know why, no user names / passwords specified in SQL Servcer management )

    Author Comment

    strConn = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Score;Integrated Security=SSPI;"

    Author Comment

    thanks guys, the above worked

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now