VBA Downloading Challenge into Excel

gasguy

New Member
Joined
Sep 18, 2006
Messages
3
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

gasguy

New Member
Joined
Sep 18, 2006
Messages
3
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

Well-known Member
Joined
Feb 22, 2006
Messages
609

ADVERTISEMENT

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

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
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



Code:
    '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

Well-known Member
Joined
Jul 22, 2004
Messages
503
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
 

Forum statistics

Threads
1,136,655
Messages
5,677,017
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top