Code to include a one-up suffix to a field value  
Author Message
morganherring





PostPosted: Thu Jan 04 09:59:12 CST 2007 Top

worksheet functions >> Code to include a one-up suffix to a field value

I have the following INSERT statement (which works fine) -- what I need is
to modify it so that the value of the field "Location" will be passed to SQL
server with a one-up suffix (example: on the 1st pass, append "1" to the
location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2, ect -- ) to
track versions. I'm not sure how I would write the code to automatically
affix the next higher number. How would I go about doing this?

Many thanks in advance.

Here's my code:
========================================
Sub ProductData()

Dim oConn As Object
Dim sSQL As String

Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Products")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xxx.xx.xx;" & _
"Initial Catalog=Products;" & _
"User Id=xxxxx;" & _
"Password=xxxxx"

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


oConn.Close
Set oConn = Nothing


End Sub

Excel495  
 
 
Bob





PostPosted: Thu Jan 04 09:59:12 CST 2007 Top

worksheet functions >> Code to include a one-up suffix to a field value Do a query prior to the insert to get the MAX value where the field contains
Dallas, and then just add 1 to the count.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)




>I have the following INSERT statement (which works fine) -- what I need is
>to modify it so that the value of the field "Location" will be passed to
>SQL server with a one-up suffix (example: on the 1st pass, append "1" to
>the location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2, ect -- )
>to track versions. I'm not sure how I would write the code to
>automatically affix the next higher number. How would I go about doing
>this?
>
> Many thanks in advance.
>
> Here's my code:
> ========================================
> Sub ProductData()
>
> Dim oConn As Object
> Dim sSQL As String
>
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Products")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xxx.xx.xx;" & _
> "Initial Catalog=Products;" & _
> "User Id=xxxxx;" & _
> "Password=xxxxx"
>
> 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
>
>
> oConn.Close
> Set oConn = Nothing
>
>
> End Sub
>
>


 
 
Doctorjones_md





PostPosted: Thu Jan 04 10:09:12 CST 2007 Top

worksheet functions >> Code to include a one-up suffix to a field value An "after-thought" here -- would it be possible to give the value a one-up
alpha suffix (rather than numeric)? I'm thinking that I could modify the
following code, but I'm not quite sure where to begin ...
=============
Sub SaveUniqueFilename2()
Dim Path As String
Dim FileName As String
Dim pSuffix As String
Dim inputFN As String
Dim i As Long
pSuffix = Format(Now, "MM-dd-yy")
Path = "C:\Batch Folder\"
FileName = InputBox("Enter a file name.", "File Name")
inputFN = FileName
If Dir$(Path & FileName & ".doc") = "" Then
ActiveDocument.SaveAs (Path & FileName & ".doc")
Else
FileName = FileName & " " & pSuffix
If Dir$(Path & FileName & ".doc") = "" Then
ActiveDocument.SaveAs (Path & FileName & ".doc")
MsgBox inputFN & " already exits. This file was saved as: " _
& FileName
Else
i = 2
Do While Dir$(Path & FileName & " " & i & ".doc") <> ""
i = i + 1
Loop
ActiveDocument.SaveAs (Path & FileName & " " & i & ".doc")
FileName = FileName & " " & i
MsgBox inputFN & " already exits. This file was saved as: " _
& FileName

End If
End If
End Sub

==============================================



>I have the following INSERT statement (which works fine) -- what I need is
>to modify it so that the value of the field "Location" will be passed to
>SQL server with a one-up suffix (example: on the 1st pass, append "1" to
>the location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2, ect -- )
>to track versions. I'm not sure how I would write the code to
>automatically affix the next higher number. How would I go about doing
>this?
>
> Many thanks in advance.
>
> Here's my code:
> ========================================
> Sub ProductData()
>
> Dim oConn As Object
> Dim sSQL As String
>
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Products")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xxx.xx.xx;" & _
> "Initial Catalog=Products;" & _
> "User Id=xxxxx;" & _
> "Password=xxxxx"
>
> 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
>
>
> oConn.Close
> Set oConn = Nothing
>
>
> End Sub
>
>


 
 
Doctorjones_md





PostPosted: Thu Jan 04 10:18:18 CST 2007 Top

