Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Extract Data from a website

Excel vba 2010.

Thanks to byundt,

I have code directing me to a website.
Function GetIE() As Object
Dim IE As Object, oShApp As Object, oWin As Object
Set oShApp = CreateObject("Shell.Application")
For Each oWin In oShApp.Windows
    If TypeName(oWin.Document) = "HTMLDocument" Then
        Set IE = oWin
        Set oShApp = Nothing
        Set oWin = Nothing
        Exit For
    End If
If IE Is Nothing Then
    Set IE = CreateObject("InternetExplorer.Application")
End If
Set GetIE = IE
End Function

Sub test()
Dim IE As Object
Dim t As String
Set IE = GetIE()
t = "11509957"
IE.Navigate ("" & t & " ")
End Sub 

Open in new window

Once i'am at a website:
I need to know if there is way to extract data from a webpage...if i know what html code to look for..

in this case: but not the website above..
mboxCreate("hybris_idp", "communityName=Anonymous", "SKU=4YP37", "db_audience="

The vaue i'm wanting is the value after the

"SKU=<alphanumeric number>",

say to a textbox 2 on my userform...

Avatar of byundt
Flag of United States of America image

Could you please post an URL for a web page that has the desired information?
Avatar of Fordraiders


When I used the View...Source menu item on that web  page in Internet Explorer, I found the following text on statement 506:
mboxCreate("hybris_idp", "communityName=Anonymous", "SKU=4YP37", "db_audience=" + data.audience, "db_industry=" + data.industry);

Would you be wanting the code to return the following text?
"db_audience=" + data.audience, "db_industry=" + data.industry);
bynudt, No just the "4YP37"
byundt, when u get a chance...any more suggestions
I'm not understanding your overall problem.

I asked you for a link that contained the desired information. You gave me: 

I then asked what information you wanted to get from that web page. You responded "just the 4YP37"

Since the desired information is part of the link, I am definitely not understanding what you want.
i'll rephrase my repsonse.
byundt, If a person goes to this website
and then say finds a product  and click on it to view details...such as "4YP37"

Then i need to pull that sku # from the webpage.

does this help..
Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect, Will take it from here...
byundt, I guess i expected  the opposite to happen.

No information on the Worksheet will predetermine where to go on this website.

A person goes to this site looking for product. Once they find a product.

They press the button on the sheet... the code will go to the current page a person has open on this website while in product view.

say at this particular moment they are looking at a product number "3UU59"

the code will look for this part of the html code:
mboxCreate("hybris_idp", "communityName=GIS", "SKU=3UU59");

and return me the  
3UU59 to the cell in A1
I am stuck trying to figure out which tab is active if you have more than one in your Internet Explorer session. If there is only one, then you can capture the SKU using this sub (along with the previous code). If there are more than one tab, the code checks only the first one.
Sub GetSKUonFirstTab()
Dim IE As Object
Dim webpage As String
Dim i As Long, j As Long
Dim sFind As String, SKU As String, URL As String

sFind = "mboxCreate(""hybris_idp"", ""communityName=Anonymous"", ""SKU="
Set IE = GetIE()

URL = IE.LocationURL
webpage = ""
i = 0
j = 0
SKU = ""
webpage = GetWebpage(URL)
i = InStr(1, webpage, sFind)
If i > 0 Then
    i = i + Len(sFind)
    j = InStr(i, webpage, """")
    If j > 0 Then
        SKU = Mid$(webpage, i, j - i)
    End If
End If
End Sub

Open in new window