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


Advertisements:










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

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
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
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 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 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
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
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 Replies!   View Related
Page Break Preview It Shows The Page Numbers In The Centre Of The Page
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 Replies!   View Related
Validation Source: Change The List Source
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 Replies!   View Related
Pivot Table Retains Old Source Data In Addition To New Source Data
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 Replies!   View Related
Put In Error Handling In Case The Format Of The Page Changes Or The Internet Page Is Unavailable
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 Replies!   View Related
Macro To Print Page Based On Active Page
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 Replies!   View Related
Printing One Page Grayscale And One Page Color With Code
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 Replies!   View Related
Countif Formula: Take Data From One Page And Graph It On Another Page
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 Replies!   View Related
Remove Page Numbers In Page Break Preview
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 Replies!   View Related
Transfer Specific Data On Each Page To A Different Colum On That Same Page
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 Replies!   View Related
Change Start Page Of Multi Page UserForm
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 Replies!   View Related
Copying 12 Months Data Across The Page To Down The Page
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 Replies!   View Related
Wondering How To Keep Headers Repeating From Page To Page.
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 Replies!   View Related
Formula On A Page Updated When Any Value On The Page Is Changed
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 Replies!   View Related
Paste HTML
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 Replies!   View Related
IE & VBA - HTML Found Is Not What I See
Trying to automate a job. I'm using this
Public Sub Test()
'Initiate IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

View Replies!   View Related
HTML Scraping Using VBA
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 Replies!   View Related
HTML On UserForms
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 Replies!   View Related
Remove Html Markup
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 Replies!   View Related
Extracting Sheet To Html
I have over 40 workbooks that have a summary page for the products contained within each. I would like to extract the information on the summary page to an html format with all the html tags so that I may post the information within a sharepoint webpart.

I have the publication the sharepoint down, but the extraction to html is my issue.

The information is contained within a 2 column by 15 row section give or take a row depending on the workbook. I am not to worried about the format of the cells or sheet, just the content.

View Replies!   View Related
Convert Zero To Blank HTML
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 Replies!   View Related
Macros To Create HTML
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 Replies!   View Related
Delete HTML Tags
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 Replies!   View Related
Add Line Breaks From HTML
If I copy for example from here ....

View Replies!   View Related
Run Macro In HTML File
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 Replies!   View Related
Convert Macros To Html
I need to convert my spreadsheet to HTML for my website and purchased SpreadSheet Converter. Since my spreadsheet results need to be SORTED and I used a macro to do this, I discovered this program will not work for me (as the sort function is not transferred and macros don't function.) Is there a program that will convert Excel to HTML AND allow macros to work (or at least the Sort function?)

My spreadsheet program requires input of price data into various cells; then on another page the results are generated and are sorted in Excel. The data only makes sense if it is sorted. Without the Sort, the price data is mixed up and not useful. Thus the web based spreadsheet will not work properly.

View Replies!   View Related
Hide Tabs In HTML View
Does anyone know of a way with VBA to hide the tabs when saving excel as a web page?

View Replies!   View Related
Inserting HTML Around Cell Data
I'm using excel 2003 and was wondering if I could insert html around the cell data in a faster way than cutting and pasting it. Here is an example of a column and what I would like to do.

aaaa
bbbb
cccc
dddd

Now I would like every cell to have HTML around it to be easily posted on a website. I would like the cells to now look like this.

<a href="www.url.com/aaaa"><img src="www.url.com/aaaa.jpg"></a>

<a href="www.url.com/bbbb"><img src="www.url.com/bbbb.jpg"></a>

<a href="www.url.com/cccc"><img src="www.url.com/cccc.jpg"></a>

<a href="www.url.com/dddd"><img src="www.url.com/dddd.jpg"></a>

I haven't dealt much with functions so I don't even know if this is possible.

View Replies!   View Related
VBA Bypassing The Save As When Creating A Html
I have modified the export a range to HTML code from Mr Walkenbachs excellent book and it all works well (still learning my trade with vba!!). The only issue I have is when the code a save as dialogue box appears. As I am looking to automate this process I was hoping to get this code to automatically save preferably to a path ("c:dailyrange.htm" for eg). I have tried various permutations but am really struggling with the concept.

I am using excel 2003.

The code

Sub ExportToHTML()
' Dim ws As Worksheet
Dim Filename As Variant
Dim TDOpenTag As String, TDCloseTag As String
Dim CellContents As String
Dim Rng As Range
Dim r As Long, c As Integer

'Create 7 htmls one for each column of the specified range
For Column = 1 To 7
Range(Cells(14, Column), Cells(40, Column)).Select
' Use the selected range of cells
Set Rng = Application.Intersect(ActiveSheet.UsedRange, Selection)

View Replies!   View Related
To Extract Data From HTML Header
I have managed to open an HTML file from IE and start importing some data from the HTML source to an excel table.

The problem is that I need to get access to some information in a function that is in the HTML header and I do not know the syntax to use.

I am defining the HTML document using ".Document.body.innerHTML". But I need to find the syntax that is equivalent to ".Document.head.innerHTML".

With HTMLdoc
.Visible = True
.Navigate PageString
Do Until .ReadyState = 4: DoEvents: Loop
End With

View Replies!   View Related
Write A Line Into A HTML File With VBA
Say there is a HTML file at C:DataFile.html

I'd like to use Excel VBA to write this meta tag into that HTML file:

****** http-equiv="refresh" content="600">
That's to make it refresh every 600 seconds.

The most elegant way would be to locate the "head" tag and write it right after it, but the refreshing meta tag seems to work where ever it is, even at the very end of the file after the "/html" tag! So locating the "head" tag is not that important, if it's difficult.

So, how do I use VBA to write lines into a HTML (or any text) file?

View Replies!   View Related
Pull URL From HTML In Text File
I have some code that loops through a bunch of text files, finding any that contain an href, and printing that entire line (if found) into excel. These text files are source code for a website. What I need to do, is within this line being pasted, is grab only a few things from within some tags such as the info between <title>This is the title</title> the tags and print it into a colum, I do not want the entire line, just certain things that are in the line. I have supplied the code that I currently have. I have it so that 'WholeLine' contains the entire line. Can I manipulate that with something like Cells (myR, 3).Value = WholeFile(?).

Sub CheckTextFilesForHREFs()
MsgBox "Press OK to begin report"
Dim WholeLine As String
Dim myPath As String
Dim workfile As String
Dim myR As Long

View Replies!   View Related
HTML Exporting As Text, Needs To Be Currency
I'm using Microsoft Office 2003 and have tried everything I can think of to strip the formatting from data I exported into Excel from the internet. I've tried DATA / TEXT TO COLUMNS, Formatting, LEFT, RIGHT, exporting to NotePad and back again... nothing works?

View Replies!   View Related
Export To HTML Filename And Filepath
I need a macro to export a range of cells to .html. The Save as Web Page works fine for me except a few thing that I need to automate. The macro I use now looks like this (recorded):

View Replies!   View Related
Auto Refresh Published HTM HTML
I have a worksheet which holds data and formula (obviously) and publishes only the data in a htm file to be viewed by others using a browser (IE). I want the htm file to auto refresh for the viewer.

View Replies!   View Related
Updating Html Forms With Textboxes
Is it possible to populate textboxes within excel then submit them to a HTML page containing forms? I know it sounds long winded but users are currently duplicating their work by having to put times in an excel spreadsheet (textboxes) and then use forms on an intranet page for another dept to see. I was thinking that if possible it would be better to do it all from excel.

View Replies!   View Related
Update Sheet From Html Form
I am doing a project to maintain employees details with Excel as database and HTML/JSP as frontend.

how to start about maintaining EXCEL as Database.

View Replies!   View Related
Macro To Save As MHTML Not HTML
I have excel 2003 and I have a macro that sorts data and then saves it as an HMTL page. When I was upgraded to excel 2003, it started saving the sheet as 'mhtml', which is causing me other problems. using:

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:Documents and Settings holg1My DocumentsQCMA events2007 est.htm" _
, "Event", saverange, xlHtmlStatic, "total_points_2007_8160", "")
.Publish (True)
.AutoRepublish = False
End With

