For/Next loop to process INSERT INTO statement

I reposted this because I was unable to achieve desired results from
previous recommendations.
Previous Post:
=================== I have the following code which does the following:
1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server
What I need for the code to do is to upload all rows on the worksheet. My
thought is that I might need a For/Next Loop, but I'm not sure where in the
code to place it. Any ideas on how/where would I modify the code to enable
it to (loop through) upload all rows, or iterate on each row having
data (those not deleted by the DeleteBlankRows procedure)?
Here's my code:
======================== Private Sub DeleteBlankRows()
Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next
End Sub
Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub
Thanks in advance.Try changing
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
to
For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i
HTH,
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I reposted this because I was unable to achieve desired results from previous recommendations.
> Previous Post:
> ===================> I have the following code which does the following:
> 1. Deletes all rows having a value of "0" in column C
> 2. Uploads the data in Row 2 to my SQL Server
> What I need for the code to do is to upload all rows on the worksheet. My thought is that I might
> need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where
> would I modify the code to enable it to (loop through) upload all rows, or iterate on each row
> having
> data (those not deleted by the DeleteBlankRows procedure)?
>
> Here's my code:
> ========================> Private Sub DeleteBlankRows()
> Dim lastrow As Long
> Dim r As Long
> lastrow = Range("C" & Rows.Count).End(xlUp).Row
> For r = lastrow To 2 Step -1
> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
> ActiveSheet.Rows(r).Delete
> End If
> Next
> End Sub
> Sub InsertData()
> Dim oConn As Object
> Dim sSQL As String
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xx.x.xx.xx;" & _
> "Initial Catalog=xxx_xxx;" & _
> "User Id=xxxx;" & _
> "Password=xxxx"
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> oConn.Close
> Set oConn = Nothing
> End Sub
> Thanks in advance.
>|||Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
I was getting "Wrapped Around the Axle" trying to deal with Stored
Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
(somewhat), but just needed some tweaking -- thanks again for you help and
quick response. :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Try changing
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
> &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
> Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> to
> For i = 2 To Range("A65536").End(xlUp).Row
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
> '" & _
> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
> Range("E"&i).Value & "', '" & _
> Range("F"&i).Value & "')"
> oConn.Execute sSQL
> Next i
> HTH,
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the code
>> to enable it to (loop through) upload all rows, or iterate on each row
>> having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
>> &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>|||Doctor Jones,
I have to believe that there is a way to move an entire table into a database without looping. But
I have no experience with SQL, and have never used code like yours - still, I'm glad to hear that my
(probably sub-optimal) code worked out for you.
Bernie
MS Excel MVP
"Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
> I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts
> (and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks
> again for you help and quick response. :)
>
> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet. My thought is that I
>> might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on
>> how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on
>> each row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>|||Bernie,
There are a plethora of methods to Insert an entire Table, but your For/Next
code does exactly what I needed it to do.
Several other methods (OPENROWSET -- BULK INSERT) weren't working for me --
based on SQL Server Security Settings and/or other issues.
Thanks again for your help! :)
"Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
news:eHaaO0DJHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Doctor Jones,
> I have to believe that there is a way to move an entire table into a
> database without looping. But I have no experience with SQL, and have
> never used code like yours - still, I'm glad to hear that my (probably
> sub-optimal) code worked out for you.
> Bernie
> MS Excel MVP
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
> news:uhXwiI6IHHA.816@.TK2MSFTNGP06.phx.gbl...
>> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
>> I was getting "Wrapped Around the Axle" trying to deal with Stored
>> Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
>> (somewhat), but just needed some tweaking -- thanks again for you help
>> and quick response. :)
>>
>> "Bernie Deitrick" <deitbe @. consumer dot org> wrote in message
>> news:OJ9VN15IHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Try changing
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> to
>> For i = 2 To Range("A65536").End(xlUp).Row
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
>> '" & _
>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>> Range("E"&i).Value & "', '" & _
>> Range("F"&i).Value & "')"
>> oConn.Execute sSQL
>> Next i
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@.xxxyahoo.com> wrote in message
>> news:uQqUzX5IHHA.1816@.TK2MSFTNGP06.phx.gbl...
>>I reposted this because I was unable to achieve desired results from
>>previous recommendations.
>> Previous Post:
>> ===================>> I have the following code which does the following:
>> 1. Deletes all rows having a value of "0" in column C
>> 2. Uploads the data in Row 2 to my SQL Server
>> What I need for the code to do is to upload all rows on the worksheet.
>> My thought is that I might need a For/Next Loop, but I'm not sure where
>> in the code to place it. Any ideas on how/where would I modify the
>> code to enable it to (loop through) upload all rows, or iterate on each
>> row having
>> data (those not deleted by the DeleteBlankRows procedure)?
>>
>> Here's my code:
>> ========================>> Private Sub DeleteBlankRows()
>> Dim lastrow As Long
>> Dim r As Long
>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>> For r = lastrow To 2 Step -1
>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>> ActiveSheet.Rows(r).Delete
>> End If
>> Next
>> End Sub
>> Sub InsertData()
>> Dim oConn As Object
>> Dim sSQL As String
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xx.x.xx.xx;" & _
>> "Initial Catalog=xxx_xxx;" & _
>> "User Id=xxxx;" & _
>> "Password=xxxx"
>> sSQL = "INSERT INTO Upload_Specific " & _
>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>> [Features]) " & _
>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>> '" &
>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>> Range("E2").Value
>> & "', '" & _
>> Range("F2").Value & "')"
>> oConn.Execute sSQL
>> oConn.Close
>> Set oConn = Nothing
>> End Sub
>> Thanks in advance.
>>
>>
>>
>