worksheet functions >> Code to include a one-up suffix to a field value Bob,

Thank you for your quick reply -- let me see if I understand you correctly
...
When I open Excel, and run the Sub ProductData() routine, I should insert a
query just prior to the Insert Statement?

Could you give me an example of what the code might look like?

When I open Excel, I have the current value, I just need to append a suffix
value (preferable an alpha suffix if possible)

================================


> Do a query prior to the insert to get the MAX value where the field
> contains Dallas, and then just add 1 to the count.
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>


>>I have the following INSERT statement (which works fine) -- what I need is
>>to modify it so that the value of the field "Location" will be passed to
>>SQL server with a one-up suffix (example: on the 1st pass, append "1" to
>>the location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2, ect -- )
>>to track versions. I'm not sure how I would write the code to
>>automatically affix the next higher number. How would I go about doing
>>this?
>>
>> Many thanks in advance.
>>
>> Here's my code:
>> ========================================
>> Sub ProductData()
>>
>> Dim oConn As Object
>> Dim sSQL As String
>>
>> Application.ScreenUpdating = False
>> Set wsSheet = ActiveWorkbook.Sheets("Products")
>> Set oConn = CreateObject("ADODB.Connection")
>> oConn.Open = "Provider=sqloledb;" & _
>> "Data Source=xxx.xx.xx;" & _
>> "Initial Catalog=Products;" & _
>> "User Id=xxxxx;" & _
>> "Password=xxxxx"
>>
>> 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
>>
>>
>> oConn.Close
>> Set oConn = Nothing
>>
>>
>> End Sub
>>
>>
>
>


 
 
Bob





PostPosted: Thu Jan 04 12:18:45 CST 2007 Top

worksheet functions >> Code to include a one-up suffix to a field value This is the sort of query I was envisaging

sSQL = "SELECT Location FROM [Sheet1$A1:B20] WHERE Location LIKE
'Dallas%'"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = Application.Transpose(Application.Transpose(oRS.getrows))
On Error Resume Next
iMax = ActiveSheet.Evaluate("MAX(--SUBSTITUTE({""" & Join(ary,
""",""") & """},""Dallas "",""""))")
On Error GoTo 0
MsgBox iMax +1
Else
MsgBox "No records returned.", vbCritical
End If

I ran it against an Excel workbook, but the principles are the same. First I
queried against any location start with Dallas. I then extracted the MAX
value from the returned array, using array handling if none found and add 1
to it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)




> Bob,
>
> Thank you for your quick reply -- let me see if I understand you correctly
> ...
> When I open Excel, and run the Sub ProductData() routine, I should insert
> a query just prior to the Insert Statement?
>
> Could you give me an example of what the code might look like?
>
> When I open Excel, I have the current value, I just need to append a
> suffix value (preferable an alpha suffix if possible)
>
> ================================


>> Do a query prior to the insert to get the MAX value where the field
>> contains Dallas, and then just add 1 to the count.
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>> (change the xxxx to gmail if mailing direct)
>>
>>


>>>I have the following INSERT statement (which works fine) -- what I need
>>>is to modify it so that the value of the field "Location" will be passed
>>>to SQL server with a one-up suffix (example: on the 1st pass, append "1"
>>>to the location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2,
>>>ect -- ) to track versions. I'm not sure how I would write the code to
>>>automatically affix the next higher number. How would I go about doing
>>>this?
>>>
>>> Many thanks in advance.
>>>
>>> Here's my code:
>>> ========================================
>>> Sub ProductData()
>>>
>>> Dim oConn As Object
>>> Dim sSQL As String
>>>
>>> Application.ScreenUpdating = False
>>> Set wsSheet = ActiveWorkbook.Sheets("Products")
>>> Set oConn = CreateObject("ADODB.Connection")
>>> oConn.Open = "Provider=sqloledb;" & _
>>> "Data Source=xxx.xx.xx;" & _
>>> "Initial Catalog=Products;" & _
>>> "User Id=xxxxx;" & _
>>> "Password=xxxxx"
>>>
>>> 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
>>>
>>>
>>> oConn.Close
>>> Set oConn = Nothing
>>>
>>>
>>> End Sub
>>>
>>>
>>
>>
>
>