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
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
Const strURIpre As String = [url]
Set ie = CreateObject("internetexplorer.application")
If ie.ReadyState = 4 Then
ie.Visible = False
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.
grrr = UserForm1.WebBrowser1.Document.body.innerText
UserForm1.sourceoutput.Value = CStr(grr)
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")
.Visible = True
' Go to web page
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"
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
'then some code to save the source code
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:
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.
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?
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 www.10212009dave.com and tomorrow link may be www.10222009dave.com. Lets say the link is on www.gugg.com. 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 www.10212009dave.com into cell A1 and tell excel to search the source code on www.gugg.com, and if the contents of cell A1 is NOT found, I'd display a message box stating the link has been updated.
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).
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
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....
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.
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.
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.
Link To Web Page
I want to create a sheet where the weather is updated automatically. for example,
CITY TEMPERATURE PERCIPATATION
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.
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 http://www.seoconsultants.com/tools/headers.asp 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.
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")
.Visible = True
Do Until .ReadyState = 4: DoEvents: Loop
End With ..................................
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.
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.
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.
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.
<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
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?
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.
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?).
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?
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 https://us.etrade.com/e/t/invest/quotesandresearch
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.
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"
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?
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...
If row = 0 Then
response.write "<tr BGCOLOR=" & "'#FFFFCE'; >"
row = 1
response.write "<tr BGCOLOR=" & "'#CEFFCE';>"
row = 0 ................
Macro: Paste Special From Web Page
I need to create a macro to "paste special - values", therefore I have coded
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.
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.
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
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,
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()
NewWindow = True
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.
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.
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 "https://www.fedex.com/FedExMMA/downloadcenter.do*" 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")
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?
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)