Data scraping - VBA

B

Buggington

New Member
Joined
Mar 28, 2014
Messages
8
Hello :)

I'm a total VBA newbie, but need to create a data scraping tool. I've managed to find something on YouTube that showed me how to do the majority of what I want, but it doesn't seem to be working.

This is what I have so far:
Code: 
Sub ie_open()
    
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Open site to login and find product?"
 
    ' Dialog's Title
    strTitle = "EPOS"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
        If iRet = vbNo Then
        MsgBox "Cancelled."
    Else
        Dim ie As Object
        Set ie = CreateObject("InternetExplorer.Application")
        ie.Navigate "[URL="http://www.google.com"]www.google.com[/URL]"
        ie.Visible = True
        
        MsgBox "Click OK when page is ready"
    End If
    
    Do While ie.Busy: DoEvents: Loop
    
    Set TDelements = ie.Document.getElementsbytagname("GridLabel-")
    r = 0
    c = 0
    
    For Each TDelement In TDelements
    Tabelle1.Range("A1").Offset(r, c).Value = TDelement.innertext
    r = r + 1
        Next
    
    Set ie = Nothing
    
    End Sub

When I step through the code, everything runs absolutely fine until I get to the "Tabelle1.Range" line, where it seems to skip straight to "Set ie = Nothing" rather than get the data.

Any ideas what's happening here? It's worth noting that I've changed the web URL as it's company data.

Thanks :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Firstly, I had to change Tabelle into Sheet1 to work with my version of Excel.
Also, changing the url to Google means that different text is returned from the web page so I can't check whether the TagName "GridLabel-" exists or not on your page.

I suspect that no tag names of "GridLabel-" have been found and that is why it is jumping to the end.

Two things to try:
1. Look at the source of the website. In Internet Explorer use the View-->Source menu item and search for "GridLabel-". If there isn't one then that is why it can't be found.
2. Add a Debug.Print statement to the code straight after the "For Each" line:
Code: 
    For Each TDelement In TDelements
    Debug.Print TDelement.innertext

That should print what the macro has found in the VBA Immediate window.

Do you know if the "GridLabel-" value is right or was it in the code you copied?

Basically, you need to search for different things on different people's pages so one set of code does not work for everyone. If you look at the source code you will see what Excel is having to process. It needs to find strings in there that are near to the data you need to scrape. Then it can copy that data into Excel.

Please forgive me if you already know that.
 
Upvote 0
Thanks for replying; I've added that code where you suggested and it still skips straight past it.

I've found "GridLabel-" in the source code of the website - I've pasted a bit below. The data that I'm looking for starts with "2 - Stratford" and I want a lot of the data that goes beyond that.

Code: 
<

[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="21999182">[/COLOR][/COLOR]464 - Harlow PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="15083893">[/COLOR][/COLOR]470 - Rayleigh Weir PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="60066153">[/COLOR][/COLOR]479 - Hereford PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="18700353">[/COLOR][/COLOR]494 - Larkfield PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="10381187">[/COLOR][/COLOR]498 - Marshall Lake PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="3051546">[/COLOR][/COLOR]575 - White Rose PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="13691654">[/COLOR][/COLOR]883 - Wakefield PFS CLSD[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="14549178">[/COLOR][/COLOR]1001 - Bury St Edmunds PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="16641692">[/COLOR][/COLOR]1080 - Scarborough PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="23622547">[/COLOR][/COLOR]1100 - Arnold PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="12843586">[/COLOR][/COLOR]1112 - Meadowhall PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="44526384">[/COLOR][/COLOR]1199 - Bishop Auckland PFS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="14832282">[/COLOR][/COLOR]9012 - ST.ALBANS[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="8537462">[/COLOR][/COLOR]9034 - STOKE[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]Value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="8565398">[/COLOR][/COLOR]9048 - Rye Park New[COLOR=#a31515][COLOR=#a31515]OPTION[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SELECT[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=">"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('2');SetFormAction('MoveToIteration', 'APP', 27, 2, '3051467');return false"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=">>"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('2');SetFormAction('MoveToIteration', 'APP', 27, 2, '3051469');return false">[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SPAN[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SPAN[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="font-size: 12px">[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SPAN[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]DIV[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]DIV[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="Container-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=""><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]A[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]NAME[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="ContainerId_3">[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]A[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]DIV[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="Navbar-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="FONT-WEIGHT: bold; COLOR: #f26334">[/COLOR][/COLOR]Weekly (w/e)[COLOR=#0000ff][COLOR=#0000ff]<[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]br[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SPAN[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="font-size: 12px"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="<<"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('3');SetFormAction('MoveToIteration', 'DIT', 64, 3, '2014-01-04');return false"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="<"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('3');SetFormAction('MoveToIteration', 'DIT', 64, 3, '2014-02-15');return false"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]INPUT[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="text"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]tabindex[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="1"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]size[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="15"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]name[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="JumpDate3"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="22/02/2014"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]onchange[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="return true;"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]a[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]href[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="javascript:show_calendar('frmView[\'JumpDate3\']','1','2014','dd/MM/yyyy','January,February,March,April,May,June,July,August,September,October,November,December','Sun,Mon,Tue,Wed,Thu,Fri,Sat','../Themes/DTIR2','1');"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]***********[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="window.status='Date Picker';return true;"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]**********[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="window.status='';return true;"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]img[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]src[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="../Themes/DTIR2/images/calendar.gif"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]border[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="0">[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]a[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="Go"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('3');SetFormAction('GoToDateIteration', 'DIT', 64, 3, JumpDate3.value, '2013-02-19', '2014-04-05');return false"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=">"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="SetActiveContainer('3');SetFormAction('MoveToIteration', 'DIT', 64, 3, '2014-03-01');return false"><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]input[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]type[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="button"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]disabled[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="1"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]class[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="pagebutton"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]value[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=">>"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]*******[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="return false;">[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]SPAN[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]DIV[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]><[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]TABLE[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="Grid-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]=""[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]border[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="0"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]cellpadding[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="2"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]cellSpacing[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="0">[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]TR[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]TD[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="GridItRepeatLabel-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="COLOR: #f26634">[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]div[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="GridItRepeatLabel-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="COLOR: #f26634">[/COLOR][/COLOR]2 - Stratford[COLOR=#a31515][COLOR=#a31515]div[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]TD[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]TD[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="GridLabel-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="">[/COLOR][/COLOR]
[COLOR=#0000ff][COLOR=#0000ff]
<
[/COLOR][/COLOR][COLOR=#a31515][COLOR=#a31515]div[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]CLASS[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="GridLabel-"[/COLOR][/COLOR][COLOR=#ff0000][COLOR=#ff0000]STYLE[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]="">[/COLOR][/COLOR]22/02/2014[COLOR=#a31515][COLOR=#a31515]div[/COLOR][/COLOR][COLOR=#0000ff][COLOR=#0000ff]>[/COLOR][/COLOR]
 
Upvote 0
I have just revisited one of my similar macros. It uses MSHTML rather than IE but is otherwise similar. It took a while to get that working because it seems that Microsoft have changed some security settings since I wrote it.

I tried out your code as well. I had to change the worksheet and the tag to suit what was in Google and it worked as I expected.

I wonder if your security settings need updating as well. The ones I had to change were in Internet Options-->Security in the Internet section. I clicked on Custom Settings and checked that in Miscellaneous "Access sources across domains" and "Navigate windows and frames across domains" were both set to Enabled.
 
Upvote 0
If it is company data does that mean that it is on an intranet rather than the Internet? If so then you will need to look at your Local Intranet security settings instead.

It might be worth setting up an "Add Watch" on the TDelement and TDelements objects. Just right click both and select Add Watch. Set a breakpoint (F9) on the Tabelle line and run the program. You should see both objects in your Watch window. They should both have + signs against them when the program is running (or paused at a breakpoint). If you can drill down into these objects and view the associated data then it means that the object are being populated.
 
Upvote 0
It's not on our Intranet - it's sales data that comes from retailer's website, so it's Internet based. I changed those settings and it still skips straight past those lines.

I added the watches and both TDelement and TDelements were as below:

Value: <out context="" of="">
Type: Variant/Empty
Context: Sheet1.ie_open

Does this reveal anything else?

Thanks for all your help :)

</out>
 
Upvote 0
"GridLabel-" is a class name, not a tag. Tags are things like OPTION, TR, TD, DIV.

It's easier to develop HTML parsing code if you set references to Microsoft Internet Controls (InternetExplorer object) and Microsoft HTML Object Library (HTMLDocument object and related classes) via the Tools - References menu in the VBA editor. Then you get the intellisense output of the various properties and methods.

The OPTION tags will have a parent SELECT tag which you haven't shown, so if you find that you can loop through and extract the text of each option value, like this:
Code: 
    Dim IE As InternetExplorer
    Dim r As Integer
    Dim selectElem As HTMLSelectElement, optionElem As HTMLOptionElement
    
    Set selectElem = IE.document.getElementsByTagName("SELECT")(0)
    r = 0
    For Each optionElem In selectElem.Options
        Tabelle1.Cells(r, 1).Value = optionElem.Text
        'Sheet1.Cells(r, 1).Value = optionElem.Text
        r = r + 1
    Next
The above code assumes that the IE object is loaded with the web page and the SELECT element in question is the first SELECT element. If not, change the (0) to (1), or (2), etc. Or reference it using getElementById("theId") if it has an id, or getElementsByName("theName")(0) if it has a name - again the (0) might be (1) etc.
 
Upvote 0
Hi John,

Thanks for the advice, and while I understand the part about tags the rest is a bit too high level for me! Could you explain a bit more please?
 
Upvote 0
Library references - in the VBA editor click the Tools menu then References. Scroll down and tick the box next to MS Internet Controls and MS HTML Object Library. This allows you to declare VBA variables as an appropriate data type from each these libraries. So instead of declaring IE as Object, you can say:
Code: 
Dim IE As InternetExplorer
Set IE = New InternetExplorer
and intellisense is available in the VBA editor to show the properties and methods of the IE object as you type in "IE.".

Similarly, MS HTML Object Library allows you to declare VBA variables such as:
Code: 
Dim HTMLdoc As HTMLDocument
Set HTMLdoc = IE.document
Combining your code with my code gives:
Code: 
Option Explicit

Sub IE_Open()
    
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim selectElem As HTMLSelectElement, optionElem As HTMLOptionElement
    Dim i As Integer
        
    Set IE = New InternetExplorer
    With IE
        .Navigate "www.google.com"
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        While .document.readyState <> "complete": DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Set selectElem = HTMLdoc.getElementsByTagName("SELECT")(0)
    i = 0
    For Each optionElem In selectElem.Options
        Tabelle1.Cells(i, 1).Value = optionElem.Text
        'Sheet1.Cells(i, 1).Value = optionElem.Text
        i = i + 1
    Next
    
End Sub
Makes the same assumption as previously posted and is guesswork without knowing the real URL.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA scrapping of USPTO website
2 3
Replies
28
Views
828
Domenic
D
O
  • Question
Another problem reading HTML-page
Replies
2
Views
235
Oldfox
O
B
  • Question
Edge automation without installing selenium
Replies
1
Views
7K
John_w
J
O
  • Question
Reading html file from local hard drive
Replies
1
Views
174
Ruddles
Ruddles
O
  • Solved
Unable to download html data from specific site
Replies
7
Views
166
Edgar_
Edgar_

Forum statistics

Threads
1,216,124
Messages
6,128,990
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top