• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2614
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jeroen RosinkCommented:
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 RosinkCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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