We help IT Professionals succeed at work.

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

2,666 Views
Last Modified: 2013-12-25
Hi
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.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks
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
too

Can you assist further?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Software testing consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Jeroen RosinkSoftware testing consultant
CERTIFIED EXPERT

Commented:
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.
Sham HaqueSenior SAP CRM Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I took Integrated Security=True;

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

Progress....
"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

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

Author

Commented:
thanks guys, the above worked
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.