excel VBA - function to parse data

Question

I am trying to parse a webpage but am having difficulty in getting the information coming through.

I have a simple button which navigate to a website in a worksheet

Private Sub Sellit_Click()
Dim IE As Object
Dim HTMLDoc As HTMLDocument
Dim oHTML_Element As IHTMLElement

Set IE = CreateObject("Internetexplorer.Application")
IE.Visible = True
apiShowWindow IE.hwnd, SW_MAXIMIZE
IE.navigate "https://www.yahoo.com/"
Do
Loop Until IE.ReadyState = READYSTATE_COMPLETE
DoEvents

Scrape

End Sub

While the function Scrape in a module

Function Scrape()

Dim IE As Object
Dim HTMLDoc As HTMLDocument
Dim oHTML_Element As IHTMLElement

MsgBox IE.document.Title

End Function

I kinda think i know the problem here is the IE doesn't go from the worksheet to the module and vise versa but am not quite sure how to fix it.

your help will be much apperciated

Where does this approach fail? Does IE.ReadyState never equal READYSTATE_COMPLETE? Is Scrape() called? Within Scrape you never bind IE, HTMLDoc or oHTML_Element to anything. — Evan
– Evan, Commented Jul 2, 2015 at 16:48
sorry i am very new to vba, i called Scrape after the IE.readyState = Readystate_complete, and i wanted the existing IE's document.title and am testing it with the msgbox to see if i get a return, apparently not. How can i have the existing IE's title ? — Crays
– Crays, Commented Jul 2, 2015 at 17:08
I see where you attempted to call Scrape but my question was is the macro executing that far or is it getting hung up in the loop. The second part of my previous comment still is true, IE is never set to anything within Scrape. IE in Scrape is not the same as IE in Sellit_Click. The Dim Keyword redimensions it in memory at a different location. You can eithier pass IE, rebind IE, or use a global varriable for IE Varriable Scope is important here. — Evan
– Evan, Commented Jul 2, 2015 at 17:32

Burak Ozdemir · Accepted Answer · 2015-07-02 17:35:21Z

2

You'd have to declare the IE object variable publicly, then refer to it using it's fully qualified name. To do so:

At the top of the Worksheet code module type Public IE as Object
Remove variable declarations to IE within the SellIt Click event and the Scrape function. Since this is declared publicly, it shouldn't be declared privately within the code.
In the standard module, also remove the IE declaration. (Same reason as step 2)
Change MsgBox IE.document.Title to include the sheet's codename. For example, if the sheet codename is Sheet1 it should read MsgBox Sheet1.IE.document.Title

Let me know if that helps.

edited Jul 2, 2015 at 17:35

Burak Ozdemir

5,3623 gold badges26 silver badges30 bronze badges

answered Jul 2, 2015 at 17:27

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel VBA - function to parse data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related