CODEWITHSUNDEEP
excelvba
Jeanjean
Jeanjean

Reputation: 863

Open .csv file from internet using VBA

I am trying to open a VBA file from internet. I first tried to download and open it using this method successfully. However, I do not want the file to be stored on my hard drive. Thus, I tried an easier way by using the workbooks.open method :

Sub OpenInternetFile()

Workbooks.Open _
    Filename:="http://webstat.banque-france.fr/fr/downloadFile.do?id=5385698&exportType=csv", _
    Format:=4

End Sub

Problem, it does not look like the downloaded file (see picture 1) is in a .csv format; its datas cannot be converted properly. The picture 2 is the expected render. Do you have any idea how to fix this issue please?

Picture 1 : Problem

Picture 2 : Ok version

Upvotes: 0

Views: 504

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Please, try simple using OpenText instead of Open, for ";" as separator:

Dim  pathToFile As String
  pathToFile = "http://webstat.banque-france.fr/fr/downloadFile.do?id=5385698&exportType=csv"
  Workbooks.OpenText fileName:=pathToFile, origin:=xlWindows, StartRow:=1, _
                           DataType:=xlDelimited, Other:=True, OtherChar:=";"

If some columns format are not the one suitable for your localization, it is easy to use FieldInfo in order to set format for each column.

Upvotes: 2

Random User
Random User

Reputation: 341

It appears Excel wrongfully uses comma as a separator, instead of semicolon. I have tried modifying the format option, as suggested by BigBen, but unsuccessfuly.

You could try the following code though. Definitely not the most elegant way to do it, but it seems to work.

Sub ImportData()
    Dim Wkb As Workbook
    Dim Wks As Worksheet
    Dim MaxColumn As Long
    Dim MaxRow As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim TmpStr As String
    Dim TmpSplit() As String
    Dim TmpArr() As String
    
    
    Set Wkb = Workbooks.Open(Filename:="http://webstat.banque-france.fr/fr/downloadFile.do?id=5385698&exportType=csv") 
    Set Wks = Wkb.Sheets(1)
    With Wks
        MaxColumn = .UsedRange.Columns.Count 
        MaxRow = .UsedRange.Rows.Count 
        Application.Calculation = xlCalculationManual
    'The TmpArr array is used to temporarily store records, and dimensionned according to the data size
        ReDim TmpArr(1 To MaxRow, 1 To MaxColumn)
        'For each row in imported data
    For i = 1 To MaxRow
        'Concatenate each column, with a comma inbetween
            TmpStr = vbNullString
            TmpStr = .Cells(i, 1)
            For j = 2 To MaxColumn
                TmpStr = TmpStr & "," & .Cells(i, j)
            Next j
        'Next, split the concatenated string and store in the TmpSplit array, which holds each value for the current record
            TmpSplit = Split(TmpStr, ";")
            'The TmpSplit array is then used to fill the TmpArr, which contains each record
            For k = 0 To UBound(TmpSplit) - 1
                TmpArr(i, k + 1) = TmpSplit(k)
            Next k
        Next i

    'Finally, print the TmpArr in the current sheet. The range on which i print the record is dynamically dimensionned using the size of the TmpArr
        .UsedRange.Clear
        .Range("A1:" & .Cells(UBound(TmpArr, 1), UBound(TmpArr, 2)).Address) = TmpArr
        Application.Calculation = xlCalculationAutomatic
    End With
End Sub

Upvotes: 2

Related Questions