Getting HTML Source into Excel
EdM
down the HTML source code from an URL into a String variable for later
parsing. Does anyone know the easiest way to go about this? I've tried
doing a Workbooks.Open("http://....") call, but it doesn't satisfy my needs.
I need to download the actual HTML source code text into a variable so I can
take it apart and find what I'm looking for.
I've seen this Internet_Assistant object, but I don't know how it's used or
if it can do what I'm looking for or where there is documentation for it.
Ed
arobinson
-A
EdM <Edwa...@home.com> wrote in message
news:nhVQ4.47532$fF5.1...@news1.rdc1.il.home.com...
Peltier
helpful little sub that reads an html doc line-by-line, and places these
lines into a column of the active worksheet, starting at the active
cell.
HTH,
- Jon
'---------------------------------------------
Sub inputHTMLfile()
' input html file line by line without interpreting html tabs
' open new sheet first and select home cell
Dim linein As String, fh As Integer, FileName As String, lineNum As
Double
FileName = Application.GetOpenFilename
Application.ScreenUpdating = False
lineNum = 0
fh = FreeFile
Workbooks.Add
Open FileName For Input As fh
Do Until EOF(fh)
Line Input #fh, linein
ActiveCell.Offset(lineNum, 0) = linein
lineNum = lineNum + 1
Loop
Application.ScreenUpdating = True
End Sub
'---------------------------------------------
arobinson
Web site.
-A
Peltier <pel...@home.com> wrote in message
news:3914BECC...@home.com...
Jake Marx
Hi Ed,
Another approach would be to automate Internet Explorer. Below is a
function I created that will return the HTML source for a given URL. You
could expand the function to look at certain frames, etc (look up the
Navigate method in MSDN Library for more options). You may want to add some
error handling as well.
Regards,
Jake Marx
Public Function sGetHTML(rsURL As String) As String
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate rsURL
Do Until Not .Busy
DoEvents
Loop
With .document
If Not (.url Like "res*") Then sGetHTML _
= .documentelement.innerhtml
End With
.Quit
End With
Set objIE = Nothing
End Function
EdM
Do you know the syntax to access form elements? I want to set a property of
an Select box in a form, submit the form, and get back the HTML which I
could get to with the code you posted.
Thanks,
Ed
Jake Marx <Jake...@americawest.com> wrote in message
news:uqkGJaQu$GA.268@cppssbbsa05...
Peltier
on one. I guess I've done it in two steps, saving locally using
Netscape or IE, then opening in Excel this way. I use it when I'm
writing my own web pages, which I do automatically using Excel VBA.
Your suggestion is better, and another person gave a macro that
automated IE.
I gave my two cents, and it was worth about that.
- Jon
_______
Jake Marx
I think that would be pretty difficult to do directly. There is an optional
PostData argument to the Navigate method, but I've never used it. It can be
used to Post data to a URL, much like an HTML form would do.
What I would suggest is to fill out the form manually and click Submit.
Once the results come up, look in the location bar for the URL - hopefully,
it will contain the parameters you entered. Use that URL instead of the
original one to get the desired HTML.
For example, let's say you wanted to get a basic stock quote for MSFT from
http://finance.yahoo.com/?u. Instead of going to that URL and trying to
input MSFT and click Submit, just use this URL
http://finance.yahoo.com/q?s=msft&d=v1, which I got from manually submitting
MSFT and copying the resulting URL from the location bar.
This method won't always work (pages that use ASP, JSP, etc. may not display
the resulting URL in the location bar). If you can't get the resulting URL
in your situation, post back to see if anyone has other ideas.
Regards,
Jake Marx
EdM <Edwa...@home.com> wrote in message
news:jD1S4.53758$fF5.1...@news1.rdc1.il.home.com...
John M. Broom
Hi Jake:
Like Ed, I am trying to read HTML into an Excel spreadsheet so that I can parse
out the information I want. I use the same approach in a Visual Basic program
and an Access database using the Internet transfer controls, but it seems that
you have to have a form to be able to drop the Internet transfer control on to.
So I tried your code snippet and I got the following error:
Run-time error '438': Object doesn't support this property or method
on this line:
sGetHTML = .documentelement.innerhtml
Am I not including the appropriate OCX file or something? Thanks in advance for
any help.
John Broom
David McRitchie
This is probably somewhat of a digression, but
If the HTML that you want to pull in is in fact stock information from
finance yahoo, it would make a lot more sense (to me) to just
pull in the spreadsheet form (.csv) file for the stocks you want
to track, put all your stocks into something like the following
specifically it would be what you get at the bottom of the table
you see in Yahoo for your selected stocks.
http://quote.yahoo.com/d/quotes.csv?s+MSFT+NE+YHOO&f=sl1d1t1c1ohgv&e=.csv
HTH, the above is not in my stocks.htm page but there is
similar stuff there.
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
David McRitchie
bottom of the table look for download spreadsheet
you can create your own shortcut. But then this was
to get a .CSV file so most in the thread probably weren't
looking for this specific application.
David McRitchie