Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Getting The Entire HTML Source Code From A Web Page
I am looking to read the source code for a website that keeps the stats for a hockey league in Sweden

For other sites i can use the code below and it works fine, but the site i am using to get the Sweden stats seem to keep the data in some type of a Java app (sorry still somewhat of a newbie) and doesn't work the same as the others

when i veiw the source code just by right clicking the page all the data i want shows up. When i try to use my code it doesn't get the stuff i want.

I have tried both objDoc.body.innerHTML and objDoc.body.outerHTML and i get different results but not the same as right clicking on the page and viewing the source, is there another command that i can use to get it all?

the website is

HTML [url]

Sub Get_Stats()
Const strURIpre As String = [url]
Set ie = CreateObject("internetexplorer.application")
ie.Navigate strURIpre
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do

View Replies!   View Related
Download Html Source From The Web
I need to do seems quite simple, I want to grab the source of a webpage into a string (where I'll then to some fiddling about with it to strip it down to the information which I need). Currently I'm trying to do it using the webbrowser object and meddling around with the .document properties, but I can't figure it out.

UserForm1.WebBrowser1.Navigate UserForm1.address.Value
grrr = UserForm1.WebBrowser1.Document.body.innerText
UserForm1.sourceoutput.Value = CStr(grr)

View Replies!   View Related
Navigate In Ie Using Source Code In Web Page
i am trying to make excel open a web page then naviagte to a link. I know how to do this if the link has the same address all the time using

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
' Go to web page
.navigate [url]

Do Until .readyState = 4

but how could I navigate to a link which continually changes.

for example on a web page the link always shows "report", but the actual address may changes say every hour.

Is there a way to navigate to what ever address sits behind "report"

View Replies!   View Related
How To Save HTML Source Code From VBA
I would need to get HTML source code from any given page. I know how to open a HTML page from Excel and I can do it with VBA, but how to get for example this page's source code?

I would start with making a sub that takes a string (the address) as an input parameter and finish with saving the source code of that address as an text file like c:code.txt

So something like

Dim webaddress as string

Sub GetSourceCode(webaddress)
'then some code to save the source code
End Sub

View Replies!   View Related
Import Web Page With VBA
I need to get VBA code to import a web page one at a time based on a condition. When one of the urls below has "1 mins to post", I want that page imported into a worksheet.
The pages require a login and here is the source code for login - "acctnum" and password - "zipcode". The name for "1 mins to post" is name ="MTP".

I would want to keep the page loaded until "MTP" = OFF. Then whenever the next URL goes to "1 mins to Post", load that web page and keep it loaded until "OFF" and so on.

This would need to continually scan through all of the following urls:

View Replies!   View Related
VBA Code To Activate/Press Button On Web Page
VBA Code To Activate/Press Button On Web Page. press a button on web page using VBA

View Replies!   View Related
Refresh XML Data In A Map If The Source File Is Updated
If I have a spreadsheet with some data loaded in through XML and viewed as a
map, what is the VBA command to reload/reimport the source XML data?

The scenario is that the source data (XML) may change at any time, and I would like to reflect this to the presentation layer (excel) at regular intervals. I have a loop set up but cannot find the command to reset the source XML data.

View Replies!   View Related
Extracting Web Data From A Sub Web Page
I am using the "CreateObject("InternetExplorer.Application")" method to extract data from a webpage. This webpage is a sub page that I cannot find the data for, even after filtering through all the ".document.all(x)" items? Any ideas on how to obtain this "sub page" data, since there is other data that I need which is on the "main" page? I don't want to have to call up this sub page separately since I'm already seeing it (physically able to view it on the main page but not able to extract it) from the main page?

View Replies!   View Related
Save Html Source As Text
I would like to be able to navigate to a site and save the source text of the html into a text file.

View Replies!   View Related
Search HTML Source Code For Text
I need to check a website daily to see if a link has been updated. If it has been updated, the beginning of the link changes to a different date. Example: today link is and tomorrow link may be Lets say the link is on The link does not change everyday, but I think a good way to see if it has been updated is to search through the source code in the html for that link.

Thus I would put into cell A1 and tell excel to search the source code on, and if the contents of cell A1 is NOT found, I'd display a message box stating the link has been updated.

View Replies!   View Related
Drop Down Value From A HTML Page
I have copied a drop down value from a HTML page to excel and this drop down is showing up in execl and I am unable to delete it by either deleting the rows or columns. What do I do to remove the dropdown value in the sheet?

View Replies!   View Related
Copy Web Text / Source?
The problem is, these identifiers are in no discernable or predictable pattern. I cannot open the page directly in excel, nor can I use the Import Data from Web function (2007) ... results are simply a blank page.

What I thought I could do, then, is automate the procedure that obtains the source code, which I can parse and look for the current date. Once I have the line with the current date, I can extract the unique identifier, then paste it back into a string and resubmit to the browser.

I just can't figure out how to get to the source code... anybody out there have a way to get to it? Since this is going to ultimately be distributed to 20 or so analysts in different countries, I don't think I can use other tools (like the HTML Extractor from Iconico).

View Replies!   View Related
Save As Web Page
Hi, i am trying to save a range on Sheet1 as a web page. i have managed to save the entire workbook as a webpage but am struggling to get the right syntax for just the range on sheet1. when i do this manually i select the range, set the print area, then choose file-save as web page- and choose selection:print area or republish if i have done this before. i am not sure also where to add the directory i wish to save it to. the file name always changes, so i can either: use the name of the file or i can use the same file name that is entered into a cell (cell never changes). i hope this makes some sort of sense. here is the code i used to save the entire workbook

View Replies!   View Related
Web Page Interaction
I would like to be able to turn the following worksheet in to an inspection scheduler. It already displays job information pertaining to the job & lot number entered into cells C2 & C3 in cells F2:F14. Now, I would like to write a script that will do the following...

If lets say I click on "Groundwork", it will know that I am trying to schedule a groundwork inspection for the permit number in cell F11, and it will know it needs to be scheduled through Fairfax County's website, because it says "FFX" in cell F2. (the only two counties I really deal with online are Loudoun & Fairfax...I'm not worried about the others). So it will then automatically jump to Fairfax County's website (links to the websites are in cells E22:E26), conduct the appropriate steps to schedule an inspection through that county, then kick back some sort of confirmation that it was scheduled. The same steps apply each time for each inspection type, but each county has their own steps....

