Solved: Firefox Questions

[stanl]

I've read several threads that suggested VBA can work within the Firefox browser, and it appears Firefox Extensions are XML files. Prior to making it my default browser and running the bleeding edge I just wonder if anyone has experience with Excel/VBA and Firefox?

I don't mean to make this a totally lame post. I have several vibrant and active Excel workbooks that parse data from IE Windows, but obviously need to be modified or abandoned if the user chooses Firefox.

TIA

Stan

[Zack Barresse]

Hi Stan,

I have not manipulated Firefox with VBA at all - admittedly I would like to be able to, just not sure how, so I will be watching this thread too.

But I wanted to let you know that I too use Firefox, but still use IE for any VBA automation. IE comes standard with Windows XP now and is still even required for Windows/Office updates. Just because Firefox is the default browser does not mean we lose any functionality of controlling it from VBA.

[mvidas]

I agree with Zack about subscribing for curiosity (I havent taken the time to study extensions and see how they work).

However, you can still make routines that parse websites without IE, there are a few different options available on how you can do it. Using IE for getting webdata causes delays and errors I don't get using other methods

[Ken Puls]

Oh! Sign me up!

[stanl]

However, you can still make routines that parse websites without IE, there are a few different options available on how you can do it. Using IE for getting webdata causes delays and errors I don't get using other methods

Yes, I have found that the freeware ActiveX controls from XStandard (for HTTP requests) and Chilkat (for XML parsing) are excellent. As a tangent - how much of the API overlaps CreateObject("InternetExplorer.Application") versus CreateObject("Shell.Explorer") - i.e. methods like navigate() or getelementsbytagname()?

Stan

[mvidas]

As a tangent - how much of the API overlaps CreateObject("InternetExplorer.Application") versus CreateObject("Shell.Explorer") - i.e. methods like navigate() or getelementsbytagname()?

They are very similar, you're right, and in all honesty I don't know how much overlaps between them. I'd guess quite a bit, as windows treats them pretty much the same too (put a web address into windows explorer, or a local directory into ie).

Yes, I have found that the freeware ActiveX controls from XStandard (for HTTP requests) and Chilkat (for XML parsing) are excellent.

