VBA Code to Download a .CSV File

[krishhi]

Hi there,

I have a problem, I need to download a .csv file daily from a website and save into a particular location with the current date.

For example let say, the Website url is:

http:\\www.somexwebsite.com\docs\testfile.csv

so I want to save this file "testfile.csv" in to : E:\current documents\testfile_todaydate.csv

Is there any chance to do this?

Thanks,

Waiting for your kind reply,

Krrish

 

[Sektor]

[COLOR="Blue"]Declare[/COLOR] [COLOR="Blue"]Function[/COLOR] URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
     [COLOR="Blue"]ByVal[/COLOR] pCaller [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], _
     [COLOR="Blue"]ByVal[/COLOR] szURL [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], _
     [COLOR="Blue"]ByVal[/COLOR] szFileName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], _
     [COLOR="Blue"]ByVal[/COLOR] dwReserved [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], _
     [COLOR="Blue"]ByVal[/COLOR] lpfnCB [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]


[COLOR="Blue"]Sub[/COLOR] DownloadFile(URL [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], TargetFile [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR])
    [COLOR="Blue"]If[/COLOR] URLDownloadToFile(0, URL, TargetFile, 0, 0) <> 0 [COLOR="Blue"]Then[/COLOR]
        MsgBox "Error occured.", vbCritical
    [COLOR="Blue"]Else[/COLOR]
        MsgBox "File has been downloaded!", vbInformation
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[krishhi]

[COLOR=blue]Declare[/COLOR] [COLOR=blue]Function[/COLOR] URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
     [COLOR=blue]ByVal[/COLOR] pCaller [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], _
     [COLOR=blue]ByVal[/COLOR] szURL [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], _
     [COLOR=blue]ByVal[/COLOR] szFileName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], _
     [COLOR=blue]ByVal[/COLOR] dwReserved [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], _
     [COLOR=blue]ByVal[/COLOR] lpfnCB [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
 
 
[COLOR=blue]Sub[/COLOR] DownloadFile(URL [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], TargetFile [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR])
    [COLOR=blue]If[/COLOR] URLDownloadToFile(0, URL, TargetFile, 0, 0) <> 0 [COLOR=blue]Then[/COLOR]
        MsgBox "Error occured.", vbCritical
    [COLOR=blue]Else[/COLOR]
        MsgBox "File has been downloaded!", vbInformation
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Hey,

Thank you very much for the quick and kind reply,

Could you please explain how to use this? I mean, when i placed in VBA editor and press play buttion, it doesn't show any thing?

Sorry, I forgot everything about VBA

 

[Sektor]

Sub DownloadFile(URL As String, TargetFile As String)
...

You pass Sub URL of file and full file path to save.

 

[brettdj]

You can also use XMLHTTP

Code sample below credited to Matty Vidas, I have adapted his google icon example to your sample website and filename below

Cheers

Dave

Option Explicit
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte

    'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False    'Open socket to get the website
    oXMLHTTP.Send    'send request

    'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop

    oResp = oXMLHTTP.responseBody    'Returns the results as a byte array

    'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF

    'Clear memory
    Set oXMLHTTP = Nothing
End Function

Sub TestingTheCode()
'This will save the Google logo to your hard drive, insert it into the
' active spreadsheet, then delete the local file
'SaveWebFile "http://www.google.com/intl/en/images/logo.gif", "C:\GoogleLogo.gif"
'ActiveSheet.Pictures.Insert "C:\GoogleLogo.gif"
'Kill "C:\GoogleLogo.gif"
    SaveWebFile "http:\\www.somexwebsite.com\docs\testfile.csv", "E:\current documents\testfile_todaydate.csv"
End Sub

 

[krishhi]

You can also use XMLHTTP

Code sample below credited to Matty Vidas, I have adapted his google icon example to your sample website and filename below

Cheers

Dave

Option Explicit
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
 
    'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False    'Open socket to get the website
    oXMLHTTP.Send    'send request
 
    'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop
 
    oResp = oXMLHTTP.responseBody    'Returns the results as a byte array
 
    'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
 
    'Clear memory
    Set oXMLHTTP = Nothing
End Function
 
Sub TestingTheCode()
'This will save the Google logo to your hard drive, insert it into the
' active spreadsheet, then delete the local file
'SaveWebFile "http://www.google.com/intl/en/images/logo.gif", "C:\GoogleLogo.gif"
'ActiveSheet.Pictures.Insert "C:\GoogleLogo.gif"
'Kill "C:\GoogleLogo.gif"
    SaveWebFile "http:\\www.somexwebsite.com\docs\testfile.csv", "E:\current documents\testfile_todaydate.csv"
End Sub

Thank you very much guys.

I forogot to mention. The Website requires User Authenication. So, can we pass this too.?