View Replies!   View Related
Can I Desplay Web Page
if a web page can be displayed on an excel sheet, like internet explorer, i mean when i open sheet2 for example i can view a web page without switching to the internet explorer?

View Replies!   View Related
Get Elements From Web Page
Dim struserID As String
Dim strPassword As String
Dim strUploadFile As String
Dim strQueryURL As String
Dim objIE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection

Set objIE = New SHDocVw.InternetExplorer
struserID = "xxxxxx"
strPassword = "abcdef"
strUploadFile = "C:Misc estupload.txt"

Code navigates to sign on page, enters userID and password, clicks on submit. The password used causes upload page to load. This all works ok. After upload page is loaded, the code does not enter either of the two following "for" routines.

View Replies!   View Related
Get Number From Web Page
I am trying to get a single number from a web query table without downloading the table to a worksheet. I would like to either turn the table into an array in vba or just extract one number from the table. In this case it would be the "This Week" result or array(2,2) position in the table.

The website:[url]

I have tried messing around with the destination for the webquery object but cannot get it to work unless the destination references (directly or indirectly) a worksheet range.

View Replies!   View Related
Link To Web Page
I want to create a sheet where the weather is updated automatically. for example,


Toronto -9 20% rain
Montreal -20 40% rain

thats what it should look like or similar. The problem is I want to have a formula for the temperature where it gets an update from the internet. So everytime I open or refresh this sheet, it would change to whatever the website is.

View Replies!   View Related
Insert Cell Value To The Web Page
I have list of URL's (5000+) per country which need to be check whether they are working or broken. I have found this website which solves my purpose but processing 5000+ URL's manually will consume lot of time

So is it possible to have macro which will pick URL from each cell & run it in "URI:" textbox of the above link.

View Replies!   View Related
Check If The Web Page Is Updated.
I have a list of urls and I need to check them regularly, if they are updated or not. All the pages are from the same website and in same format. The information I need to check if updated or not is inside a table.

View Replies!   View Related
Activate Web Page For Entry
I have created a spreadsheet and a VBA form to save this information for each individual. As it stands right now, you enter the information it checks to see if it needs to create a new sheet and enters all the data on the first available line. That part is working perfectly.

What I need now is once it has put the information in the correct sheet, i need to bring the webpage to the foreground and input the data stored on the webpage. When I tried AppActivate it only activates the window, but it doesn't bring it to the foreground so no information is every entered.

I tried Application.Sendkeys ("% R") to restore it, but it is very hit and miss.

The title of this webpage is "Verizon Sell One More [Make a Referral] - Microsoft Internet Explorer provided by Verizon"

Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate ""
Do Until .ReadyState = 4: DoEvents: Loop
End With ..................................

View Replies!   View Related
Compile A Web Page To Which There Are Many Many Queries?
how would you compile a web page to which there are many many querys?
i.e a website that has links to results

View Replies!   View Related
Read Data From Web Page
how to read entire table from the webpage/URL. But does anyone know if I want to read only one particular number from a specific location on a web page.

View Replies!   View Related
Activate Web Page Application
Is it possible to have Excel place a mouse click on another program through a VBA code or another method? For example, my Excel sheet imports live data from another program and performs calculations on this data. If for example cell A3 is greater then 10, I would like to have the program click on another application window. Is this even possible? Who can I find to write this code for me? Excel NOOB here.

View Replies!   View Related
Navigating To A Web Page Programmatically
Until recently I successfully used this code to navigate to a web page and populate some of it's boxes, before programatically pressing the page's "Validate" button, whereupon the page would test the data I'd populated the boxes with, and come back with an answer to tell me if there were any errors in my data:

Private Sub CommandButton1_Click()

Dim uf1_cbutt1_click_ie As Object

Set uf1_cbutt1_click_ie = CreateObject("InternetExplorer.Application")

Can anyone shed any light on how I may be able to programatically navigate with VBA to the page I want? I'm also having trouble finding the new name of the "Validate" button - and how to programatically click it.

View Replies!   View Related
Login To Web Page Via Add-in
I use a tool that connects to It is an xla addin. It references a toolkit that is installed...

View Replies!   View Related
Copy/Pasting From Web Page
I am a novice when it comes to excel, but I have a project due at work where I need to search out correct addresses and copy and paste them into the cells where the incorrect info already exists. Here is my problem, I have been working on this no problem for about a week now and today all of a sudden when I copy the text from my search result on google and try to paste it into the cell, it pastes the google link, instead of the text I copied. It does this for the entire row but will not do it for row after row, I might be able to get the next 5 or 6 rows to accepet the correct text, and if I try to just type the info in, it appears as a clickable link.
I cannot figure out what I did, if anything or how to fix it so that I can get this work done.

View Replies!   View Related
Import Web Page Via XMLHTTP
I am trying to automate data downloads into Excel from a webpage, similar to webquery but slightly more complex. In the browser I can only get the data I want if I select a checkbox. As soon as the checkbox is clicked, the webpage reloads with the right data, and this is what I am trying to import into Excel via XMLHTTP.
The html/javascript code related to the checkbox on the website is:

<form method="post" name="tsOptions">
<input type="checkbox" name="allRecs" onclick= 'form.submit();'>

The ... contain some html stuff and further controls like radiobuttons. Anyway, I know how to load the website into Excel VBA as is before the checkbox is selected. Now how do I implement this PUT/submit.form() thing? the VBA code so far is:

Set oHttp = CreateObject("MSXML2.XMLHTTP")
oHttp.Open "GET", "http://...?option1=...&option2=...", False
a = oHttp.responseText 'a contains source code of website

View Replies!   View Related
Java Page Web Query
Is there a way to call a Java Script / a JSP page in VBA. we have to get data from 270 different java pages from intranet for further VBA workings in Excel.

View Replies!   View Related
Pull Web Page Into Worksheet
Can you use something like that to pull "All the text" from that URL back to Excel (like in Select All, Copy, PasteSpecial) rather than using SendKeys? The existing URL listed in your Test Sub would be fine to use in the example. If so, could you please modify the Test Sub code to show the code?

View Replies!   View Related
Embed Web Page In Worksheet
I am trying to embed part of a webpage into my worksheet. Specifically, I want to include a form from a web-site that allows me to enter a flight number. I then want the results to pop-up in a new web-window.

