Re: [ExcelVBA] Interacting with Internet Explorer “Save As” dialog box using VBA

 

Dear All

Here is an actual example that I am working on in relation to an Android project using B4A :-

Option Explicit

'   Please note: In Excel's VBA (ALT+F11) you have to add Reference (Tools->References) to

'   Microsoft HTML Object Library

'   and Microsoft Internet Controls items.

Sub ScrapeSpaceWeather()

Const URL = "http://www.spaceweather.com"

Const READYSTATE_COMPLETE As Long = 4

Dim nRow As Long

Dim oBrowser As InternetExplorer, oBrowserDocument As HTMLDocument

Dim sSplitPageText() As String, sWebPageText As String

    Set oBrowser = New InternetExplorer

    With oBrowser

        .Visible = False 'True

        .navigate (URL)

        While Not .readyState = READYSTATE_COMPLETE

            DoEvents

        Wend

        Set oBrowserDocument = .document

'       sWebPageText = oBrowserDocument.ie9_body.outerHTML

        sWebPageText = oBrowserDocument.documentElement.outerText ' or  .innerText .innerHTML

        oBrowserDocument.Close

        .Quit

    End With

    sWebPageText = Replace(sWebPageText, QUOTE, "")

    sSplitPageText = Split(sWebPageText, vbCrLf) ' try LF here instead ?

    For nRow = 0 To UBound(sSplitPageText)

        If Len(sSplitPageText(nRow)) > 0 Then Debug.Print nRow, sSplitPageText(nRow)

        Select Case True

            Case InStr(sSplitPageText(nRow), "10.7 cm flux:")

                Cells(1, 1).Value = sSplitPageText(nRow)

            Case InStr(sSplitPageText(nRow), "Now: Kp=")

                Cells(2, 1).Value = sSplitPageText(nRow)

            Case InStr(sSplitPageText(nRow), "Sunspot number:")

                Cells(3, 1).Value = sSplitPageText(nRow)

            Case InStr(sSplitPageText(nRow), "Solar wind")

                If InStr(sSplitPageText(nRow + 1), "speed:") Then

                    Cells(4, 1).Value = "Solar wind" & sSplitPageText(nRow + 1) & sSplitPageText(nRow + 2)

                End If

            Case InStr(sSplitPageText(nRow), "X-ray Solar Flares")

                If InStr(sSplitPageText(nRow + 1), "6-hr max:") Then

                    Cells(5, 1).Value = "X-ray Solar Flares" & sSplitPageText(nRow + 1) & sSplitPageText(nRow + 2)

                End If

            Case Else

        End Select

    Next nRow

End Sub

Use the Immediate window to see what is in the webpage  text.

Note the Remmed out other possibilities for collecting sWebPageText. This is a very simple example where the web text is quite easy to parse. With more complicated websites you may have to parse the HTML text from oBrowserDocument.ie9_body.outerHTML and look for named divisions and keywords to extract the right text then remove all the HTML tags.

Sometimes you have to do the Split on vbLF or vbCR  instead of vbCRLF.

And watch out for the dots when looking at the code.

Regards

Derek Turner

England+++

---

From: "Green 1z@compuserve.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Sunday, 14 December 2014, 12:09
Subject: Re: [ExcelVBA] Interacting with Internet Explorer "Save As" dialog box using VBA

 

Hi,

There is an IE object that you can set a reference to... Also if you google "screen scraping vba excel" and "internetexplorer.application vba" I think you'll find lots of code examples.

HTH

Lisa

-----Original Message-----
From: mkorgie@southwind.net [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Sat, Dec 13, 2014 9:41 pm
Subject: [ExcelVBA] Interacting with Internet Explorer "Save As" dialog box using VBA

I'm automating a process where I export information (.CSV file) from a web site into excel using VBA.   Does anyone know how to interact with the Internet Explorer dialog box (screen shot below) so that I can save the file directly from VBA?   Thanks much!

__._,_.___

---

Posted by: Derek Turner <g4swy@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___