How can Defining fields delimiter character, Encoding and Records seperator ({CR}{LF}) for CSV files in export (Save As) from Excel by VBA

Question

I tied Save As my sheet in CSV format with (Comma Delimited) "," for fields and {CR}{LF} for records in line by below code.

The issue is:

1) Generated file is delimited by ";" sign instead of ",".

2) Be sure records are separated by {CR}{LF}

3) How can define encoding as Unicode UTF-8 (in situation if needed)

I want this file have save by .txt extension.

How can I generate CSV file in true format by above situation?

Sub GenCSV()

    Dim NewBook As Workbook

    Set NewBook = Workbooks.Add
    ThisWorkbook.Worksheets("Sheet1").Range("tblTaxRep[[Header1]: _
        [Headern]]").SpecialCells(xlCellTypeVisible).Copy
    With NewBook
        .Worksheets("Sheet1").Cells(1, 1).PasteSpecial (xlPasteValues)
        .SaveAs Filename:=ThisWorkbook.Path & "Report" & ".txt", FileFormat:=xlCSV
        .Close SaveChanges:=False

    End With

End Sub

score 0 · Accepted Answer · answered Jul 22 '17 at 14:06

Option Explicit

Const strDelimiter = """"
Const strDelimiterEscaped = strDelimiter & strDelimiter
Const strSeparator = ","
Const strRowEnd = vbCrLf
Const strCharset = "utf-8"

Function CsvFormatString(strRaw As String) As String

    Dim boolNeedsDelimiting As Boolean

    boolNeedsDelimiting = InStr(1, strRaw, strDelimiter) > 0 _
        Or InStr(1, strRaw, Chr(10)) > 0 _
        Or InStr(1, strRaw, strSeparator) > 0

    CsvFormatString = strRaw

    If boolNeedsDelimiting Then
        CsvFormatString = strDelimiter & _
            Replace(strRaw, strDelimiter, strDelimiterEscaped) & _
            strDelimiter
    End If

End Function

Function CsvFormatRow(rngRow As Range) As String

    Dim arrCsvRow() As String
    ReDim arrCsvRow(rngRow.Cells.Count - 1)
    Dim rngCell As Range
    Dim lngIndex As Long

    lngIndex = 0

    For Each rngCell In rngRow.Cells
        arrCsvRow(lngIndex) = CsvFormatString(rngCell.Text)
        lngIndex = lngIndex + 1
    Next rngCell


    CsvFormatRow = Join(arrCsvRow, ",") & strRowEnd

End Function

Sub CsvExportRange( _
        rngRange As Range, _
        Optional strFileName As Variant _
    )

    Dim rngRow As Range
    Dim objStream As Object

    If IsMissing(strFileName) Or IsEmpty(strFileName) Then
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:=ActiveWorkbook.Path & "\" & rngRange.Worksheet.Name & ".csv", _
            FileFilter:="CSV (*.csv), *.csv", _
            Title:="Export CSV")
    End If

    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = 2
    objStream.Charset = strCharset
    objStream.Open

    For Each rngRow In rngRange.Rows
        objStream.WriteText CsvFormatRow(rngRow)
    Next rngRow

    objStream.SaveToFile strFileName, 2
    objStream.Close

End Sub

Sub CsvExportSelection()
    CsvExportRange ActiveWindow.Selection
End Sub

Sub CsvExportSheet(varSheetIndex As Variant)

    Dim wksSheet As Worksheet
    Set wksSheet = Sheets(varSheetIndex)

    CsvExportRange wksSheet.UsedRange

    End Sub

Reference

How can Defining fields delimiter character, Encoding and Records seperator ({CR}{LF}) for CSV files in export (Save As) from Excel by VBA

The issue is:

1 Answers1