So, I want the search box (or a section of the webpage) to appear in my excel worksheet so that I don't have to always open IE when searching for data.


That's the web page.

View Replies!   View Related
Web Page > Memory Reduction
I have windows XP and xl2k. I have a single- sheet workbook (9MB) linked to another multi-sheet workbook of databases (20MB). The single-sheet workbook has about 100 pictures (30k ea) that, when a number is entered, searches the database. If no match is found for a given picture and product info, then the picture is deleted and the rows are hidden (macros). Usually, about 10 products are found in the database for any one entry.

My goal is that I can "save as > web page" and email these results off to my customer. The problem is the after-the-search result page is about 1MB and so too is the .htm file that is created when I "save as > web page." I was thinking that 10 pictures at 30k should be around 300k plus the formatting (no?). I cleared the "tools>option>general>web options>general>save any additional hidden data..." to little effect. Is there a macro that will delete the hidden rows which I should run AFTER the product search (because deleting them instead of hiding them messes up the macro run) - I'm sure that would help (right?). But my original thought was that the .htm file size would not be affected by hidden rows.

The per-item section of excel is 375 cells + the picture with simple font and formatting and no other objects, etc... When I delete the picture and save as > web page just this one-item section area (375 cells), the .html file is 37kb in size. That seems large to me (yes/no?). (10 x 37K = 370K before pictures!). This may be a reasonable file size, I'm no expert. I read a post where xl2003 has a compression application - is that what I need, or is there a 3rd-party app that can help me reduce the size of this file in htm. Or, is this the best I can expect from this method and I should be exploring elsewhere (metatags?).

View Replies!   View Related
Import Chart From Web Page
There is a chart at the center of this page:
I can't import it into excel using DataImport External Data New Web query
Is there a way to grab that data in VBA or in Excel somehow?

View Replies!   View Related
Web Query On Non Specific Page
I'm fine with Web queries generally (I'm using these for returning options pricing data) and have set up the query with code to loop through the options I want the prices on, go get them, and format the data usefully on my side. No problem there.

To get a web query to work (I understand) it needs the exact web address to go to

That's pretty easy. However the page I'm trying to source data from doesn't change at all: it's

I can't see how to now 1. enter the symbol (e.g AA), 2. choose a dropdown option (i.e Greeks) 3. choose a second dropdown option (i.e All Strike Prices) and get the data from the table.

View Replies!   View Related
Find Form By Name On Web Page
I'm working on automating a task in internet explorer with excel. A webpage contains a form with fields. The fields have different names. I have a loop that figures out name of the field and the value I want to put into it. This is what im tying do do:

Dim myString As String
myString = "myField"

ie.document.forms(0).myString.Value = "hello"

View Replies!   View Related
Import From PDF Embedded On Web Page?
Working on automating a data update process for my department (well, for the Italian speaking analysts anyway). I'm trying to import data from an embedded PDF to my Excel 2007 worksheet - I only need the data on the PDF, but if the whole screen can be scrapped I can clean it up in my code.

Any ideas if/how I can do this?

View Replies!   View Related
Web Page Soft Returns From Asp
I am having much difficulty trying to fix this. I need any hard returns pulled from my query to be rplaced by soft returns on a excel web page. The code I recently tried is as follows...

Sub showProducts()
%> <%

If row = 0 Then
response.write "<tr BGCOLOR=" & "'#FFFFCE'; >"
row = 1


response.write "<tr BGCOLOR=" & "'#CEFFCE';>"

row = 0 ................

View Replies!   View Related
Macro: Paste Special From Web Page
I need to create a macro to "paste special - values", therefore I have coded

"Selection.PasteSpecial Paste:=xlPasteValues"

It works well if copy and paste special within the same excel sheet.
However, when I ctrl C on any other website and revert back to excel to use my macro to paste special, it cannot work.

View Replies!   View Related
Text To Numbers :: Invoice From A Web Page
I would like to be able to copy an invoice from a web page, paste it into Excel and then run a macro on it that will strip away all unwanted lines. That much I have gotten fine. Obviously, part of this invoice is numbers (Qty, Unit Cost, Total Cost, etc.) but when my macro is complete these numbers are all stored as text.

