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