RunTime error 91

[Vipin Aeri]

Hi Everyone,
I am new to vba coding and trying to write a vba code so that I can extract the Prev.Price from Yahoo finance into my excel sheet. I am getting an Run time error 91: Object variable or with work block variable not set.

It opens the correct webpage from tricker code and then shows the above error. Would like to write a loop that it stops automatically at the black range in the row. but doesnot how to do that too.

 

[Marc L]

Hi !

This errors means an element does not exist in the line where error occurs !
Check within your webbrowser with its inspector tool …

There are so many VBA solutions on web for Yahoo !
​

 

[Vipin Aeri]

HI Marc

Thanks for the reply. I know there are lot of solutions on Web. but would like to make a customised sheet for My funds only. Secondly these solutions doesnot fulfill my requirements. I donot understand what did you mean by inspector tool for web browser. will appreciate if you kindly explain me why I am getting this error and what is the solution.

 

[Marc L]

As many Yahoo Web solutions work on any workbook …

Your issue : you did not respect the spelling
of the desired element of the webpage …

So what is the exact ID of this element ?

Once found, compare with your code and mod both of your errors :
one in its fix part and the other in its variable part upon the code …
​

 

[Vipin Aeri]

thanks Marc. When I add this line to fetch the price form webpage. then I get this error:
Range("B" & Row.Row) = doc.getElementById("yfs_110_" & Range("A" & Row.Row).Value).innerText

Yes I know yahoo solutions but I would like to add these rows in my existing Portfolio excel file . where I have lot of others things too

 

[Marc L]

There are many VBA working codes for Yahoo all over the Web !

As I yet wrote : the element of your codeline does not exist !

So just check the ID in its source webpage …
​

 

[Vipin Aeri]

Sorry. I am writing again the ID is correct. yfs_110_113221.BO.
as 113221.BO is already in tricker for the first entry.

 

[NARAYANK991]

Hi ,

If you can wait till tomorrow , I can post the revised code.

Narayan

 

[Vipin Aeri]

Thanks Narayan . I can wait.

 

[Marc L]

the ID is correct. yfs_110_113221.BO.

Incorrect ID with 2 spelling errors ‼

As I yet noticed, one error in the fix part (character error)
and another one in the variable part according to ticker code (case sensitive) …

At very very beginner level, just check with source webpage :
it's very not difficult to copy the ID from source webpage code
and paste it to VBA code …

Once ID is correct, your code works until it meets the third error
within worksheet cells : some bad ticker codes …
​

 

[NARAYANK991]

Hi Vipin ,

See if this works.

There are 2 macros , Macro1 and Macro2 , using different techniques.

Narayan

 

[Marc L]

Can avoid loop with correct id, needs less than 20 codelines
whatever the technique used …

Vipin, your original code revisited to help you to understand :​

Sub Macro1()
         L& = Cells(Rows.Count, 1).End(xlUp).Row
         If L < 3 Then Beep: Exit Sub
         Range("B3", Cells(L, 2)).ClearContents
    With CreateObject("InternetExplorer.Application")
        .Visible = True
    For R& = 3 To L
        U$ = "https://finance.yahoo.com/q?s=" & Cells(R, 1).Value
        S$ = "yfs_110_" & Cells(R, 1).Value
        .Navigate U
            While .ReadyState < 4:  DoEvents:  Wend
        If .LocationURL <> U Then
            Cells(R, 2).Value = " bad CODE !"
        ElseIf Not IsObject(.Document.getElementById(S)) Then
            Cells(R, 2).Value = " bad ID !"
        Else
            Cells(R, 2).Value = .Document.getElementById(S).innerText
        End If
    Next
        .Quit
    End With
End Sub

This code does not need to activate any Microsoft reference …
​

 

[Vipin Aeri]

Thanks Narayan
Macro 1 gives an error after the first value:
Method 'Navigate' of obeject Iwebrowser2 failed.

But Macro 2 works and fast too.
so will be using Macro 2 and it doesnot matter about the Macro1

 

[Vipin Aeri]

Thanks Marc.
Just got your message , will try your suggestion too and Try to understand my mistakes in the codeing

 

[Vipin Aeri]

Hi Narayan,
I have a small question as per yours Macro2 suggestion :
It is working fine but code captures the pervious close from the site instead of current value. Is it possible to get the current value from site ?
thanks

 

[NARAYANK991]

Hi ,

Give me some time ; I will check the source and see what change has to be made.

Narayan

 

[Vipin Aeri]

Yes offcourse Narayan. take your time.

 

[NARAYANK991]

Hi Vipin ,

Try replacing the following line of code :

Rw.Offset(, 1).Value = htm.getElementById("table1").Rows(0).Cells(1).innerText

by this :

Rw.Offset(, 1).Value = htm.getElementById("yfs_l10_" & Rw.Value).innerText

Narayan

 

[Vipin Aeri]

Thanks once again Narayan. It worked and now I get the Actual price instead of previous price. I think you used earlier Table 1 from web page and it was previous price.

 

[NARAYANK991]

Hi Vipin ,

That is correct.

Narayan

 

[Vipin Aeri]

Hi Narayan.
Have one more question as I am new to vba coding. I want to add this file with my Mutual fund sheet and like that it gets the entry automatically in this sheet too, so that whenever I refresh this sheet gets the price of new entry.

I have wrote a vba just to copy the range from Mutual fund sheet to this sheet but I dont know how to write a code so that while copying it checks till the last entry and then copies to other sheet. I am enclosing sample file