[RESOLVED] Textbox.text & Follow Hyperlink not working

Printable View

Jun 28th, 2012, 10:37 AM
barbashamilcar

[RESOLVED] Textbox.text & Follow Hyperlink not working

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:

Code:

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?
Jun 28th, 2012, 04:35 PM
westconn1

Re: Textbox.text & Follow Hyperlink not working
you may need to replace the # and + characters (and some others) with % and character number, as is normal in webbrowsers

something like

Quote:

Java+AND+Linux+AND+C%2B%2B+AND+C%23
vb Code:
```
replace("Java AND Linux AND C++ AND C#","+","%" & hex(asc("+")))
```
Jul 2nd, 2012, 11:10 AM
barbashamilcar

Re: Textbox.text & Follow Hyperlink not working
Quote:
Originally Posted by westconn1

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

something like

vb Code:

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.

HTML Code:

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]
Jul 2nd, 2012, 12:21 PM
techgnome

Re: Textbox.text & Follow Hyperlink not working

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
Jul 2nd, 2012, 04:39 PM
westconn1

Re: Textbox.text & Follow Hyperlink not working

Quote:

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
Jul 2nd, 2012, 04:43 PM
barbashamilcar

Re: Textbox.text & Follow Hyperlink not working

Quote:

Originally Posted by 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

HTML Code:

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.
Jul 2nd, 2012, 04:47 PM
barbashamilcar

Re: Textbox.text & Follow Hyperlink not working

Quote:

Originally Posted by westconn1

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?
Jul 2nd, 2012, 04:58 PM
westconn1

Re: Textbox.text & Follow Hyperlink not working
Quote:

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
vb Code:
```
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
Jul 2nd, 2012, 06:10 PM
techgnome

Re: Textbox.text & Follow Hyperlink not working

Quote:

Originally Posted by barbashamilcar

HTML Code:

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:

Code:

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

-tg
Jul 5th, 2012, 04:24 PM
barbashamilcar

Re: Textbox.text & Follow Hyperlink not working

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:

Code:

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.