Re: Excel VBA to acess currently open web page (existing IE6 insta

Tech-Archive recommends: Fix windows errors by optimizing your registry

Tim,

I agree with you but when I tested with the google home page there is a significant difference between what you get for innerHTML and what I can get going to 'View Source' from the IE context menu. I tried to pull the text from the temporary internet files using a script and while that might work for Keith, I failed on a PC running a Vista system with IE8. I think that if he can share where the data is (in a table, in a text area or wherever) we might find a solution. It's just tough without the file but I understand completely that he can't and shouldn't share the file in a newsgroup.

Steve



"Tim Williams" <timjwilliams@xxxxxxxxx> wrote in message news:288d7d2c-dc36-4362-85d5-b3747c9ebb13@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

document.body.innerHTML

should do it. Without seeing the page you're trying to capture it's
hard to know what else to suggest.

Tim

On Jul 14, 3:30 pm, ker_01 <ke...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Thanks Steve and Tim- this definitely has me on the right track.

Turns out that the content is not within the innerHTML, and I've been
googling and checking MSDN for syntax to return all, but so far no luck. If
you happen to come back to this thread, I'd welcome any syntax hints on how
to return the full page source, which I can then parse with RegEx.

Many thanks,
Keith



"Steve Yandl" wrote:
> Keith,

> What I show below might get you close. The line
> strPartURL = "google"
> should be amended so the string on the right is some portion of the URL > for
> the page you will have open (this is to prevent problems if you happen > to
> have multiple web pages open at once). I used google for the test.

> Note that this delivers the inner html for the body of the document > which is
> not delivering as much as you get when you right click and use View > Source.
> If it doesn't deliver what you need, I suspect you can work with the > "All"
> collection and retrieve the specific text you're after. This at least > shows
> you how to capture content from already open IE windows.

> '-------------------------------------------

> Sub GetInnerHTML()

> Dim strHTML As String
> Dim strPartURL As String
> Dim strPageHTML As String

> strPartURL = "google"

> Set objShell = CreateObject("Shell.Application")
> Set objShellWindows = objShell.Windows

> If objShellWindows.Count = 0 Then
> Exit Sub
> End If

> For i = 0 To objShellWindows.Count - 1
> Set objIE = objShellWindows.Item(i)
> If InStr(objIE.LocationURL, strPartURL) Then
> Set rng = objIE.Document.Body.CreateTextRange
> strPageHTML = rng.htmlText
> End If
> Next i

> MsgBox strPageHTML

> Set objShellWindows = Nothing
> Set objShell = Nothing
> End Sub

> '------------------------------------------

> Steve Yandl

> "ker_01" <ke...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>news:DE8B8DAB-2991-4E41-8746-A0F4F13819F5@xxxxxxxxxxxxxxxx
> > Don- thank you for your generous offer. Unfortunately, the URL is on a
> > secure
> > site, and I have no way to give access to others; the good news is > > that I
> > can
> > right-click and I see that the page source contains the information I > > want
> > to
> > scrape with RegEx, so really any web page will do.

> > Here is a test case:
> > * Open IE
> > * Navigate towww.google.com(or an alternate address of your > > preference)
> > * Click View/Source - this will bring up the page source in notepad

> > The part I'm missing is how to get this source automatically from an
> > "already open" IE window into Excel as a string. With Google it would > > be
> > easy
> > to open the URL directly from Excel VBA, but my challenge is to > > capture
> > that
> > text string directly from an already open browser window.

> > I've been abusing code snippets from all sorts of sources (non-VBA > > sites,
> > etc) but haven't gotten anywhere. I expect it will be something like:

> > Dim IE As SHDocVw.InternetExplorer
> > Do
> > MsgBox IE.document '.body.innerHTML?
> > Loop

> > I appreciate your time and expertise, and any advice you might be able > > to
> > offer.

> > Thank you!
> > Keith

> > "Don Guillett" wrote:

> >> There may be a way but witout seeing all the info, it's hard to say. > >> Give
> >> urls and the desired. OR,
> >> If desired, send your file to my address below along with this msg
> >> and
> >> a clear explanation of what you want and before/after examples.

> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguille...@xxxxxxxxxxxxx
> >> "ker_01" <ke...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >>news:5842968F-B02C-4FCE-93F7-E55E50040607@xxxxxxxxxxxxxxxx
> >> > Thanks to Ron and ryguy for responses to an earlier thread- I asked > >> > my
> >> > question poorly and the thread died, so here goes another try.

> >> > I have a series of web pages that for the sake of convenience, I > >> > really
> >> > can't access programatically with Excel (I know it is probably
> >> > possible,
> >> > but
> >> > there are several pages of data entry to get to the target pages, > >> > and
> >> > it
> >> > isn't practical)

> >> > So, I'm perfectly willing to access the web page directly in IE6.
> >> > However,
> >> > I
> >> > want to scrape the target page's source and pull a bunch of data > >> > into
> >> > Excel.
> >> > This web page design prevents copy/paste, so my only way to scrape > >> > the
> >> > data
> >> > is from the page source (or hand-retype all the data, which is what > >> > I'm
> >> > trying to avoid).

> >> > I haven't found any information on how to get Excel to interact > >> > with an
> >> > existing IE6 instance and grab the page source on the fly (when a > >> > macro
> >> > runs). Can anyone point me in the right direction?

> >> > Also, I'm currently on IE6, but if a newer version of IE would make
> >> > this
> >> > easier to program, I'd consider upgrading.

> >> > Many thanks,
> >> > Keith- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Excel VBA to acess currently open web page (existing IE6 insta
    ... I agree with you but when I tested with the google home page there is a ... Dim strPartURL As String ... Set objShellWindows = objShell.Windows ... "already open" IE window into Excel as a string. ...
    (microsoft.public.excel.programming)
  • Re: Excel VBA to acess currently open web page (existing IE6 insta
    ... I used google for the test. ... Dim strHTML As String ... Set objShell = CreateObject ... "already open" IE window into Excel as a string. ...
    (microsoft.public.excel.programming)
  • Re: Need help with SeriesCollection Object Please !
    ... Well Peter your explanation of my questions make sense and are sound answers. ... You seem very experienced with Excel and VBA:) ... Function GetSourceSheet(sFmla As String, sWSname, sFile As String) As ... ran my code on certain other charts sometimes it would return ...
    (microsoft.public.excel.programming)
  • Re: Format Excel - Object required error
    ... tell excel to autofit the columns. ... Public Sub FormatXLReport(strPath As String, ... Dim blnExcelExists As Boolean ... Private Const SW_SHOWNORMAL = 1 ...
    (microsoft.public.access.modulesdaovba)
  • Re: XP and string paths
    ... > the "Lcase" function is suddenly no longer available to Excel? ... > that occurs because of Windows XP. ... > string functions that Excel uses has become ... > is the usual "missing reference" problem. ...
    (microsoft.public.excel.programming)