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.
>>>>
>>>>
>>>
>>>
>>
>>
>
>