-
Solved: VB control of IE Dialog box
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.
-
[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]
Cordially,
Aaron
Keep Our Board Clean! - Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
-
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
-
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.
Cordially,
Aaron
Keep Our Board Clean! - Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
-
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]
-
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?
-
Interesting. The download method shouldn't affect the file. Is it possible that the file is that way naturally?
Cordially,
Aaron
Keep Our Board Clean! - Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
-
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?
Last edited by jennyrmruk; 11-30-2009 at 03:11 PM.
Reason: want to add an example
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules