VBA code: Save an excel file as semicolon separated values

[Dorthe]

Hello
I am trying to write a vba-code which will save my Excel file as a semicolon separated values file. Unfortunately my results so far are halting.

Both pieces of code below create a semicolon separated values file, but only if I save them manually.
If I just close the worksheet the file is saved as a comma separated file.


So could anyone maybe help me or explain to me how I write a code that will save the file as semicolon separated values?

Thank You very much in advance.
Yours Dorthe Christiansen




1) ActiveWorkbook.SaveAs Filename:="C:\Users\Public\Desktop" & Aname & sFile, FileFormat:=6

2) ActiveWorkbook.SaveAs Filename:="C:\Users\Public\Desktop\SemicolonSeparatedFile.csv", _
FileFormat:=xlCSV, CreateBackup:=False

 

[Leith Ross]

Hello Dorthe,

This macro will allow to set the field separator. If you do not supply one then a comma will be used. You can also set the new line character that defines the end of a row. The default is a Carriage return and Line feed. Add a new VBA module to your workbook to paste the macro code into. You can then add a call to this macro in your existing code.

Macro Code

' Written:  January 21, 2018
' Authpor:  Leith Ross
' Summary:  Copies all of the cells in the supplied range with
'           values to a new Unicode text file. Each row is terminated
'           with the specified new line character(s) and the fields
'           in each row are separated with the character(s) in the
'           string separator.
'
' NOTE:     If you open this file with Excel, you will need to run
'           TextToColumns to separate the data into the worksheet columns.




Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)


Sub CreateUnicodeFile(ByVal File As String, ByRef Rng As Range, Optional Separator As String, Optional NewLine As String)


    Dim Bytes() As Byte
    Dim Divider As String
    Dim k       As Integer
    Dim n       As Long
    Dim Row     As Long
    Dim Text    As String
    
        ' Defaults: Separator = ",", NewLine = CrLf
        Separator = IIf(Separator = "", ",", Separator)
        NewLine = IIf(NewLine = "", vbCrLf, NewLine)
            
            ' Add the Unicode Byte Order Mark for little-endian.
            ReDim Bytes(1)
                Bytes(0) = 255
                Bytes(1) = 254
        
            For Row = 1 To Rng.Rows.Count
                
                For Col = 1 To Rng.Columns.Count
                    Text = Text & Rng.Cells(Row, Col)
                    If Col <> Rng.Columns.Count Then
                        Text = Text & Separator
                    End If
                Next Col
                
                Text = Text & NewLine
            Next Row
            
        Text = StrConv(Text, vbUnicode)
        n = Len(Text)
        
        k = UBound(Bytes)
        ReDim Preserve Bytes(k + n)
        CopyMemory Bytes(k + 1), ByVal Text, n
                    
        Open File For Binary Access Write As #1 
            Put #1 , , Bytes
        Close #1 
        
End Sub

Example of Calling the Macro

Sub MacroTest()


    Call CreateUnicodeFile("C:\Users\Public\Desktop\SemicolonSeparatedFile.csv", ActiveSheet.UsedRange, ";")
    
End Sub

 

[Dorthe]

Dear Leith
Thank You So much!
This code did the job very nicely
Yours
Dorthe Christiansen

 

[Leith Ross]

Hello Dorthe,

You're welcome. It is always nice when the code runs right the first time. Have a great day.

 

[Pathman]

Hi Leith,

I've tried but getting VBA error Run-time error '75
Path/File access error,

and when I debug this error, then goes to code: Open File For Binay Access Write As #1


Can you please help on this?

Thank you,
Pathman

 

[michiel_soede]

Very nice solution, thanks!