Looping through a range code

[craigwg]

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.

[vba]
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
[/vba]

[Bob Phillips]

[vba]

For iRow = 1 To Range("A1").End(xlDown).Row
strSQL = "Insert into dbo.authors (au_id, au_fname, au_lname, phone, address, city, state, zip) " & _
"values ('" & _
wsSheet.Cells(iRow, 1).Value & "','" & _
wsSheet.Cells(iRow, 2).Value & "', '" & _
wsSheet.Cells(iRow, 3).Value & "', '" & _
wsSheet.Cells(iRow, 4).Value & "', '" & _
wsSheet.Cells(iRow, 5).Value & "', '" & _
wsSheet.Cells(iRow, 6).Value & "', '" & _
wsSheet.Cells(iRow, 7).Value & "', " & _
wsSheet.Cells(iRow, 8).Value & ")"
'execute the insert statement
con.Execute strSQL
Next iRow
[/vba]

Using row for a variable name is not a good idea

[mdmackillop]

Do you need a closing ";"

[craigwg]

xld, That still grabs a static number of rows. I need it to reference the active selection. That might be row 1-6 or it might be a10-g13, or it might be a single row. The end user will make the selection and then hit a button which fires this macro, or module if we get technical.

mdmckillop, the ";" isn't really "needed". Technically its better for SQL, and if I was passing multiple statements through I might include it, but the SQL portion is very simple. I opted for simiplicity, but either works just fine.

All that stated, can anyone help me loop through a selection? See my first post above if needed.

Craig

Craig

[mdmackillop]

[VBA]For iRow = Selection(1).Row To (Selection(1).Row + Selection.Rows.Count-1)
[/VBA]

[craigwg]

[vba]For iRow = Selection(1).Row To (Selection(1).Row + Selection.Rows.Count-1)
[/vba]

Oh yeah, that's the money. Tastes better than pizza! Thanks. It's always the little stuff that just destroys me.

(Pasting one gold star on mdmackillop's forehead!)