arkusM
11-23-2009, 06:57 AM
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
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/gdsr/GdsrAlbertaImperial20091119.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
jennyrmruk
11-30-2009, 10:00 AM
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:
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
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.