Macro to save a CSV with a tilde as delimiter

[David Montoya]

Gentlemen,

Using a VBA macro, is there a way to save a CSV file a tilde delimiter instead of comma?

Thank you

 

[DanteAmor]

You can change the configuration temporarily to save these files.

Enter control panel

Press Additional settings.

Change List separator by tilde

 

[David Montoya]

Dante,

I need to do it at file level with VBA, rather than using the regional settings of the computer.

Thank you.

 

[DanteAmor]

I have not found a way to set the List separator from VBA or at least it is not an easy way.


But I have 2 options, I will attach the examples:
The first is to save the csv file delimited by commas, but with extension txt, open the file, replace the commas with the new delimiter, save the file.

    sFile = "file.txt"
    ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlCSV
    ActiveWorkbook.Close
    Workbooks.Open sFile
    Cells.Replace What:=",", Replacement:="'"
    ActiveWorkbook.Save
    ActiveWorkbook.Close

The second, is a macro, saves data by data separated by a delimiter in a text file.

Sub Archivo()
'
    Const separador As String = "'"
    Dim ruta As String, nombre As String, fc As String
    Dim f As Long, col As Long
    Dim nFileNum As Variant
    Dim r As Range, c As Range, cadena As String
    '
    Set h1 = Sheets("Data")
    ruta = ThisWorkbook.Path & "\"
    If Left(ruta, 1) <> "\" Then ruta = ruta & "\"
    nombre = "archivo"
    '
    fc = h1.UsedRange.SpecialCells(11).Address
    f = h1.UsedRange.SpecialCells(11).Row
    col = h1.UsedRange.SpecialCells(11).Column
    nFileNum = FreeFile
    Open ruta & nombre & ".txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=nFileNum]#nFileNum[/URL] 
    For Each r In h1.Range("A1:A" & f).Rows
        For Each c In h1.Range(h1.Cells(r.Row, "A"), h1.Cells(r.Row, col))
            cadena = cadena & c.Value & separador
        Next
        If cadena <> "" Then
            cadena = Left(cadena, Len(cadena) - 1)
        End If
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=nFileNum]#nFileNum[/URL] , cadena
        cadena = Empty
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=nFileNum]#nFileNum[/URL] 
    MsgBox "End"
End Sub

They are examples for you to take and adapt them to your information.

 

[David Montoya]

Dante,

I came up with something quicker as easier to do instead:

    Range("C1048576").End(xlUp).Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).FormulaR1C1 = "=CONCATENATE(RC[-3],""~"",RC[-2],""~"",RC[-1])"
    Columns("D:D").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    ActiveWorkbook.SaveAs ("Actual Path" & Format(Now(), "DD-MMM-YYYY") & ".csv"), FileFormat:=xlCSVWindows, CreateBackup:=False

Thanks for your effort to assist!

 

[DanteAmor]

You're welcome and thaks for the feedback.