0

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

Jeanjean
  • 723
  • 2
  • 12
  • 22

2 Answers2

2

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
Random User
  • 341
  • 3
  • 6
  • Your code works like a charm. However, I do not understand how it works (I suppose coding is more than a hobby for you) and it bugs me to use such script if I am not comfortable with it... I tried to change system's default separator before opening the file but it does not work :( – Jeanjean Sep 09 '20 at 16:40
  • 1
    Glad it worked! I added some comments for some more clarity. – Random User Sep 09 '20 at 16:58
  • Thank you very much for your work. I used the "local variables" and "immediate" windows while running step by step the code and am pretty happy to see your comments matched with my understanding. I need to work more on arrays; I never used them and am surprised they work using a "string" variable. – Jeanjean Sep 09 '20 at 17:06
2

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.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27