Communication between Excel and Internet Explorer
Witek Wojnarowicz
Does anybody know how to from a VBA code:
- open an URL in Internet Explorer,
- wait until the page is created,
- save opened page as a Text File,
- open next URL
Now I'm using
Set appIE = CreateObject("InternetExplorer.Application")
appIE.Application.Visible = True
to open Internet Explorer
Many SendKeys commands to
- input an URL,
- open page,
- save to a text file
- close IE
(I need to open up to 100 pages)
This solution sometimes crashed.
Any ideas will be appreciate.
Witek
Kyle Freeman
allows you to make HTTP connections to URLs--like having the ability to
browse within your application. I have used it, but there are some good
examples of it's use in VB at http://www.planet-source-code.com/.
Kyle Freeman
In article <aZI35.34264$DC.7...@news.tpnet.pl>,
Sent via Deja.com http://www.deja.com/
Before you buy.
Jake Marx
Here's an example that should get you started. It doesn't work real well
with active content (ASP, etc.), but it should work for most sites. If you
want the HTML source instead of the viewable text produced by the HTML, you
can change the InnerText property to InnerHTML.
Regards,
Jake Marx
Sub Demo()
Dim ie As Object
Dim nFile As Integer
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = False
.Silent = True
.Navigate "www.yahoo.com"
Do Until Not .Busy
DoEvents
Loop
nFile = FreeFile
Open "D:\yahoo.txt" For Output Shared As #nFile
Print #nFile, .Document.DocumentElement.InnerText
Close #nFile
.Quit
End With
Set ie = Nothing
End Sub
Witek Wojnarowicz <vi...@bbs.chip.pl> wrote in message
news:aZI35.34264$DC.7...@news.tpnet.pl...
Alyda Gilmore
Further to Jake's most excellent example, may I suggest that you set a
reference to Microsoft Internet Controls (Shdocvw.dll) and use 'Dim ie As
SHDocVw.InternetExplorer' to declare the ie object. The beauty of this
approach is that you have all the IntelliSense features of the VBA Editor at
your disposal, including auto list members, syntax checking, parameter info,
quick info, and code formatting.
---
Alyda
Jake Marx <Jak...@home.com> wrote in message
news:OEkOBzs2$GA.279@cppssbbsa05...
Jake Marx
execute faster than late binding. Another way to declare the object is:
Dim ie As InternetExplorer.Application
IMO, it's a little easier to read, and you shouldn't run into any naming
conflicts. There are a few situations where late binding is necessary: 1)
when working with a product that doesn't support early binding, such as
Active Server Pages, and 2) when it's possible that not all client machines
will have the necessary object library. In this case, it looks like the
routine is for personal use, so, as you suggested, early binding is the best
way to go.
Regards,
Jake Marx
Alyda Gilmore <five...@home.com> wrote in message
news:#hR8ciu2$GA.244@cppssbbsa05...
> Witek,
>
> Further to Jake's most excellent example, may I suggest that you set a
> reference to Microsoft Internet Controls (Shdocvw.dll) and use 'Dim ie As
> SHDocVw.InternetExplorer' to declare the ie object. The beauty of this
> approach is that you have all the IntelliSense features of the VBA Editor
at
> your disposal, including auto list members, syntax checking, parameter
info,
> quick info, and code formatting.
> ---
> Alyda
>
<snip>
Witek Wojnarowicz
Thanks for your help
It really works:
Print #nFile, .Document.Body.InnerText
Witek
PS. Let me know how to set reference to shdocvw.dll
Jake Marx wrote in message ...
Alyda Gilmore
In the VBA editor choose Tools, References and check Microsoft Internet
Controls. If you can't find it on the list, click the browse button and look
in C:\Windows\System for Shdocvw.dll. Then use 'Dim ie As
SHDocVw.InternetExplorer' to declare the ie object.
---
Alyda
Witek Wojnarowicz <vi...@bbs.chip.pl> wrote in message
news:fU045.38445$DC.8...@news.tpnet.pl...
Peter Jamieson
Your excellent code was timely as I am just learning to do this Excel/IE
communication!
The sticking point for me is I want the retrieved data(which is tabular) to
go into a sheet in an open workbook but cannot work out how to adapt your
code:
nFile = FreeFile
Open "D:\yahoo.txt" For Output Shared As #nFile
Print #nFile, .Document.DocumentElement.InnerText
Any help appreciated on how to retrieve tabular data into Excel and keep the
format.
Cheers, Peter J.
"Jake Marx" <Jak...@home.com> wrote in message
news:OEkOBzs2$GA.279@cppssbbsa05...
Jake Marx
I don't think my method of getting the text is the best way when you're
dealing with HTML tables. My routine is better for getting text in the form
of paragraphs, etc - good for searching for text within a web page or
something like that.
For your purposes, you may want to simply open the URL in Excel to see what
happens. For example, this will bring in a tax table from the IRS site:
Workbooks.Open "http://www.irs.gov/prod/ind_info/tax_tables/dta_010k.html"
In XL2000, this looks pretty good. In XL97, I think the results are
sometimes less than desirable.
So, you could open the URL, then copy the worksheet from that new workbook
to the target workbook (maybe values only if you don't want hyperlinks).
Just a thought.
Regards,
Jake Marx
Peter Jamieson <ldo...@bigpond.net.au> wrote in message
news:jjL45.2607$Sn2....@news-server.bigpond.net.au...
Peter Jamieson
G'day Jake,
Thanks for the suggestion!
I coded it and it works in Office97 but as you commented the result not
brilliant'
The data came through as a single column of text only without the tabular
format.
A fair bit of wangling of formulae gets it back to a more tractable form but
all a bit clunky.
I've avoided moving to XL2000 but may yet have to!
Just had a thought: is it possible to directly reference an open web page
from code as for example: IE.ActiveWebPage.Select
Cheers, Peter J.
Jake Marx wrote in message <#MkTgtS3$GA....@cppssbbsa02.microsoft.com>...