Getting Web page source (HTML or XML) in VBA?

I would like to retrieve contents of a web page, be it HTML or XML, into VBA variable!

Later, I would chop, cut, parse or extract the data I need.

Both importing as XML or WebQueries is unsatisfactory for a certain number of pages I need. XML has bad schema, WebQuery tells me it can't find any data.

I tried with WinHTTPRequest, but Excel gives me back error "undefined user type" in other words it doesn't recognize that object.

Basically I want the source of web page to become a string in my VBA code. In other words that would be replication of funcionallity of

Please Login or Register  to view this content.

which is provided by AutoHotKey scripting language (if anyone used it, very simple and effective for many things).

Can it be done in Excel's VBA?

I get no takers on this one?

Come on, this is chalenging, it's not like "How do I display a message box before deleting something?" or "How do I make cell A4 red?". Not that I don't think these don't deserve replies, they just have standard answers.

I've done some related stuff ,but I never found a way to download the source file.

I did end up of having success downloading tables where I could reference individual elements in tables , count how many tables were on a page etc.

Prior to that I was also seeking to download the source file, but abandoned that route once I able to selectively pick my data directly off the webpage.

If you think it can be any help I can probably dig up an example.

Thanks SuitedAces (are they red or black?)!

The very reason I'm looking to download source is because using WebQuery to retrieve data fails to retrieve data from pages I need. I don't know the reason why is that so.

Example:
http://www.wagerline.com/Scores-Odds...ontID=9361&t=0

Site displaying various betting and sports info. In this particular example we have a boxscore which has perfectly formatted HTML as source.

You can verify that tables are really present by looking at HTML source or by using WebDeveloper add-on for Firefox which can display information on just about any part of the web page! This particular page has 5 of them: login box, two team statistics, technical fouls and team statistics.

Why Excel's WebQuery can't pick any of them - I have no idea (it gives something like "connection is there, but there are no data"). That's why I need the source to parse out tables myself.

Ok, check that , I WAS able to open the source file the second time I tried.
That was strange.

Ok, check that , I WAS able to open the source file the second time I tried.

How did you managed that?

because I wasn't able to look at the source file from the menu.

Mozilla Firefox (unlike IE) with its extensions or add-ons (WebDeveloper, FireBug, View Source Chart, TableTools etc.) is your friend. You'll find anything you ever wanted to know or extract from a particular web page and much more.

I don't understand the question , red or black ?

I thought your screenname came from poker terms, "suited connectors" 76s meaning 7 and 6 of the same suit like spades for example!
Aces being cards of the same rank cannot in fact be suited, you can only have two red (hearts and diamonds) or two black (clubs and spades) aces in hole cards.

But same terms have different meanings for different people. You might have meant a "dressed up (suited) champion in his field (Ace)"?

Good stuff, SuitedAces!

I guess if you can instantiate IE object you should be able to instantiate WinHTTPRequest object, however I fail at that!

The simplest solution that I found to actualy getting "your hands on the source of web page" is this (in pseudocode):

Please Login or Register  to view this content.

Haven't implemented it yet, but it should work.

I was just hoping for a neat all-Excel solution!

http://word.mvps.org/FAQS/MacrosVBA/...eClipboard.htm

As far as the source code goes, at this point I'm not convinced that parsing it would be any less time consuming than working with the HTML Document Object .

It wouldn't, parsing it would be certainly more time-consuming. I just don't know how to access HTML DOM programtically.

Give me a liitle time and I will put together a sub that will demonstrate the htlm doc properties that I am familiar with .
With these I was able to write the procedure in the example I uploaded.
Also I will find some links for the HTML Document Object Model (HTML DOM) reference.

Great stuff SuitedAces. Thanks a ton!

Yes, very nice stuff, thank you! I was looking for the solution to the same problem (grabbing an html page source code since web query won't work in the page that I want to parse), Almighty Google directed me to this page, and I register just to take a look at your code and found something amazing.

This stuff is just what I was looking for but i can't seem to download the attachments. Can someone advise. Thanks

I am trying to automate a Web query that pastes the data on a spreadsheet.
It seems I am 95% there however yet an inch is as good as a mile.

I need to step thru several web pages. I have a cell on the worksheet that creates the new wed address but a just need a command that will use the web address on the worksheet.

As an example the cell P10 formula = http://www.thenewwebaddress.com./1950.xml

Every new web address changes the _195?.xml to say _1951.xml then 1952.xml etc.

I'm using this code I found on this site.

_____________________
Const MyUrl As String = Activesheet.cells("P10").String <<problem child

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
_____________________

Can anyone advise the code I need to use to grab the changed wed address from cell P10 on the worksheet?

Thanks

Making progress however every time I run the query again it pastes the new date X rows to the right. Is there a true/false I need to change?

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

Brilliant code, SuitedAces!

Working off your code, I was able to turn it into an automated share count extractor from Yahoo Finance (soon to port it to Google Finance as well). Sheet2 now has a list of ~500 stock symbols in column A, and column B is being filled in one by one (~4 seconds/stock symbol) with the respective share count. It's anything but robust at this point, but it's handling the job flawlessly.

It uses two loops - one to check each of the cells of coded output for the correct piece of info, and one to run through the list of symbols, changing the URL each time.

If anyone would like this code, it's attached...

how to get the data from a particular tag in website?

Great coding and kind of along the lines of what i've been looking for - but is there a fix regarding the URL - ie - can this be called from cell A2 as originally designed?

I have started a new thread but no responses