Sleeper: Excel VBA and HTML pages

[vanduzee_t]

Hello
I have been researching and asking questions on other forums, but I still can not figure this out.

I have an excel sheet (blank).
What I need to do is to write some code to retrieve data from a table on an HTML page.

I know that the url will have the same domain (ie: http://www.mydomain.com/pages/ )

I have researched the HTMLDocument option, but still cannot seem to get it. Ive tried the Get external data option, but still no.

This webpage may have several tables in it, but I need to find the right table and the right cells to import (even copy/paste would do).

The cells in the table will always be constant (only the content in the cells change) and the table will always be in the same place on the page. It is a dynamic page, so it is always the same... only the content changes.

I need to be able to control this from a command button on the sheet.

Any suggestions?

Thank you
Terry

[Jacob Hilderbrand]

What happens when you try to use the get external data option?

[vanduzee_t]

It gives me an error saying there was no data to get.

[TonyJollans]

Hi Terry,

Welcome to VBA Express!

I'm no expert in this area but with a bit of trial and error I got this to pull your post here into Excel. It should give you something to work with.

Dim objWeb As QueryTable
Set objWeb = ActiveSheet.QueryTables.Add( _
                  Connection:="URL;http://www.vbaexpress.com/forum/showthread.php?t=1058", _
                  Destination:=Range("A1"))
With objWeb
.WebSelectionType = xlSpecifiedTables
    .WebTables = "10" ' Identify your HTML Table here
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

[Ken Puls]

Tony,

I've been wondering how to do that for ages!

Can you tell me though, is it possible (or do you know how) to run that on the active web page in the browser?

I have a site that I go to for my liquor inventories to cost items, but I always have to search to find the item first, then hand copy the info to Access. My issue is that the web page is different every time.

Thanks,

[TonyJollans]

Hi Ken,

I'm just beginning to play with web pages and excel and am no expert.

I think to do what you ask may require a completely different approach using the InternetExplorer Object.

And, if you want a currently active browser window, you will probably need to use GetObject. The problem here is that you cannot control which, of several open browser windows, you will get so you must be sure there is only one.

I haven't had a lot of joy navigating the HTMLDocument object yet.

Depending on exactly what you are doing, you might be able to grab the URL from your browser and feed it into something like what I posted earlier.

I'm guessing really and I'd have to play around to say much more than that. I will post back if I work it out but don't hold your breath

[Ken Puls]

Thanks Tony,

I'm going to try and fool around with it as well a bit. I think I might be able to get the current URL from IE... maybe... and feed that it. I was actually hoping to read the table into an array to manipulate it though, but naturally the first row is made up of merged cells (at least when pasted to Excel), so that's thrown a kink in there.

I'll post back if I find anything as well, but probably a new post, as I don't want to hijack Terry's post (anymore than I already have!)

Cheers,

[vanduzee_t]

I did find the code given earlier, worked. It gave me the info I needed; and more.

I don't need the url of the open browser page. Infact, at the time I run the code, I will have approx 18 browser windows open.

I know the site/domain that it will have, but I will need to use a wild card for the rest of the path. ie: http://www.domain.com?=RRej0..... etc etc; I know the domain portion, but because it is a dynamic php page, the rest will change based on the client's information on the page.

Im also trying to find a way to trackdown 3 particular cell on the table. I know I can use the HTML Elements (but I don't know how to use them) for a table.
Here is an example of what I "had" been working on (with the help of others); the problem with this is that I would need to cycle through all elements on the page and by hit/miss method, find the right values.

Dim ieApp As Object
Dim ieTbl As Object
Dim sws As SHDocVw.ShellWindows
Dim ieDoc As Object
Set sws = New SHDocVw.ShellWindows
For Each ieApp In sws
Set ieDoc = ieApp.document
If TypeName(ieDoc) = "HTMLDocument" Then
MsgBox SHDocVw.OLECMDID_SELECTALL
' Activedocument.HTMLProject.HTMLProjectItems(1).Name
' If ieDoc Like "http://www.somedomain.com/*" Then
For Each ieTbl In ieDoc.all
If TypeName(ieTbl) = "HTMLTable" Then
Stop
If ieTbl.Cells(0).innerText = "sometext" Then
Range("A5").Value = ieTbl.Cells(10).innerText
'Debug.Print ieTbl.Cells(10).innerText
End If
End If
Next ieTbl
End If
Exit For
Next ieApp
Set ieApp = Nothing

Then, after all that, using the range(A65536).xlUp, I could find the next row to place the information.

Thank you so much
Terry

[TonyJollans]

Thanks for that comeback, Terry.

I shall pinch that and play with it - I think it will help Ken, as well.

[Ken Puls]

Hi Terry,

Do you have any specific libraries referenced to run your code? I keep bombing on with a "User Defined Type Not Defined" error on

sws As SHDocVw.ShellWindows

[vanduzee_t]

I have microsoft internet controls set for references and Microsoft HTML object library set.

[Ken Puls]

Hello,

Thanks to the code from both of you, I've managed to solve my core issue!

Normally, I have to go to my website and search for the item, and that won't change. Once I've found it now, though, I can return to Excel, and run the code below. It will evaluate all open IE windows to see which has the main part of the product URL (http://www.bcliquorstores.com/en/products) and then pull all the data from the appropriate table to Excel. Ultimately I'm going to code this for Access, but I'm pretty excited that I got this far! The only issue I have is that I have to make sure I only have one IE window with a BCLiquorstore link, or I may get the wrong info.

Sub QueryLiquorTable()
'Requires reference to Microsoft Internet Controls
'Should work with any web table, provided it is not in a frame!
'Can change the coreURL to be assigned from a worksheet
Dim sws As SHDocVw.ShellWindows
Dim ieDoc As Object
Dim n As Integer
Dim objWeb As QueryTable
Dim CoreURL As String
'Set main URL to evaluate open IE windows
CoreURL = "http://www.bcliquorstores.com/en/products"
Set sws = New SHDocVw.ShellWindows
'Cycle through all open IE windows and assign the window whose URL
'matches that decided above to a variable
For n = 0 To sws.Count - 1
    If Left(sws.Item(n).LocationURL, Len(CoreURL)) = CoreURL Then
        Set ieDoc = sws.Item(n).Document
        Exit For
    End If
Next n
'Retrieve the web table from the IE window
If Not ieDoc Is Nothing Then
    Set objWeb = ActiveSheet.QueryTables.Add( _
        Connection:="URL;" & ieDoc.URL, Destination:=Range("A1"))
With objWeb
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "2" ' Identify your HTML Table here
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
Else
    MsgBox "Sorry, the specificied domain is not showing" & vbCrLf & _
        "in any open internet explorer windows", vbOKOnly + vbCritical
End If
End Sub

IIm also trying to find a way to trackdown 3 particular cell on the table. I know I can use the HTML Elements (but I don't know how to use them) for a table.

Terry, I did you get your issue solved yet? I don't know anything about HTML elements, but is there a Find method that you can use, rather than looping through all the cells?

Cheers,