Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

how pass data form excel sheet to sql db

  Asked By: Diana    Date: Dec 16    Category: MS Office    Views: 1655
  

excel sheet contain more number of data this all the data transfer from db
table throught sql strored procedure using vba

this is my vba code
Private Sub CommandButton2_Click()
Dim CNN As New ADODB.Connection
Dim Cmd As New ADODB.command
Dim rst As New ADODB.Recordset
Dim i, dl, ri As Integer
Dim sHeader, sRecord As String

CNN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
Catalog=TEST1;Data Source=KSTC11"

r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0

'With rst
'.AddNew
Filename = Range("A" & r).Value
FileSize = Range("B" & r).Value
Date_Time = Range("C" & r).Value
Company = Range("D" & r).Value
Description = Range("E" & r).Value
FileVersion = Range("F" & r).Value
ProductName = Range("G" & r).Value
ProductVersion = Range("H" & r).Value
'.Update ' stores the new record= Range("A" & r).Value
sRecord = "xp"
With Cmd
.ActiveConnection = CNN
.CommandText = " test1 " & sRecord & " ," & Filename & ", " & FileSize
& " ," & Date_Time & " ," & Company & "," & Description & "," & FileVersion &
"," & ProductName & "," & ProductVersion & ""
End With
'End With
Set rst = Cmd.Execute
'MsgBox i
r = r + 1 ' next row
Loop

rst.Close
Set rst = Nothing
CNN.Close
Set CNN = Nothing
End Sub

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on how pass data form excel sheet to sql db Or get search suggestion and latest updates.




Tagged: