craigwg
06-08-2009, 09:49 AM
I'm sure this is really easy. I've been combing forums and help pages for DAYS trying to find this and I am
really close to putting my fist through my monitor! Looping is killing me! I have a groovy connection from
VBA to and SQL database, I can select, insert and delete and will soon
be able to update.
The problem is, I have to statically state in the code which rows I want to insert. My goal is to make a
selection of multiple rows (1 to infinity) in Excel, run the VBA and the code then runs against those rows.
Here is my WORKING code that works perfectly when I execute it, only does the stated row(s) though.
I have been attempting MANY things, for each loops and for loops and if statements and none of it is working.
Keep in mind the SQL connection works great, its only the looping part that I'm struggling with.
Public Sub Insert()
'define a DSN-less connections string:
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=books;" & _
"Data Source=ISTSLCD3" 'Initial Catalog is db, datasource is servername
'Define and set the worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
'Open a connection to the database. It's important that the connection CursorLocation
'property be adUseServer.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseServer
.Open stADO
.CommandTimeout = 0
End With
'Notice that strings are quoted in '""' and numbers are quoted with only ""
Dim strSQL As String
Dim row As Integer
For row = 1 To 5 'This is the static call that is KILLING ME. How do I make this loop through my selection?
strSQL = "Insert into dbo.authors (au_id, au_fname, au_lname, phone, address, city, state, zip) _
values ('" & wsSheet.Cells(row, 1).Value & "','" & wsSheet.Cells(row, 2).Value & "', '" & _
wsSheet.Cells(row, 3).Value & "', '" & wsSheet.Cells(row, 4).Value & "', '" & wsSheet.Cells(row, 5).Value _
& "', '" & wsSheet.Cells(row, 6).Value & "', '" & wsSheet.Cells(row, 7).Value & "', " _
& wsSheet.Cells(row, 8).Value & ")"
'execute the insert statement
con.Execute strSQL
Next
'Clean up the connection:
con.Close
Set con = Nothing
End Sub
really close to putting my fist through my monitor! Looping is killing me! I have a groovy connection from
VBA to and SQL database, I can select, insert and delete and will soon
be able to update.
The problem is, I have to statically state in the code which rows I want to insert. My goal is to make a
selection of multiple rows (1 to infinity) in Excel, run the VBA and the code then runs against those rows.
Here is my WORKING code that works perfectly when I execute it, only does the stated row(s) though.
I have been attempting MANY things, for each loops and for loops and if statements and none of it is working.
Keep in mind the SQL connection works great, its only the looping part that I'm struggling with.
Public Sub Insert()
'define a DSN-less connections string:
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=books;" & _
"Data Source=ISTSLCD3" 'Initial Catalog is db, datasource is servername
'Define and set the worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
'Open a connection to the database. It's important that the connection CursorLocation
'property be adUseServer.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseServer
.Open stADO
.CommandTimeout = 0
End With
'Notice that strings are quoted in '""' and numbers are quoted with only ""
Dim strSQL As String
Dim row As Integer
For row = 1 To 5 'This is the static call that is KILLING ME. How do I make this loop through my selection?
strSQL = "Insert into dbo.authors (au_id, au_fname, au_lname, phone, address, city, state, zip) _
values ('" & wsSheet.Cells(row, 1).Value & "','" & wsSheet.Cells(row, 2).Value & "', '" & _
wsSheet.Cells(row, 3).Value & "', '" & wsSheet.Cells(row, 4).Value & "', '" & wsSheet.Cells(row, 5).Value _
& "', '" & wsSheet.Cells(row, 6).Value & "', '" & wsSheet.Cells(row, 7).Value & "', " _
& wsSheet.Cells(row, 8).Value & ")"
'execute the insert statement
con.Execute strSQL
Next
'Clean up the connection:
con.Close
Set con = Nothing
End Sub