CODEWITHSUNDEEP
vbaexcel
Crays
Crays

Reputation: 2508

excel VBA - function to parse data

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

Upvotes: 1

Views: 298

Answers (1)

basodre
basodre

Reputation: 5780

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

  1. At the top of the Worksheet code module type Public IE as Object
  2. 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.
  3. In the standard module, also remove the IE declaration. (Same reason as step 2)
  4. 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.

Upvotes: 2

Related Questions