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

urldownloadtofile http://anywebpage.com/index.html anyfile.txt

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.

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