The problem is, I want to copy data from .csv file, but excel automatically separates it into columns by comma, I need to separate it by ";".Can I edit csv file using vba code to add 'sep=' at the beginning?

CodePudding user response：

Excel/VBA ignores the separator option if the file has the .csv extension. You have to rename it to set the delimiter. Check out my VBA CSV parser project.

CodePudding user response：

The solution worked for me is to use filesystem object to read csv file and copy it into temporary file with 'sep=' at the first line. Here is the code:

Function readCsvF(delim as String, fPath as String) As String
    Dim sourceFile As Object, objFSO as Object, newTempFile as Object, _
    line as String, newName as String
    Set objFSO = CreateObject("scripting.FileSystemObject")
    Set sourceFile = objFSO.OpenTextFile(fPath)
    newName = objFSO.GetParentFolderName(fPath) & "\tempCSVfile.csv"
    Set newTempFile = objFSO.CreateTextFile(newName, True)
    newTempFile.Writeline("sep=" & delim)
    While Not sourceFile.AtEndOfStream
        line = sourceFile.Readline
        newTempFile.Writeline (line)
    Wend
    sourceFile.Close
    newTempFile.Close
    readCsvF = newName
End Function

So what this function does is basically creating new file in which writes first line "sep='your specified delimiter'" and then copies data from original csv file line by line. This function takes two string parameters: delim is delimiter you want to use and fPath is path to csv file, - and returns a path to the new file, so you can open it as workbook and do whatever manipulation you want with it.

Hopefully this will help someone, I really struggled to find the solution, maybe there was any better way, idk.