Solved: VB control of IE Dialog box

[arkusM]

I have some code that opens a webpage input values and submits the form.
At this point the dialog box asking what you want to do with the file pops up.

Can I get VB to say "Download" and if so specify the path?
I have spent quite a bit of time searching online. No luck!
I understand that ok'ing a download may go againt basic security issues. And if so that is fine.

[Oorang]

[vba]Option Explicit

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Sub Example()
'From All API
'example by Matthew Gates (Puff0rz@hotmail.com)
DownloadFile _
"http://www.vbaexpress.com/forum/attachment.php?attachmentid=2305&d=1258660962", _
"C:\Test\TestDownload.zip"
End Sub

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function[/vba]

[arkusM]

Aaron,

Thank you for replying. I have seen code very similiar to this at some point today. However I'm probably missing something.

Can your code be tweaked to accept a file name that will not be known until you hit the submit button? I am attempting to download daily data that must be done each day (boo) and no option for ranges.

Sorry if it seems like you are spoon feeding me I am not looking for someone to do this for me. Looks like the learning curve steepened again! LOL

Thanks for your help.

EDIT:

The code is not refined but I wanted to get it fuctioning before I added bells and whistles. Feel free to go to the site it is public info.
[vba]
Public Sub LaunchtheWebGasDay()

Dim objIE As InternetExplorer
Set objIE = New InternetExplorer 'Create Internet Explorer Object

objIE.Navigate "http://www.transcanada.com/Customer_...ranscanada.htm" 'Navigate the URL

Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop 'Wait for page to load
objIE.Document.forms(1).elements(0).Checked = True '"Alberta"
'objIE.Document.forms(1).elements(1).Checked = True '"Metric"
'objIE.Document.forms(1).elements(2).Checked = True '"Mainline"
objIE.Document.forms(1).elements(3).Checked = True '"Imperial"
objIE.Document.forms(1).elements(4).Value = "20091021"
objIE.Document.forms(1).elements(6).Value = ".csv"
objIE.Document.forms(1).elements(7).Click 'Click submit
Set objIE = Nothing
End Sub
[/vba]
Possiably getting help here

[Oorang]

Well if you notice, the URL itself is pretty predictable. It's:
https://services.tcpl.ca/cor/public/gdsr/Gsdr[Alberta/Mainline][Metric/Imperial]yyyymmdd.html

Take special note that you cannot use imperial with Mainline. It might be easiest to just create your own interface and then build the url behind the scenes, so the only page you load is the one with the data. That seems like it would be fastest in terms of performance.

[arkusM]

Brilliant, I got so into looking for a way to accept the SaveAS last week that I failed to step back out of the box.

I will figure out how I want to build the URL and load it into the code you gave me and I will be good to go.

Thank you for your time, I appreciate it.
Cheers,

M

[VBA]
Option Explicit

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Sub Example()
'From All API
'example by Matthew Gates (Puff0rz@hotmail.com)
DownloadFile _
"https://services.tcpl.ca/cor/public/...al20091119.htm", _
"C:\Test\TestDownload.xls" 'This will have to change but fine for test.
End Sub

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function
[/VBA]

[jennyrmruk]

Oorang - Your code to download a URL is very helpful. I have set it up to download a CSV file, but I am getting return characters in the resulting text file, and the first and second lines are joined, and the last two lines. I think I can strip the return characters out, but is there a way to avoid getting them in the first place?

[Oorang]

Interesting. The download method shouldn't affect the file. Is it possible that the file is that way naturally?

[jennyrmruk]

Hi Oorang. When I download the CSV file manually it is fine. I want to get a way of downloading automatically each file for each day. My code looks like this:
[vba]
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub Main()

Dim DATEROW As Integer
Dim AREA1 As String
For DATEROW = 152 To 155

AREA1 = "Bm"

BM_INDO DATEROW, AREA1

Next DATEROW


End Sub

Sub BM_INDO(DATEROW, AREA1)
Dim DownloadDate As String
Dim URL As String
Dim FileName As String
On Error GoTo ErrorHandler

DownloadDate = Worksheets("Sheet2").Cells(DATEROW, 2).Value
' DownloadDate is in the format "2009-11-01"

'This generates the specified URL
'the link to the site is bmreports.com/bsp/additional/soapfunctions.php?output=CSV&dT= with http and www in front of bmreports and the line below from the +

URL = "this is the link to the site but I am not allowed to post it" + DownloadDate + "&zT=N&element=INDO&submit=Invoke"

FileName = "C:\BM\Nov2009\" + DownloadDate + ".txt"

URLDownloadToFile 0, URL, FileName, 0, 0

Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

[/vba]
The first part of the file should look like this:
HDR,INITIAL DEMAND OUTTURN
INDO,20091101,1,N,20091101003000,27880.000
INDO,20091101,2,N,20091101010000,27311.000
INDO,20091101,3,N,20091101013000,26312.000
INDO,20091101,4,N,20091101020000,25322.000
INDO,20091101,5,N,20091101023000,24480.000
INDO,20091101,6,N,20091101030000,24039.000
INDO,20091101,7,N,20091101033000,23363.000
INDO,20091101,8,N,20091101040000,22764.000
INDO,20091101,9,N,20091101043000,22405.000
But it looks like this:
HDR,INITIAL DEMAND OUTTURN
INDO,20091101,1,N,20091101003000,27880.000
INDO,20091101,2,N,20091101010000,27311.000
INDO,20091101,3,N,20091101013000,26312.000
INDO,20091101,4,N,20091101020000,25322.000
INDO,20091101,5,N,20091101023000,24480.000
INDO,20091101,6,N,20091101030000,24039.000
INDO,20091101,7,N,20091101033000,23363.000
INDO,20091101,8,N,20091101040000,22764.000
INDO,20091101,9,N,20091101043000,22405.000
INDO,20091101,10,N,20091101050000,22250.000
INDO,20091101,11,N,20091101053000,22316.000
INDO,20091101,12,N,20091101060000,22521.000
INDO,20091101,13,N,20091101063100,23138.000
INDO,20091101,14,N,20091101070000,23734.000
INDO,20091101,15,N,20091101073000,24222.000
INDO,20091101,16,N,20091101080000,24806.000
INDO,20091101,17,N,20091101083000,26547.000
INDO,20091101,18,N,20091101090000,28474.000
INDO,20091101,19,N,20091101093000,30631.000
INDO,20091101,20,N,20091101100000,32376.000
INDO,20091101,21,N,20091101103000,34017.000
INDO,20091101,22,N,20091101110000,35009.000
INDO,20091101,23,N,20091101113000,35906.000
INDO,20091101,24,N,20091101120000,36547.000
INDO,20091101,25,N,20091101123000,36925.000
INDO,20091101,26,N,20091101130000,36834.000
INDO,20091101,27,N,20091101133000,36296.000
INDO,20091101,28,N,20091101140000,35580.000

HA - when I paste it in here all the carriage return characters are interpreted and the data comes out the same! Will I have to open each txt file and rewrite it somehow without the carriage return characters?