Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.sqlserver.datamining
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.sqlserver.datamining
|
|||
|
|||
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) "Doctorjones_md" wrote in message ... 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.sqlserver.datamining
|
|||
|
|||
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 ============================================== "Doctorjones_md" wrote in message ... 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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.sqlserver.datamining
|
|||
|
|||
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) ================================ "Bob Phillips" wrote in message ... 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) "Doctorjones_md" wrote in message ... 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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.sqlserver.datamining
|
|||
|
|||
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.ge trows)) 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) "Doctorjones_md" wrote in message ... 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) ================================ "Bob Phillips" wrote in message ... 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) "Doctorjones_md" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and use vba code | New Users to Excel | |||
copy and use vba code | Excel Discussion (Misc queries) | |||
Display the source for a pivot table page field | Excel Worksheet Functions | |||
Need help with Email Code (PLEASE) | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |