VBA Downloading Challenge into Excel

[gasguy]

Hello,
I have been frustrated by the website shown in the some of the VBA code below. What I attemtoping to do is to navigate successfully to the export window, choose the option MS Excel 97-2000 (Data Only) and then export the data.

Ideally, I would like to be to get to the export point and then export the file to download loaction on my hard drive without having to confirm to overwite or anything like that. Simply, export the data and save it.

Main problem seems to be the window which appears with the export options just can't be activated from VBA - can't seem to be able activate the handle for the window or anything.

Greatly appreciate any insight from the smarter people out there.
Basic VBA code is below
Thank you.


Sub Update()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://infopost.panhandleenergy.com/InfoPost/jsp/OperAvailByLoc.jsp?varDate_MM=08&varDate_DD=25&varDate_YY=2006&search=Retrieve"
Do Until .ReadyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop


.Navigate "javascript:doExport('exportdlg')"
'Do Until .ReadyState = 4: DoEvents: Loop
'Do While .Busy: DoEvents: Loop

end sub

 

[gasguy]

Forgot to mention that I am using Windows XP and Excel 2003.
Thx.

 

[tightwad]

the website you posted won't work because it needs cookies. What are you trying to get from www.panhandleenergy .com?

 

[gasguy]

I am essentially trying to get pipeline flow data for a natural gas supply and demand model. The data from the website is all freely available public information that is required to be posted by energy regulatros.

If you go to that website, go under Trunkline Gas Company, Capacity. Operationally Available by Location, and then from that screen I am trying to export the data via VBA. Can get there by navigating to the export button using VBA, but the window that appears, does not seem to want to cooperate in terms of any way to navigate around it using VBA.

I am not fond of using SendKeys, so this is my least favorite option.

If there is some way to get the correct format type chosen (MS Excel 97-2000 (Data Only)) from the list box in that window, and export without having to conform an overwite, this would be great. May be asking for the impossible depending on the limitations of the website.

Thanks.

 

[tightwad]

Hmmm, I see what you mean. There may be a way around it, but i think it is over my head.

 

[scifibum]

Here's part of a possible solution...

You need a reference to Microsoft Scripting Runtime to make this work. This library includes an object called "ShellWindows" which is a collection of all explorer windows including Internet Explorer windows (I think), so you can associate the new window with an object.

I tested this briefly and it seems to work up to the point of starting the download of the exported report.

If you want to skip the dialog where Windows asks you whether to open or save the downloaded file, you may need to look into using internet calls more directly in your code instead of automating IE. I haven't any experience doing this but check out this URL:

http://groups.google.com/groups?hl=en&lr=&q=vba http download file&sa=N&tab=wg

    'your code to launch export dialog here

    Dim sws As New ShellWindows
    Dim ie2 As InternetExplorer
    Dim opt As Variant
    Dim frm As Variant
    

    
    'wait 5 seconds for export dialog to load
    Application.Wait Now + TimeValue("00:00:05")
    
    'find IE window with export dialog
    For Each ie2 In sws
        If InStr(1, ie2.LocationURL, "exportdlg") > 0 Then
            'Window found, modify form and submit
            Set frm = ie2.document.forms(0)
            For Each opt In frm.exportformat
                If opt.Value = "MSExcel" Then opt.Selected = True
            Next opt
            frm.from.Value = 1
            frm.to.Value = 10
            'execute the built in function to submit form
            ie2.document.parentWindow.execScript ("checkValuesAndSubmit();")
        End If
    Next ie2

 

[scifibum]

I should add that the site appears to use a URL to produce the exported report so if you look into it thoroughly you might be able to figure out what URL will call the export directly instead of automating through their export form, and if you have the complete URL then saving the output to a file is relatively straightforward using winInet api:

http://vbnet.mvps.org/index.html?code/internet/urldownloadtofile.htm