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+++
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!
Tidak ada komentar:
Posting Komentar