Import CSV from Web (1 Viewer)

dlugirapfr

Registered User.
Local time
Today, 09:25
Joined
Nov 22, 2012
Messages
68
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

error reading drive A:
Local time
Today, 16:25
Joined
May 7, 2009
Messages
11,148
see this link:
 

dlugirapfr

Registered User.
Local time
Today, 09:25
Joined
Nov 22, 2012
Messages
68
Thank you for that link but I checked two solutions and it doesn't work.
My code looks like
Code:
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

error reading drive A:
Local time
Today, 16:25
Joined
May 7, 2009
Messages
11,148
it's a missed.
paste this in a Module:
Code:
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

Registered User.
Local time
Today, 09:25
Joined
Nov 22, 2012
Messages
68
Thank you, now it works perfectly ! :) 😎
Greetings and have a nice day !
 

dlugirapfr

Registered User.
Local time
Today, 09:25
Joined
Nov 22, 2012
Messages
68
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.
 

Users who are viewing this thread

Top Bottom