with a defined document name (test.htm), it works (saves as html doc). using:

eventname = "jan22"

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
eventname, "Event", _
saverange, xlHtmlStatic, "total_points_24036", "")
.Publish (True)
.AutoRepublish = False
End With

with a variable document name (jan22), it saves as mhtml. How do I make it save as an HTML doc instead of an MHTML doc?

View Replies!   View Related
Code To Open Html File
I need vba code to open a HTML file when i click a button in microsoft excel.

View Replies!   View Related
Automate Import Of HTML Chart
I am trying to automate grabbing the chart on this website, using Excel VB. Here is the URL: http://www2.barchart.com/sectors.asp?base=industry

View Replies!   View Related
HTML Text Imported As Dates
I have 85 Html files that I open in excel. The files have a bunch of columns with numbers. Excel handles most of them properly, but if the number looks like a date, it is imported as a date (which it shouldn't be). For example if the number is 13-1, excel handles it fine, but if the number is 12-1, excel thinks it is a date and imports Dec-01. How do I get excel to import it as 12-1?

View Replies!   View Related
Dynamic Importing From Spreadsheet To HTML/php/asp..
We (the board members of an ultimate frisbee team) have an debt workbook that we would like to dispurse amongst our team members as easy as possible. The thing is that we'd like to limit what they are seeing without having to edit the xls-file, only certain columns etc. Ignorance is bliss so to speak.

So my thought was to have some sort of function that exports data on the fly from the excel book to a html / asp / php / xml (or likewise) page everytime a member would access that page. This would give our treasure (as if..) master a lot less work and make it much easier for our members to check their debt. The workbook itself has a couple of worksheets and the ideal would be for us not to split these into seperate workbooks. Is this doable? I read about web queries, but they seem to be directed the other way, from html to xls.

View Replies!   View Related
Parse HTML Code, Create A Table
I have a function that locates a table on a webpage and pulls the html code into one cell in a worksheet. Basically we can call this one cell a text file. I need to parse through this text file (cell A1) to create a table. This text file only has info for 1 table, the table always has 12 columns, the rows are variable. I would like it to then write back this parsed text file back into excel as a table, say starting in cell A2 on the same worksheet.

View Replies!   View Related
Find And Return Text From The HTML Code
I am trying to log a specific portion of code from a webpage. The line of code looks like this:

View Details

I need to extract the userid protion, the part between "=" and "'target...." and then

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved