[RESOLVED] Textbox.text & Follow Hyperlink not working

[barbashamilcar]

Hi Guys,

I've created a macro where the Textbox.text ( in a form ) would go into a variable and then the variable would become the Search query to look when running a search in Google, Bing or Yahoo, like this:

ThisWorkbook.FollowHyperlink Address:="http://www.google.com/search?q=" & TextBox3.Text, NewWindow:=True

I have this in a command button and it works perfectly. My problem is that the text is not getting pasted exactly as it is entered in the textbox and I don't know why. It should be just a copy/paste but it's not.

Here is what happens:

If my text box has the following string: "Java AND Linux AND C++ AND C#"

Or any other text that has the "+" or the "#" it just skips it and replaces it with a blank space instead of running a search in Google for that specific query : "Java AND Linux AND C++ AND C#" it shows like:
"Java AND Linux AND C AND C " or sometimes the browser just freezes and does nothing, It's like the search query is not building at all.

It just happens with terms like C# or C++


I think I need to change the way I search on Google, but don't know how.

Any suggestions?

[westconn1]

you may need to replace the # and + characters (and some others) with % and character number, as is normal in webbrowsers

something like

Java+AND+Linux+AND+C%2B%2B+AND+C%23

replace("Java AND Linux AND C++ AND C#","+","%" & hex(asc("+")))

[barbashamilcar]

you may need to replace the # and + characters (and some others) with % and character number, as is normal in webbrowsers

something like

replace("Java AND Linux AND C++ AND C#","+","%" & hex(asc("+")))

It works separately, but I have not been able to integrate the whole thing, If I assing this REPLACE to a variable, then add another variable to the next replace for the # symbol, THEN try to add the variables to the string, it just takes the first instance or takes them all and duplicates them, see below:

The below code shows how I attempt to integrate them all, but I know it would duplicate the final text, but If I just put ANDTxtBox.Text = mystr it just takes the first instance of the replace instead of them all.

StrValue = Sheets("Panel").Range("C2").Value
StrSwitch = Sheets("Panel").Range("D2").Value

[CODE]'AND
If ORTxtBox.Text = "" Then

mystr = ANDTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " AND ")

ANDTxtBox = mystr

mystr = Replace(ANDTxtBox.Text, StrValue, StrSwitch)
mystr1 = Replace(ANDTxtBox.Text, "#", "%" & Hex(Asc("#")))
mystr2 = Replace(ANDTxtBox.Text, "+", "%" & Hex(Asc("+")))

ANDTxtBox = mystr & mystr1 & mystr2


Else
If ANDTxtBox.Text <> "" And ORTxtBox.Text <> "" Then

mystr = ANDTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " AND ")

ANDTxtBox = mystr

StrReplace = Replace(ANDTxtBox.Text, StrValue, StrSwitch)
ANDTxtBox.Text = StrReplace & " AND ("

End If
End If[/CODE]

[techgnome]

what you need is a URLEncode routine...

there appears to be a solution here:

http://stackoverflow.com/questions/2...g-in-excel-vba

found via: https://www.google.com/search?q=urlencode+VBA

-tg

[westconn1]

mystr = Replace(ANDTxtBox.Text, StrValue, StrSwitch)
mystr1 = Replace(ANDTxtBox.Text, "#", "%" & Hex(Asc("#")))
mystr2 = Replace(ANDTxtBox.Text, "+", "%" & Hex(Asc("+")))

ANDTxtBox = mystr & mystr1 & mystr2

you co not need 3 variables as each one contains the complete string, you certainly do not need to concantenate them
but you do need the next replace the use the return of the previous

[barbashamilcar]

what you need is a URLEncode routine...

there appears to be a solution here:

http://stackoverflow.com/questions/2...g-in-excel-vba

found via: https://www.google.com/search?q=urlencode+VBA

-tg

Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

This code looks really close to what I attempt to do, but I do not know VB that much to fully disinguish where would I use this code to encode the final string produced by my code.

could you provide any more assistance? See code above in previous post.

[barbashamilcar]

you co not need 3 variables as each one contains the complete string, you certainly do not need to concantenate them
but you do need the next replace the use the return of the previous

Yes, I am aware of that error, but I wanted to give you the idea of what I wanted to do.

So the string in google would look like: Java AND C# AND C++ without crashing the browser, so my idea was:

ok so first I have to replace the PLUS sign, then evaluate again for the # sign, then with the completely replaced string, then just run it in google.

I don't fully understand what you said about this " but you do need the next replace the use the return of the previous "

It sounds like I can do this?: replace(ANDTxtBox.text, XX, XX) and replace (ANDTxtBox.text, XX, XX) like nesting them together??? or something like that?

