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

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

Regards

Derek +++

From: "Green 1z@compuserve.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
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".

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
Subject: 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
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

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 (7)

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