VBA, mid replies with Run-time error 5 (Invalid procedure call or argument)

[MoBo]

I'm strugling with VBA code. I'm quite new at VBA programming. The code below returns an error at line with mid function Scraping from web page works OK (red bold text). I would like to get only part of the text.
Sample of data from web page looks like this:
firstXXXXsecond
Desired result of this VBA should return XXXX to cell C2 on sheet "All".
If I use only variable data without mid line it works Ok, but result is not the one i want.

I would appreciate any hint.

Sub GetData()

Dim IE As Object
Dim part_1 As Integer, len As integer
Dim data As String, data_trimed As String

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://www.xy.com/a.php?ID_xy_200=1"
IE.Visible = True

While IE.busy
DoEvents
Wend

IE.document.All("input_name").Value = ThisWorkbook.Sheets("All").Range("B2")
IE.document.All("input_name").Select
IE.document.All("trigger").Select

data = IE.document.getElementById("output").innerHTML
part_1 = inStr(data, "first") + 5
len = inStr(data, "second") - inStr(data, "first") - 5
data_trimed = Mid(data, part_1, len)
ThisWorkbook.Sheets("All").Range("C2") = data_trimed

IE.Quit

End Sub

 

[Joe4]

What is the value of data when the error occurs?
You can see that easily by adding this line:

[COLOR=#333333][I]data = IE.document.getElementById("output").innerHTML
MsgBox data[/I][/COLOR]

Also, as Norie pointed out, it is helpful if you can tell us exactly what the error message is that you are getting.

 

[JoeMo]

I changed it as you advised but still got an error at mid line
Sub GetData()

What are the values of data, part_1 and mylen when you get the error?

 

[MoBo]

What are the values of data, part_1 and mylen when you get the error?

If I'm doing it like you advised without IE call with string it works OK.
If I comment mid line and put msg box for part1 and mylen, got the right numbers.
Using mid I got error "Run-time error 5" (Invalid procedure call or argument)

Have no idea what's wrong.
Text from web site contains characters <>:;"= Is that the problem mybe?

 

[Joe4]

We are trying to help you, but you really aren't really answering our questions (help us tohelp you!).
Please answer the questions JoeMo and I asked in the two previous posts.
That is, tell us exactly what those specific values are in this example that is not working for you.

 

[MoBo]

Compile error, syntax error.
And it highlight all three rows with "len" in it in red.

You can comment all the IE lines and just temporarily set:

data = "[COLOR=#333333]firstXXXXsecond"[/COLOR]

to isolate that part of the code.

Try it for yourself and see!

If I do this works OK.

Data from web is like this : "<span style="color: green;">Kode OK (desired text)<span></span></span>"
For test I used data = "<span style=""color: green;"">Kode OK (desired text)<span></span></span>"
instead of "first" I'm using ">Kode OK ("
instead of "second" I'm using ")<span>"

Result than is OK: "desired text"
Is the problem with " in string I got from web page becouse when I use
data = IE.document.getElementById("output").innerHTML
the thing does not work.

I tried to replace all ", <, >, :, / = using function replace but no success

Any idea? I know I'm doing it wrong, but I have not enough knowledge to figure it out.

 

[Norie]

Error will be caused by the value of mylen (or len in the original code), probably because it's negative.

Try stepping through the code with F8 and check the values of part_1 and mylen.

 

[MoBo]

Ignore this one half of the text was eaten..

 

[JoeMo]

Can you post the exact code you are using so we can see what you are doing with the Instr and Mid functions you are using when data = "Kode OK (desired text)"?

 

[MoBo]

Can you post the exact code you are using so we can see what you are doing with the Instr and Mid functions you are using when data = "Kode OK (desired text)"?

Here's all the code I use. If i comment mid function it works OK. Otherwise it stops there. I'm testing on sample where mylen is always > 0, otherwise it can return negative as well, and i have to fix that too.

sample of thata from web looks like:

Koda OK (string i need)

Al results but ime2 are OK. Code works only if i comment line with ime2 = mid..
For error i got Run-time error 5 (Invalid procedure call or argument) on ime2 = mid line

Sub ChkID()

Dim IE As Object
Dim ime As String, tmp As String, ime2 As String, tmp2 As String
Dim rng1 As Integer, rng2 As Integer, doCrke As Integer
Dim part1 As Integer, mylen As Integer

rng1 = ThisWorkbook.Sheets("Vsi").Range("C2") - 1
rng2 = ThisWorkbook.Sheets("Vsi").Range("C3") + 1

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "https://www.xy.com/a.php?ID_data_200=1"
IE.Visible = True

While IE.busy
DoEvents
Wend

For i = rng1 To rng2

IE.document.All("Code").Value = ThisWorkbook.Sheets("Vsi").Range("B5").Offset(i - 1)
IE.document.All("Code").Select
IE.document.All("import_a").Select

tmp = IE.document.getElementById("etc").innerHTML
tmp2 = Replace(tmp, """", "")
tmp2 = Replace(tmp2, ";", "")
tmp2 = Replace(tmp2, "/", "")
tmp2 = Replace(tmp2, ":", "")
part1 = InStr(tmp2, ">Koda OK (") + 10
mylen = InStr(tmp2, ")") - InStr(tmp2, ">Koda OK (") - 10
doCrke = InStr(1, tmp, ">")

ime = Right(tmp, Len(tmp) - doCrke)
ime2 = Mid(tmp2, part1, mylen)


ThisWorkbook.Sheets("Vsi").Range("C5").Offset(i - 1).Select
ActiveWindow.ScrollRow = Selection.Row - 20

If i > (rng1 + 1) Then
ThisWorkbook.Sheets("Vsi").Range("A5").Offset(i - 2) = Format(Date, "d. m. yyyy")
ThisWorkbook.Sheets("Vsi").Range("E5").Offset(i - 2) = ime
ThisWorkbook.Sheets("Vsi").Range("H5").Offset(i - 2) = tmp
ThisWorkbook.Sheets("Vsi").Range("I5").Offset(i - 2) = part1
ThisWorkbook.Sheets("Vsi").Range("J5").Offset(i - 2) = mylen
ThisWorkbook.Sheets("Vsi").Range("K5").Offset(i - 2) = ime2
ThisWorkbook.Sheets("Vsi").Range("L5").Offset(i - 2) = tmp2
End If
Application.Wait (Now + #12:00:01 AM#)

Next i

IE.Quit

End Sub

 

[MoBo]

I get the text below from web. I replaces < with | to avoid html formatting here
|span style="color: green;">Koda OK (string i need)|span>|/span>|/span>