[westconn1]

It sounds like I can do this?

yes you can nest the 3 replaces, so each uses the return of the other

or you can use a variable for the result of the first replace then use replace on that variable to get the next

mystr = Replace(ANDTxtBox.Text, StrValue, StrSwitch)
mystr = Replace(mystr, "#", "%" & Hex(Asc("#")))
mystr = Replace(mystr, "+", "%" & Hex(Asc("+")))
 
ANDTxtBox = mystr 
 
'OR
ANDTxtBox = replace(replace(Replace(ANDTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))

check for errors

[techgnome]

Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

This code looks really close to what I attempt to do, but I do not know VB that much to fully disinguish where would I use this code to encode the final string produced by my code.

could you provide any more assistance? See code above in previous post.

You would add that sub to where you need it and then call it like this:

strAddress = "www.tootle.com?q=" & urlencode(Textbox3.text, true)

-tg

[barbashamilcar]

So Thank you guys for your help. At the end I went with the solution suggested by westconn1. He's helped me in other ocassions and he has been fundamental to what I am doing right now.

I wanted to share how it all turned out, It's now working perfectly after several tests, now I just need to add the error handling to it and it would be finished, at least this part. So here is how it went:

If ANDTxtBox.Text = "" And ORTxtBox.Text = "" And NOTTxtBox.Text = "" Then

    MsgBox " Please enter at least 1 search term", vbCritical, "Input Needed"
Else
StrValue = Sheets("Panel").Range("C2").Value
StrSwitch = Sheets("Panel").Range("D2").Value

'AND
If ORTxtBox.Text = "" Then

mystr = ANDTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " AND ")

ANDTxtBox = mystr

'ENCODED
ANDOp = Replace(Replace(Replace(ANDTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))

'NOT ENCODED
ANDNotEncoded = Replace(ANDTxtBox.Text, StrValue, StrSwitch)


Else
If ANDTxtBox.Text <> "" And ORTxtBox.Text <> "" Then

mystr = ANDTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " AND ")

ANDTxtBox = mystr
' ENCODED
StrReplace = Replace(Replace(Replace(ANDTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))
ANDOp = StrReplace & " AND ("

' NOT ENCODED
StrReplaceNotEncoded = Replace(ANDTxtBox.Text, StrValue, StrSwitch)
ANDNotEncoded = StrReplaceNotEncoded & " AND ("


End If
End If

'OR
If ANDTxtBox.Text = "" Then
mystr = ORTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " OR ")

ORTxtBox = mystr

'ENCODED
ORop = Replace(Replace(Replace(ORTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))

'NOT ENCODED
ORopNotEncoded = Replace(ORTxtBox.Text, StrValue, StrSwitch)

Else

If ANDTxtBox.Text <> "" And ORTxtBox.Text <> "" Then
mystr = ORTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " OR ")
ORTxtBox = mystr

'ENCODED
StrReplace = Replace(Replace(Replace(ORTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))
ORop = StrReplace & ")"

'NOT ENCODED
StrReplaceNotEncoded = Replace(ORTxtBox.Text, StrValue, StrSwitch)
ORopNotEncoded = StrReplaceNotEncoded & ")"

End If
End If

'NOT
If NOTTxtBox.Text <> "" Then
mystr = NOTTxtBox.Text
myarr = Split(mystr, ",")
For i = 0 To UBound(myarr)
    If InStr(Trim(myarr(i)), " ") > 0 Then
        myarr(i) = """" & Trim(myarr(i)) & """"
        Else: myarr(i) = Trim(myarr(i))
    End If
Next
mystr = Join(myarr, " -")

NOTTxtBox = "-" & mystr

'ENCODED
NOTop = Replace(Replace(Replace(NOTTxtBox.Text, StrValue, StrSwitch), "#", "%" & Hex(Asc("#"))), "+", "%" & Hex(Asc("+")))

'NOT ENCODED
NOTopNotEncoded = Replace(NOTTxtBox.Text, StrValue, StrSwitch)

End If
End If

SearchInput = location & identifier & ANDOp & ORop & NOTop
TextBox3.Text = location & identifier & ANDNotEncoded & ORopNotEncoded & NOTopNotEncoded

I had to separate the "Encoded" string from the Non Encoded one since it would look funny as a text and the user may be like: so wht the hell are those symbols? so I separated them into an Encoded and Not Encoded one.


Thank you guys again!


Techgnome: Your information on URL Encoding was so enlighting! I have now a list of other chars that may cause trouble and I know how to call that Encoding Function if I need it later, I have pasted it into a separate module just to have it close to me. THanks for that too.