InStr() not working properly?

[jm314]

I'm using the VBA function InStr to search a LONG string for specific values. The function doesn't seem to work properly - it doesn't find the correct value when it is present and also returns an incorrect position.

The string that I'm searching is the source code of an HTML document. I put the text in a string variable and believe the starting points should work. Any ideas what I'm doing wrong? Here's the code in question:

'******************************************
Set oIE = New SHDocVw.InternetExplorer
oIE.Visible = True

i = 1
'For i = 1 To 20
sURL = Cells(i, 1).Value
oIE.Navigate (sURL)
Do Until oIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
sText = oIE.Document.body.innerhtml
lStart = InStr(1, sText, "Posted")
lEnd = InStr(lStart, sText, "<p>")
sString = Mid$(sText, lStart, lEnd - lStart)
Cells(i, 2).Value = sString
'Next

oIE.Quit
Set oIE = Nothing
'*************************************************

 

[EBGreen]

This little snippet using your code worked fine:

sText = "Foo Bar Posted something <p> Something else"
lStart = InStr(1, sText, "Posted")
lEnd = InStr(lStart, sText, "<p>")
sString = Mid(sText, lStart, lEnd - lStart)
MsgBox sString


Could you post a sample of the actual string you are dealing with and explain what results you are seeing?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[mikej28621]

Wondering if you have tried a known string.

sText = "now is the time for all Posted men to come to the aide of their country."

instead of

sText = oIE.Document.body.innerhtml

What result are you getting from that.

 

[mikej28621]

never mind... Looks like Tom and I are going the same place at the same time....

 

[Dimandja]

Also, when dealing with HTML, do not expect tokens to be in the exact positions you deem logical. For example, you could have tokens broken up, and conti
nued in the next line.

 

[jm314]

Thank you for the suggestions.

This is a personal project I'm working on and I am at work now, so I'll try out a known string (I'll copy/paste from "view source") tomorrow. Maybe that will provide a clue as to what's going on.

As far as the HTML not appearing logically, I am copy/pasting the search terms directly from the notepad document I get from "view source-ing" the page. I'm not copying any "end of line" or "return" characters - the search terms are coming from the middle of a line of text in notepad. The 2 terms do span several lines however.

I've used this successfully in another procedure, however, that didn't work at first and I had to change my search terms to get it working.

 

[jm314]

OK, the problem seems to have something to do with the string itself, or maybe the characters in the string. I'm thinking now that all the carriage returns, line feeds, and quotation marks that are included as part of the HTML document are doing something devious. I'm now wondering if this is an issue with the way VBA handles strings or with the way InStr() searches strings.

The thing I really don't understand is why I haven't run into this problem the couple times I've done this before. I guess I'll look back through some old code.

 

[JohnYingling]

I doubt seriously that carriage returns etc are the problem. Did you forget that Instr may be case sensitive according to the global Compare option?
Have you tried?

lStart = InStr(1, sText, "Posted", vbTextCompare)

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing

http://www.VBCompare.com

Compare Code

http://www.VBSortGen.com

Generate Sort Class in VB

http://www.Vbcompare.com/Check.Htm(1979

Check To MS)