Getting Web Page Source (HTML Or XML) In VBA

Jan 19, 2008

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

ADVERTISEMENT

Getting The Entire HTML Source Code From A Web Page

Feb 12, 2010

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
Do
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do
Else

View 9 Replies View Related

Download Html Source From The Web

Jun 19, 2008

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 9 Replies View Related

How To Save HTML Source Code From VBA

Sep 22, 2008

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 9 Replies View Related

Save Html Source As Text

Feb 5, 2008

I would like to be able to navigate to a site and save the source text of the html into a text file.

View 3 Replies View Related

Search HTML Source Code For Text

Oct 21, 2009

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.

View 9 Replies View Related

Getting Clipboard Data (HTML Web Source) To Paste Into Specified Cell

Feb 7, 2013

Goal: I have data that was copied to my clipboard from the webpage source in a Chrome browser. I would like to get that data over to my excel worksheet and insert it starting at "A1".

Issue: All of the pasted data is ending up in ONLY cell "A1" when using VBA.

When I just click in cell "A1" and CTRL-V, the data gets spread across a lot of cells, which is what I am after.

Code:
'------------------------------------------
'Start The Process
'------------------------------------------

' Assigning clipboard data to string variable strClip

Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard

[Code] .....

View 4 Replies View Related

Drop Down Value From A HTML Page

Apr 1, 2009

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 9 Replies View Related

Excel 2010 :: Importing Web Page HTML Elements Text Using VBA

Mar 9, 2013

Getting some web page data into Excel 2010 using VBA. My scenario however is set up with the following titles in cell A1, B1, C1, D1 and E1 : POST CODE, OUTLET, ADDRESS, TELEPHONE, EMAIL

The result I want to achieve is I enter a post code into cell A2 for example, Excel then uses IE to navigate to the relevant web page as defined in the VBA code. I then want the following to happen:

The InnerText of the web page's h1 tag is then inserted into the OUTLET cell (B2)The first instance of the p tag is then inserted into the ADDRESS cell (C2)The second instance of the p tag is then inserted into the TELEPHONE cell (D2)The third instance of the p tag is then inserted into the EMAIL cell (E2)

All instances of the p tag are contained in a div element called div class="adBox_content" . There are also 5 other DIVs above that DIV in the hierarchy.

Using the YouTube tutorial link, the method has worked for me using the getElementsByTagName("h1").innerText

However, when I try adding a second getElementsByTagName("p")(01).innerText the whole thing fails.

So I'm left with two problems; I can't make the VBA get more than one element at a time from the page, I can only either have the h1 or the first instance of the p tag. I've tried all the getElementBy methods and none of them seem to work in getting the second and third instances to show.

I also need the code to make the data be put on the same row ONLY as where the post code was entered. In this scenario for example of entering a post code into A2, the OUTLET needs to land in cell B2 only, ADDRESS C3 only etc.

By following the youtube tutorial above by giving the cells names to refer to in the code, the data ends up being inputted in all further rows with identical cell names. I need it to not do that.

The code is needed for around 300 rows of post codes that will be entered and refreshed every week or so.

View 9 Replies View Related

Excel 2010 :: VBA Code To Extract HTML Source Code Not Working On Google Sites

Dec 6, 2012

I have previously used the following code to successfully pull out IE webpage source code for string manipulation.

Its a crude example to demonstrate the principle:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

[Code] ......

However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"

The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......

The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.

Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?

Using :
MS Excel 2010
IE Explorer 8.0

View 1 Replies View Related

Navigate In Ie Using Source Code In Web Page

Oct 13, 2006

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
DoEvents
Loop

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 9 Replies View Related

Page Break Preview It Shows The Page Numbers In The Centre Of The Page

Jun 9, 2009

When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.

View 3 Replies View Related

Validation Source: Change The List Source

Apr 1, 2009

Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?

View 5 Replies View Related

Put In Error Handling In Case The Format Of The Page Changes Or The Internet Page Is Unavailable

May 18, 2006

I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.

Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................

View 6 Replies View Related

Can't Move Page Breaks In Page Break Preview

Mar 26, 2006

I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?

View 7 Replies View Related

Print To Fit All Rows And Column On One Page And Take Up Entire Page?

Oct 16, 2013

I have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.

View 4 Replies View Related

Printing One Page Grayscale And One Page Color With Code

May 21, 2009

I have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.

View 2 Replies View Related

Countif Formula: Take Data From One Page And Graph It On Another Page

May 1, 2009

I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:

A B

1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01

I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.

View 3 Replies View Related

Change Start Page Of Multi Page UserForm

