Import CSV from Web

[dlugirapfr]

Hi All,

I tried to find a solution on forum but I cannot.
I have CSV table which is located on internet: www.example.com/13423532534gfs?@#432

When I press on url I download CSV table. I cannot import CSV table from Web in Access so i decide to put link to it on Excel and I have a connection between Excel 365 and Access 365. I created macro to refresh table in Excel and save it from Access but sometimes it doesn't work.
Is there any quick way to download CSV file to specific localization like c:\base\test.csv from URL from web ? Then I will connect to it?

Thank you for replay.

 

[arnelgp]

see this link:

Downloading a file in VBA and storing it

I need to download a file that I got from a REST search. The URL is like the following: https://abc.def/geh/servlet/rest/vault?oid=xxx&expr=files.file1 (I needed to edit it due to privacy rea...

stackoverflow.com

 

[dlugirapfr]

Thank you for that link but I checked two solutions and it doesn't work.
My code looks like

Sub DownloadFile(url As String, filePath As String)

    Dim WinHttpReq As Object, attempts As Integer
    attempts = 3
    url = "https://mysecreturl.com/fodjpdfjps"
    filePath = "c:\baza\"
    On Error GoTo TryAgain
TryAgain:
    attempts = attempts - 1
    Err.Clear
    If attempts > 0 Then
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        WinHttpReq.Open "GET", url, False
        WinHttpReq.send

        If WinHttpReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write WinHttpReq.responseBody
            oStream.SaveToFile filePath, 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
        End If
    End If
End Sub

Maybe references are missed ?

 

[arnelgp]

it's a missed.
paste this in a Module:

Private Sub test()
Dim url As String, filepath As String
    url = "https://mysecreturl.com/fodjpdfjps"
    filepath = "c:\baza\baza.txt"

    If Len(Dir(filepath))>0 then Kill filepath
    Call DownloadFile(url, filepath)
End Sub

Sub DownloadFile(url As String, filepath As String)

    Dim WinHttpReq As Object, attempts As Integer
    attempts = 3
    On Error GoTo TryAgain
TryAgain:
    attempts = attempts - 1
    Err.Clear
    If attempts > 0 Then
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        WinHttpReq.Open "GET", url, False
        WinHttpReq.send

        If WinHttpReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write WinHttpReq.responseBody
            oStream.SaveToFile filepath, 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
        End If
    End If
End Sub

then click anywhere on Sub Test() and press F5

 

[dlugirapfr]

Thank you, now it works perfectly !
Greetings and have a nice day !

 

[Isaac]

sometimes it doesn't work

Can you be more descriptive?

 

[dlugirapfr]

Can you be more descriptive?

Ok it was not main topic but I will explain. Sometimes there is error with refreshing table in Excel direct from Access. Excel block file and I see Excel process in Task Manager. I don't why. But now it doesn't matter this solution works perfectly. It better to download and replace csv than put it to Excel and refresh it from Acces.