What steps would I need to add to convert these numbers to numbers? I would like for this to all happen within the macro. This completed spreadsheet would then be linked to a table in Access where calculations will need to be performed.

View Replies!   View Related
Import Web Page From Mirror File
I have some software that was written for me that scrapes data. It has a file in it that mirrors the web pages that it is scraping.

I would like to actually import those web pages one at a time based on a condition in the browser. The code of the web page is html. When one of the 35 mirror web page's "MTP" = "POST", I would like for all the web page to be loaded into a sheet called WPS. I would want that to stay loaded until "MTP" = "OFF". There may be more that one of the mirrored web pages with "MTP" = "POST". In that case, only one can be loaded at a time and that is fine. Once the "MTP" = "OFF", I want it to go to the next mirrored web pages with "MTP" = "POST" and load that web page from the mirror file.

Here are 4 of the 35 web pages that are mirrored and their paths

View Replies!   View Related
Extract Web Data From Part Of Page
I would like to extract some data from a web page, but only part of it. The website is:


and I have a spreadsheet with many different ProjectId hyperlinks in a column (the final six-digit number). All I want is the large block of text that is in the middle of the page. Sometimes there is no text, as in:


So I don't know how to deal with that either...

I would just like to paste the text into a cell adjacent to the hyperlink. I know that there is a visibility issue with 1024 characters, but can cope with that (no text will be longer than the 32k+ chars).

Any help would be appreciated - it's for my wife's PhD, and I just want to make it easier on her. I am reasonably ok with macros, but am by no means expert! If you need clarification,

View Replies!   View Related
2000 - Save Data As A Web Page
Using excel 2000 on windows XP (home). When saving data as a web page the grids do not show despite having the page setup "show grids" box ticked. It makes reading the file on line hard to follow.

View Replies!   View Related
Publish Excel To Internet Web Page
I have this spreadsheet that I use regularly and that others also view (not edit) and to facilitate viewing, I would like it to go on the net.

Ideally, simple editing would be done from within the browser.

View Replies!   View Related
Use Code To Determine Files On Web Page?
I need to build an Excel application that allows a user to select and download files from a website. The files change daily. Is there a way in VBA to obtain the current list of files that are available for downloading?

View Replies!   View Related
Open Web Page Macro Code
I am currently using the following code in order to open a web page by clicking a button:

Private Sub Timetable18_Click()

ActiveWorkbook.FollowHyperlink Address:=""
NewWindow = True

End Sub

However, when this is executed it returns the following error:

Run-time error '-2146697210 (800c0006)':
Method 'FollowHyperlink' of object '_Workbook' failed

Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.

View Replies!   View Related
Extract Web Page Elements Into Worksheet
I recently cahve been working with a lot of webpages. Documenting the pages is quite loborious and inaccurate. I recently came across a utility that would explort all of the elements, their types etc and put it into a worksheet. For the life of me, I have not been able to find it. I was wondering if anybody knows of a utility like this, or how I could write a macro to parse this info.

View Replies!   View Related
Tab Delimited Data From Web Page
I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process. The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.

One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet. Here is the relevant code from within the Web Browser's class module:

If URL Like "*" Then
Application.Wait Now + TimeValue("00:00:01")
SendKeys "^a^c", False
Application.Wait Now + TimeValue("00:00:01")
ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3"). Range("A1")
End If

The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data. But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications. So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?

View Replies!   View Related
Open Web Page Via Macro Code
I have been looking for some VBA code that simple, when it runs, opens a browser and a specific, hard coded website. Is this possible in VBA?

View Replies!   View Related
Auto Update Of Data To Web Page
I did try a search before posting this - either I am too stuipid or the info was a bit too cpmplicated and in bits and peices for me to figure out. My query is this
Lets say I have a workbook with around 10 sheets in it. and I have a saved a copy of the sheet ias a webpage (HTML ) for user convinence. Now is it possible to somehow ensure that when the orginal workbook is updated then this webpage would also get updated - both would be in the same drive ( I dont want to use internet for this purpose)

View Replies!   View Related
Xml Integration: Use XPath To Point To A Tag In Which To Insert Text Using VBA
I'd like to send data from an excel sheet to an XML file that already exists. Is there a simple way to use XPath to point to a tag (in which I would want to insert text) using VBA?

View Replies!   View Related
Copyright 2005-08, All rights reserved