0

How can I save my workbook as CSV without losing UTF-8 characters?

So far, this the my code of saving workbook as CSV:

Option Explicit
Public wb As Workbook, ws As Worksheet, venture As String, intl As String, SvPath As String

Private Function chopFilesThenSave()
  Dim NumOfColumns As Long
  Dim RangeToCopy As Range
  Dim RangeOfHeader As Range 'data (range) of header row
  Dim WorkbookCounter As Integer
  Dim RowsInFile 'how many rows (incl. header) in new files?
  Dim p As Long

  'Initialize data
  Set ws = ThisWorkbook.Sheets("MixedNuts")
  NumOfColumns = ws.UsedRange.Columns.Count
  WorkbookCounter = 1
  RowsInFile = 2000 + 1 'how many rows (incl. header in new files?

  'Check if the folder provided is exist, else create one!
    If Len(Dir(SvPath & "batch\", vbDirectory)) = 0 Then
       MkDir SvPath & "batch\"
    End If

  'Copy the data of the first row (header)
  Set RangeOfHeader = ws.Range(ws.Cells(1, 1), ws.Cells(1, NumOfColumns))

  For p = 2 To ThisWorkbook.Sheets("Mixed").UsedRange.Rows.Count Step RowsInFile - 1
    Set wb = Workbooks.Add

    'Paste the header row in new file
    RangeOfHeader.Copy wb.Sheets(1).Range("A1")

    wb.Sheets(1).Range("A:B").NumberFormat = "@" 'set column as text

    'Paste the chunk of rows for this file
    Set RangeToCopy = ws.Range(ws.Cells(p, 1), ws.Cells(p + RowsInFile - 2, NumOfColumns))
    RangeToCopy.Copy
    wb.Sheets(1).Range("A2").PasteSpecial xlPasteValues

    'Save the new workbook, and close it
    wb.SaveAs SvPath & "batch\" & venture & intl & "BatchUpdate_" & Format(Now, "mmDDYYYY") & "-" & WorkbookCounter & ".csv", xlCSV
    wb.Close False

    'Increment file counter
    WorkbookCounter = WorkbookCounter + 1
  Next p

  Set wb = Nothing
End Function

The code runs in a loop where I cut 2,000 rows (excluding headers) from sheet "MixedNuts", copy and paste to a new workbook then save it as CSV and do this again on the next row. But again, the problem is after saving it as CSV, utf-8 characters became question marks.

Pang
  • 9,564
  • 146
  • 81
  • 122
Daddy Joe
  • 23
  • 1
  • 7
  • Possible duplicate of [Excel to CSV with UTF8 encoding](http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding) – bzimor Nov 21 '16 at 05:07
  • Try this: http://www.excelforum.com/excel-general/400043-csv-and-unicode-or-utf-8-problem.html – bzimor Nov 21 '16 at 05:09
  • http://stackoverflow.com/questions/29468070/how-to-save-a-text-file-csv-with-utf-8-without-bom-encoding-in-vba-excel – Niclas Nov 21 '16 at 07:11

0 Answers0