Jul 23, 2006

Is it possible to Change Start Page Of Multi Page UserForm Based on 8 CommandButtons. on a WorkSheet, for example.

CommandButton1
UserForm1.MultiPage1.Value = 0
CommandButton2
UserForm1.MultiPage1.Value = 1
CommandButton3
UserForm1.MultiPage1.Value = 2
CommandButton4
UserForm1.MultiPage1.Value = 3

Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?

View 2 Replies View Related

Transfer Specific Data On Each Page To A Different Colum On That Same Page

Mar 15, 2007

I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.

View 2 Replies View Related

Remove Page Numbers In Page Break Preview

May 18, 2007

how to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?

View 3 Replies View Related

Macro To Print Page Based On Active Page

Jun 29, 2007

I need a macro that will print a hidden page that is linked to the page I have active.

It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.

View 3 Replies View Related

Pivot Table Retains Old Source Data In Addition To New Source Data

Sep 7, 2006

I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

Does anyone know how to purge the old data from the internal Pivot Table memory?

I hope this is enough information....let me know if you need more.

Thanks in advance for any help,

Jon

View 9 Replies View Related

Wondering How To Keep Headers Repeating From Page To Page.

Nov 4, 2008

Working with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.

For example I have a header that may say something like:
People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.

View 5 Replies View Related

Formula On A Page Updated When Any Value On The Page Is Changed

Mar 27, 2009

Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?

For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?

View 3 Replies View Related

Copying 12 Months Data Across The Page To Down The Page

Oct 17, 2008

I have a dataset that is as follows:

Col1 2 3 4 ...... 45

123 456 xxx xxx xxx
100 234 xxx xxx xxx
221 543 xxx xxx xxx
112 234
112 689
122 956
122 234

This goes on from left to right for 45 odd columns.

I need to copy each set i.e set 1 is 123 down to 122 to say cell a25 and then copy the next set i.e. 456 to 234 below the first set.

I need to somehow write a loop code that knows where to get the 2nd, then 3rd set, etc and copy it to the bottom of the preceeding set

View 9 Replies View Related

Fill In Page From Another Page In Workbook

Dec 26, 2013

What I want to do, is to populate the Lois, Beth, Kelly and Shelley pages with the rows from the Que page when they get assigned to one of them. I tried to program then off of a different example in the forum. It didn't work. Will include the copy.

Tracking(HELP).xlsx

View 6 Replies View Related

Showing Current Page Number And Total Page Number In Entire Workbook?

Nov 14, 2013

In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".

I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.

View 6 Replies View Related

Paste HTML

Jan 20, 2010

I've recently switched from office 2003 to office 2007.

In excel 2003 I was able to paste html into excel for easy editing. In excel 2007, when I paste the html I see auto-formatted web page objects. How can I disable this and get just straight text?

I tried formatting the cells to be plain text and then pasting.

View 14 Replies View Related

HTML Scraping Using VBA

Jan 16, 2009

How can I using VBA do HTML scraping? A site which I have to use to update some data on a spreadsheet does not offer any web services. I have to copy data from the spreadsheet one cell at a time, paste into the search field and look at the results and decide what kind of modification is needed for that piece of data.

If VBA can do HTML scraping here is what I thought I could do. For each of the data in the cell, I need to go to a search field on a website, manually enter the values and hit search. Then the browser generates a url like "http://www.mysite.com/search?hl=en&q=SNX018923" and returns the result as an HTML page. How can I use vba so that I can automate this process of generating the url and load the returned html text into a variable. Then I can use the script to search for a pattern of text and do something to the active cell from where the data was taken. *nix has a curl command. What is the equivalent of curl in VBA? How do I use it to get the result I am trying to acheive?

View 2 Replies View Related

IE & VBA - HTML Found Is Not What I See

Feb 13, 2010

Trying to automate a job. I'm using this
Public Sub Test()
'Initiate IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

View 9 Replies View Related

HTML On UserForms

Dec 14, 2006

is there any way to accept the data directly on the userform(excel)? it may on some html control on userform or any other possible way to deal with.

View 2 Replies View Related

How To Extract HTML Elements

Jul 22, 2014

I have a few hundred of these divs, that I need sort and filter.

I would like to remove ALL HTML and be only left with the following:

Example Name = KeithEmail = keith@example.comStore = Store 1 (This only has 2 options. Store 1, Store 2)

All of the below code is in an individual single cell, so there are about 400 cells

HTML Code: 

<div userid=""286"">
<div id=""694"">
<h1 style=""display:none"">Keith</h1>

