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.

 
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

[Bob Phillips]

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

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]

For iRow = Selection(1).Row To (Selection(1).Row + Selection.Rows.Count-1)

[craigwg]

For iRow = Selection(1).Row To (Selection(1).Row + Selection.Rows.Count-1)

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!)