Senin, 15 Desember 2014

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".

Lisa


-----Original Message-----
From: Derek Turner g4swy@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Mon, Dec 15, 2014 8:44 pm
S
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 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: Green <1z@compuserve.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)
----------------------------------
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

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

.

__,_._,___

Tidak ada komentar:

Posting Komentar