[Code]....

View 1 Replies View Related

How To Outlook Email In HTML

Jun 26, 2014

Below I have my code that works great. What I would like to do is make it pull the .HTMLBody from a cell value instead of inbeded in the code so users can change, just like to I have it below for who I am sending it to. The issue is if I do it the same way I loose my signature and the font, size and color I have below. How can I do this.

range("P16") would be the 1st line of the mesage
range("P17") would be the 2nd line of the mesage
range("P18") would be the 3rd line of the mesage

All the sendkeys below copy a range and paste it below my message as a picture currently. That is fine.

[Code] .....

View 1 Replies View Related

VBA HTML Not Running On All Computers?

Jul 23, 2014

So I have this code I'm working on for my deptarment that goes to a website inputs data , clicks run and downloads the csv file to the worksheet. It works just fine on my PC and on my profile on the computers the other department uses. We are both using same versions of windows, excel , and IE. When i have someone from the other department run the macro it opens the website but never enters the data into the fields despite the site being the exact same coding as when i'm logged in.

[Code].....

When this code is ran by a member of the other department it just opens the website inputs nothing and doesn't press the RUN button on the website.

What setting or anything. I verified that both PC's VBA references in are there and no "Locations are missing paths" .

View 1 Replies View Related

Delete HTML Tags

Oct 30, 2007

I have lots of text with HTML tags attached, I would like to delete the tags and be left with only the text. Is there an easy way to do this?

eg.....

View 14 Replies View Related

Run Macro In HTML File

Aug 10, 2009

I have wriiten a macro in excel that pops up some photos whenever the respective cell is selected. It is working good except when I save the file in HTML format. Also what is the difference between publishing and saving html file? I am using office 2003 and microsoft web components installed.

View 3 Replies View Related

Macros To Create HTML

Nov 6, 2009

I have a couple spreadsheets which update using a RTD link. I then use Macros to sort it and publish a html file every 30 seconds.

I found the html macro thanks to : http://www.meadinkent.co.uk/xlhtmltable.htm

The problem is that whenever I run two sheets together, the macro that creates the html file at times gets the data off the wrong worksheet. Sometimes I only see one sheet's data being used, sometimes the other.

Both html files when published have some feature from one macro and some from the other macro. Like macro one sheet says use 1528 rows and the title for page is Relative Strength Over 750K. Other macro says use 979 rows and title for page is Relative Strength Under 750K. I would have both the sheets as like 1528 rows for with title Relative Strength Under 750K.

If I run one sheet at a time, everything works fine.

Here are the Macro codes for Sheet #1:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Macro1", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
End Sub

Module 1

Public dTime As Date

Sub Macro1()
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "Macro1"
Columns("P:AH").Select
Selection.Sort Key1:=Range("AG1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True........................................................

View 12 Replies View Related

Remove Html Markup

Nov 22, 2005

How do I remove HTML markup from and cell.

i.e.
<DIV class=productdesc> <H3>SCALLYWAGS CHANGING MAT. </H3><TABLE border=0>
<TBODY> <TR> <TD> <UL> <LI>Foam-filled <LI>Wipe-clean surface <LI>Fits most
dressers <LI>dimensions: 75 x 46cm </LI></UL></TD></TR>
<TR></TR></TBODY></TABLE> <UL> <LI>When using a changing mat on a dresser or
other raised surface, never leave your baby unattended even for a moment
</LI></UL></P></DIV>

I want it to read:-

SCALLYWAGS CHANGING MAT. Foam-filled Wipe-clean surface Fits most dressers
dimensions: 75 x 46cm When using a changing mat on a dresser or other
raised surface, never leave your baby unattended even for a moment

I am trying to change out website csv file into a froogle csv file but
without the HTML

View 14 Replies View Related

How To Read Tag From HTML In Excel

Apr 26, 2012

is it possible in Excel to read value from url every 5 mins.

this html url has a time which is updated every 5 mins, I want excel to read that time from that url every 7 or so mins. so after that I can write another code if time didn't change it will notifity me or do whatever I need to do after that.

View 2 Replies View Related

Convert Zero To Blank HTML

Mar 31, 2008

I have a html file that is pulled into excel. It has several values like list below. I need the zero value to be blank. Is there a quick macro that I can run to get them to change over to blanks? I tried formating but that didn't work.

A B C D E F
1 35,080 0 128 2,028 0 0
2 20,586 124,680 0 25 228 342
3 1,425 125,879 22 0 0 0
4 0 0 0 0 0 0

View 9 Replies View Related

ADVERTISEMENT