0

I have a CSV with data separated by a semicolon ";" and I would like to open it in Excel with VBA. In my case, this VBA code will be run by users from various locations so I would like to write a code that will not be sensitive to local settings and produce the same output everywhere.

I have tried several codes but it doesn't work. I do not understand why after reading the documentation.

  • Set wb = Workbooks.Open(str_Path, Format:=6, Delimiter:=";")
  • Set wb = Workbooks.Open(str_Path, Format:=4)
  • Workbooks.Open Filename:=str_Path, Format:=xlCSV, Delimiter:=";"
  • Workbooks.OpenText str_Path, Semicolon:=True, DataType:=xlDelimited
  • Workbooks.OpenText str_Path, Local:=True

With all of these instructions, the workbook is open but the data are not properly separated.

For example, here is a result, it seems that it separate the row over "," and not ";" because my number "1,2" has been splitted:

enter image description here

Thanks a lot for any ideas ! :-)

Have a great day, Max

Max
  • 73
  • 7
  • Solution 1 will work if the file is a `.txt` but not for a `.csv`, no idea why. Edit: All solutions work for a `.txt`, none do for a `.csv`. – Victor Colomb Jul 27 '21 at 13:15
  • You should use an ADOStream. Here you can see how it works https://stackoverflow.com/questions/67888283/problems-reading-a-whole-line-from-textfile/67889275#67889275 And here you can look for other charsets if needed https://learn.microsoft.com/en-us/previous-versions/exchange-server/exchange-10/ms526296(v=exchg.10) – Zwenn Jul 27 '21 at 13:51
  • [Related](https://stackoverflow.com/questions/39890471/import-semicolon-separated-csv-file-using-vba) - see the edit. – BigBen Jul 27 '21 at 14:00
  • Record a macro while importing using the legacy import wizard. (Or use Power Query). – Ron Rosenfeld Jul 27 '21 at 18:52
  • I don't want to import I just need to open a CSV to modify it. I am already using power query when it comes to read only purpose. – Max Jul 28 '21 at 07:20

1 Answers1

0

Import function to specify your delimiter

Updated with worksheet assignment

Function ImportCSVFile(FilePath As String, Optional Sheet As Worksheet, Optional Delimiter As String = ",")
    ' Requires Reference: Microsoft Scripting Runtime
    Dim RowNumber As Long: RowNumber = 0
    Dim Arr() As String

    Dim oFSO As New FileSystemObject
    Dim oFS As Variant: Set oFS = oFSO.OpenTextFile(FilePath)
    
    If Sheet Is Nothing Then Set Sheet = ActiveSheet
    With Sheet
        Do Until oFS.AtEndOfStream
            RowNumber = RowNumber + 1
            Arr = Split(oFS.ReadLine, Delimiter)
            .Range(.Cells(RowNumber, LBound(Arr) + 1), .Cells(RowNumber, UBound(Arr) + 1)) = Arr
        Loop
    End With
End Function
Tragamor
  • 3,594
  • 3
  • 15
  • 32