Pls I need help in sending data from excel sheet to sql server. Thanks.

Pls, can any1 help me on dz? I rily need help in sending data from d cells in excel to sql database using VBA. Thanks.

A simple example assuming you have 10 rows and to get the value of the first column in each. Change the connection string and in Cnxn.Execute to appropriate sql query to execute and see it in action.

This is a vague example to get you started, should be "ok" for a small excel file.

Private Sub Workbook_Open()

Dim Cnxn, val
Cnxn = CreateObject("ADODB.Connection"
Cnxn.ConnectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;" 'change to valid cs
Cnxn.Open

Dim i As Integer
For i = 0 To 10
val = Worksheets(1).Cells(i, 1).Value '10 rows first column
Cnxn.Execute "INSERT INTO table VALUES("& val & "", 0, 1 'change sql query to appropiate query.
Next
Cnxn.Close
End Sub

thanks, let me work on it, I'll reach you back. thanks once again.

@Dell_net, thanks very much, pls I'm havin problem inserting a variable dt contains alphabets into the sql, , let's say a string. Thanks.

mj:

pls I'm havin problem inserting a variable dt contains alphabets into the sql

I don't really understand what you mean.

@Poster, http://support.microsoft.com/kb/321686

Google is your friend

Pls where can i get sql software?

Hi


1. Using SSIS, SSIS is service you can install along with your sql server database engine. Having done that, see link below:

http://www.codeproject.com/KB/database/Import-Excel-SISS.aspx

[size=20pt]OR[/size]

2. Using Import Wizard  in your SSMS, see link below:

http://www.rodolforodarte.com/2010/03/importing-data-into-sql-server-using-the-import-export-wizard/

[size=20pt]OR[/size]

3. Using T-SQL code, This is also interesting

http://pratchev..com/2007/10/importing-excel-data-into-sql-server.html

    SELECT * INTO BenchmarkSurvey

    FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

    'Data Source=C:\Benchmark Survey\Received\Survey1009.xls;

     Extended Properties="Excel 8.0;IMEX=1"'), [Sheet1$];


http://support.microsoft.com/kb/321686
http://www.siusic.com/wphchen/how-to-solve-problem-that-excel-data-imported-into-sql-server-via-dts-became-null-108.html

Hope those will help you out.

#rtfm