True, but I don't generally use those I either use msxml2 or APIs:[vba]Function GetWebText(ByVal vWebSite As String) As String
'msxml2
Dim oXMLHTTP As Object, vWebText As String
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebSite, False
oXMLHTTP.Send
If (oXMLHTTP.readyState = 4) And (oXMLHTTP.Status = 200) Then
vWebText = oXMLHTTP.ResponseText
vWebText = Replace(vWebText, """, Chr(34))
vWebText = Replace(vWebText, "<", Chr(60))
vWebText = Replace(vWebText, ">", Chr(62))
vWebText = Replace(vWebText, "&", Chr(38))
vWebText = Replace(vWebText, " ", Chr(32))
For i = 1 To 255
vWebText = Replace(vWebText, "&#" & i & ";", Chr(i))
Next
End If
GetWebText = vWebText
Set oXMLHTTP = Nothing
End Function[/vba]That method there (msxml2) is my preferred method, as its relatively shorter (and you can really remove everything from the first "vWebText = Replace..." line through "Next" if you wanted to make it shorter), but the wininet API is usually fast and efficient:[vba]Option Explicit
Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_FLAG_RELOAD = &H80000000
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal _
sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal _
sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" _
(ByVal hOpen As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lLength _
As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal _
sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) _
As Long
Function OpenURL(ByVal sUrl As String) As String
Dim hOpen As Long, hOpenUrl As Long, lNumberOfBytesRead As Long
Dim bDoLoop As Boolean, bRet As Boolean
Dim sReadBuffer As String * 2048, sBuffer As String
hOpen = InternetOpen("VB Project", INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, _
vbNullString, 0)
hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)
bDoLoop = True
While bDoLoop
sReadBuffer = vbNullString
bRet = InternetReadFile(hOpenUrl, sReadBuffer, Len(sReadBuffer), lNumberOfBytesRead)
sBuffer = sBuffer & Left$(sReadBuffer, lNumberOfBytesRead)
If Not CBool(lNumberOfBytesRead) Then bDoLoop = False
Wend
If hOpenUrl <> 0 Then InternetCloseHandle (hOpenUrl)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
OpenURL = sBuffer
End Function[/vba]This is all unrelated to the main topic, but if I can convince one person to stop using IE then my day is complete

[stanl]

This is all unrelated to the main topic, but if I can convince one person to stop using IE then my day is complete

Prolly hard to be off-topic in a section for "Other"

I work for a nonprofit and create a lot of Excel-based apps for middle-high schools. For example, we got permission from EdTrust to create workbooks for Guidance counselors in poor counties (w/out broadband) - providing we display their logo and a link to the original data. The workbooks (I attached a sample for Duke, my daughter went there) consolidate 4,5,6 year graduation rates. I parse and create over 1,300 workbooks which are distributed on CD for free. Some of this is Excel Web query, some basic web-scraping involving IE. But, I would like to rely less, if not at all on IE - just don't want to throw the baby out with the bathwater.

Stan

[mvidas]

Good point about the 'other' forum
Interesting application, I'll take a closer look later on.. fwiw the above two routines are the equivalent of this (using IE)[vba]Function GetWebIE(ByVal vWebSite As String) As String
Dim IE As InternetExplorer
Set IE = CreateObject("internetexplorer.application")
IE.Navigate2 vWebSite
Do While IE.readyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop
GetWebIE = IE.Document.Body.InnerHTML 'could also be .InnerText
Set IE = Nothing
End Function[/vba]

[Killian]

The Gecko (the FireFox/Netscape engine) API is, AFAIK, only compatible with C++, but there are a couple of ActiveX Control Libraries for FireFox.
You'll need to download one, run the installer for the library and register the control (regsvr32 "C:\Program Files\Mozilla ActiveX Control v1.6\mozctlx.dll") then you should be able to use all the same methods and properties as with the IE object

[mvidas]

Neat!

I need FF at work

[stanl]

The Gecko (the FireFox/Netscape engine) API is, AFAIK, only compatible with C++, but there are a couple of ActiveX Control Libraries for FireFox.
You'll need to download one, run the installer for the library and register the control (regsvr32 "C:\Program Files\Mozilla ActiveX Control v1.6\mozctlx.dll") then you should be able to use all the same methods and properties as with the IE object

Thanx!! I have a spare PC and will make FF the default, then convert a basic IE script - login to site, enter User/PW, then navigate and grab data from selected pages - only use the Mozilla control.

This is a great forum

Stan

P.S. - I will mark this as Solved, but would really appreciate continued comments on the general subject.

[mvidas]

Stan,

Be sure to let us know what comes of it!

As for some more information, if you're going to be logging in somewhere to get the data you can still use msxml2. See an example here: http://www.experts-exchange.com/Q_21608520.html which is a question I asked at EE. The question there really dealt with ASP/vbscript, but take the word "Server" out of it and it works the same. I use it daily, works well for what I need, and I can login to a site and parse data from it without involving a browser Just an idea, though I know msxml3 and 4 are out now (at least I believe they are)

[stanl]

Mvidas;

I use msxml2, it is just more memory intense than Xstandard. Another application I wrote for middle school parses 7 days worth of hurricane maps into Excel. [attached is a snapshot of a slide from a ppt I presented this week]... Using msxml2 you still have to open an ADODB.Stream whereas XStandard requires a single line of code

oHTTP.SaveResponseToFile cFile

I used Xstandard to create zoomed mapquest images for 5,900+ NC towns and it outperformed msxml2. Again, a bit off the subject. Because I don't desire macro code in any workbooks I use .wsc files to create them.