Export data from Excel to SQL

[anandbohra]

Hi friends

as all of us knows that SQL is best for database storage so i want the data to be stored on SQL.

so i want to have one button in my Excel sheet so that the moment user click the button that data should go to SQL in specified table.

can anyone knows code for this.
(I know in sql through a simple 2 line code can do the same but my user dont know SQL i want they only click button in EXCEL & data should go directly to SQL)


thanks in advance

[Bob Phillips]

Sub AddData()
Dim oConn As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

[anandbohra]

Thanks XLD for this quick reply
my data is located in sheet named Backup
there are 30 column ( column header is named considering SQL requirement)
rows are nearly 5000.
so i want all of them should go to SQL at the moment

what changed have to be made in code given by u ?
should i name my range before running code ?

the above question arise coz in given code it wont specify from where to copy & what to copy & paste in SQL

[anandbohra]

i also posted this query in http://www.ozgrid.com/forum/showthread.php?p=367782#post367782

now i am getting
ISAM error (Could not find installed ISAM)

[anandbohra]

hi all pl solve this simple "for loop"

Sub testexportsql()
Dim Cn As ADODB.Connection
Dim ServerName As String
Dim DatabaseName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim rs As ADODB.Recordset
Dim RowCounter As Long

Dim NoOfFields As Integer
Dim StartRow As Long
Dim EndRow As Long

Dim ColCounter As Integer


Set rs = New ADODB.Recordset


ServerName = "ServerName " ' Enter your server name here
DatabaseName = "DatabaseName " ' Enter your database name here
TableName = "TableName " ' Enter your Table name here
UserID = "UserID" ' Enter your user ID here
' (Leave ID and Password blank if using windows Authentification")
Password = " Password" ' Enter your password here
NoOfFields = 20 ' Enter number of fields to update (eg. columns in your worksheet)
StartRow = 3 ' Enter row in sheet to start reading records
EndRow = 6 ' Enter row of last record in sheet

' CHANGES
Dim shtSheetToWork As Worksheet
Set shtSheetToWork = ActiveWorkbook.Worksheets("SQL")
'********

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";"

rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic

'EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
For RowCounter = StartRow To EndRow
rs.AddNew
For ColCounter = 1 To NoOfFields

rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
ColCounter = ColCounter + 1
Next ColCounter
Next RowCounter
rs.UpdateBatch

' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub




if u run this code u will get error on this line


rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)

pl solve this as this code transfer EXCEL data into SQL

[Bob Phillips]

Maybe ColCounter -1 evaluates to 0 which is an invalid index?

[anandbohra]

Thanks XLD for reply
but i tried that one also
when i was looking code in break mode i takes value of colcounter as 17 so i think it is not running as per loop means 1 to total column specified by user.

again when i do some R&D in codes it paste NULL values to some or all fields in SQL which even SQL wont recognize & u have to truncate the table.

pl refer the attachment & see where it turns into a bug

[ReenaBeleve]

Hi friends,

i want to have one button in my Excel sheet so that the moment when user click the button that data in excel should go to SQL in specified table.

can anyone knows code for this? Please suggest me.

Thanks in advance.

[zick80]

Beautifully Executable Code. Amazing.




Sub AddData()
Dim oConn As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub