Senin, 15 Desember 2014

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


Oops you have to add this line :-

Const QUOTE = """"


Derek +++

From: "Green [ExcelVBA]" <>
Sent: Monday, 15 December 2014, 22:01
Subject: Re: [ExcelVBA] Interacting with Internet Explorer "Save As" dialog box using VBA

Nice example Derek!

I'd personally probably put more comments in but it does point out something else IMHO... that it's good to know something about the page you're "scraping".


-----Original Message-----
From: Derek Turner [ExcelVBA] <>
To: ExcelVBA <>
Sent: Mon, Dec 15, 2014 8:44 pm
ubject: 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 QUOTE = """"
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
        Set oBrowserDocument = .document
'       sWebPageText = oBrowserDocument.ie9_body.outerHTML
        sWebPageText = oBrowserDocument.documentElement.outerText ' or  .innerText .innerHTML
    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.


Derek Turner

From: "Green [ExcelVBA]" <>
Sent: Sunday, 14 December 2014, 12:09
Subject: Re: [ExcelVBA] Interacting with Internet Explorer "Save As" dialog box using VBA


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.


-----Original Message-----
From: [ExcelVBA] <>
To: ExcelVBA <>
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 <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar