Solved

add additional parameters to token up to 10 expand on optional terms

Posted on 2014-02-27
5
156 Views
Last Modified: 2014-02-27
excel 2010 vba
USERFORM
TEXTBOX

What I have:
When i type in the textbox a string
I'm setting up the tokens in preparation for an sql variable in Terdata.

Each token is placed in a '%QUICK%'
followed by a <comma> if more tokens are in the textbox

Example:
If i type in the textbox
QUICK MIXER LOAD
the code puts this output

'%QUICK%','%MIXER%', '%LOAD%'

What I need:
I need the code to ONLY ACCEPT 1 TO 10 Tokens
Example:
If i type in the textbox
QUICK MIXER LOAD
the current code puts this output
'%QUICK%','%MIXER%', '%LOAD%'

I need it to do this:
'%QUICK%','%MIXER%', '%LOAD%','%%','%%','%%','%%','%%','%%','%%'
(in this case i needed 7 seperated by a <comma>  (but NO comma ta the end.)
Add the additional '%%'   as needed up to 10

Example 2 :
If i type in the textbox
QUICK MIXER LOAD
the current code puts this output
'%SOCKET%','%WRENCH%'

I need it to do this:
'%SOCKET%','%WRENCH%','%%','%%','%%','%%','%%','%%','%%','%%'

Add the additional '%%'   as needed up to 10
IN THIS CASE I NEED TO ADD 8  '%%'

Dim Nf1 As String
Dim cArrSql As Variant
Dim cD As String
Dim x As Integer
Dim sSpecialChars As String
Dim i As Long
Dim w As String

cD = UserForm2.TextBox33

' strip special characters first NEW way
    sSpecialChars = "!@#$%^&*()_+-={}|[]\:"";'<>?,./~`"
    For i = 1 To Len(sSpecialChars)
        cD = Replace$(cD, Mid$(sSpecialChars, i, 1), " ")
    Next
    cD = cD
' THIS EXCEL TRIM FUNCTION
cD = Application.Trim(cD)
cD = cD


' EXECUTE XREF.d_search ('%%','%%', '%QUICK%','%MIXER%','%LOAD%','%%','%%','%%','%%','%%')
' how many items are in the box ?  build an array
            cArrSql = Split(cD, " ")
If UBound(cArrSql) = 0 Then
 Nf1 = Trim(Chr(39) & "%" & cArrSql(0) & "%" & Chr(39))
Else


                For x = LBound(cArrSql) To UBound(cArrSql)

                        If x = UBound(cArrSql) Then

                                Nf1 = Trim(Nf1 & " " & Chr(39) & "%" & cArrSql(x) & "%" & Chr(39))
                        Else
                                If x = 0 Then
                                  Nf1 = Trim(Nf1 & " " & Chr(39) & "%" & cArrSql(x) & "%" & Chr(39) & Chr(44))
                                Else
                                  Nf1 = Trim(Nf1 & " " & Chr(39) & "%" & cArrSql(x) & "%" & Chr(39) & Chr(44))
                                End If
                        End If
                Next
End If

Nf1 = Nf1


MsgBox Nf1, vbCritical

Open in new window



MY TERDATA execute statement
EXECUTE XREF.d_search ('%%','%%', '%QUICK%','%MIXER%','%%','%%','%%','%%','%%','%%')

Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39892411
Hi,

pls try

Sub macro()
myString = "'%QUICK%','%MIXER%', '%LOAD%'"
myStrLength = UBound(Split(myString, ",")) + 1
AddString = Application.WorksheetFunction.Rept(",'%%'", 10 - myStrLength)
myString = "%QUICK%','%MIXER%', '%LOAD%" & AddString

End Sub

Open in new window

Regards
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39892835
mystringlength ?
how is that determined ?

Thanks
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39892837
rgonzo..
oop nm.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39892864
similar approach using a function

Function stringy(strInput As String)
strtarget = UBound(Split(strInput, ",")) + 1
If strtarget > 10 Then
    stringy = "Error - exceeded 10"
Else
    padding = Application.WorksheetFunction.Rept(",'%%'", 10 - strtarget)
    
    stringy = strInput & padding
End If

End Function

Sub test()
Dim strtest As String
strtest = "%QUICK%','%MIXER%','%LOAD%'"
Debug.Print stringy(strtest)

strtest = "%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%"
Debug.Print stringy(strtest)

End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39892882
Thanks
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question