• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2623
  • Last Modified:

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

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.
0
rwallacej
Asked:
rwallacej
4 Solutions
 
Wayne Taylor (webtubbs)Commented:
Hi rwallacej,

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

Regards,

Wayne
0
 
rwallacejAuthor 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?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeroen RosinkSoftware testing consultantCommented:
You also might try this:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22862577.html
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  *
FROM    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="C:\Path\To\File.xls";Extended Properties=Excel 8.0')...[YourArea]

Hope this helps ...
0
 
rwallacejAuthor 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
0
 
Jeroen RosinkSoftware testing consultantCommented:
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.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
>>'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
0
 
rwallacejAuthor 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 )
0
 
rwallacejAuthor Commented:
strConn = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Score;Integrated Security=SSPI;"
0
 
rwallacejAuthor Commented:
thanks guys, the above worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now