How to save the converted CSV from Excel in UTF-8 CSV format using VBScript?

Question

I have implemented the below script but could not save the same as UTF-8 format.

Dim strExcelFileName
Dim strCSVFileName
strExcelFileName = "C:\Users\test.xlsx"
Set fso = CreateObject("Scripting.FileSystemObject")
strScript = WScript.ScriptFullName
strScriptPath = fso.GetAbsolutePathName(strScript & "\..")
LPosition = InStrRev(strExcelFileName, "\")
If LPosition = 0 Then
    strExcelFileName = strScriptPath & "\" & strExcelFileName
    strScriptPath = strScriptPath & "\"
Else
    strScriptPath = Mid(strExcelFileName, 1, LPosition)
End If
Set objXL = CreateObject("Excel.Application")
Set objWorkBook = objXL.Workbooks.Open(strExcelFileName)
objXL.DisplayAlerts = False
For Each sheet In objWorkBook.Sheets
    If objXL.Application.WorksheetFunction.CountA(sheet.Cells) <> 0 Then
        sheet.SaveAs strScriptPath & sheet.Name & cell & ".csv", 6 'CSV
    End If
Next
objWorkBook.Close
objXL.Quit
Set objXL = Nothing
Set objWorkBook = Nothing
Set fso = Nothing

I don't think you can do this directly with Excel ([see this related question](https://stackoverflow.com/q/4221176/1630171)). You should be able [convert the CSV](https://stackoverflow.com/a/32348235/1630171) in a second step, though. — Ansgar Wiechers, Mar 05 '18 at 09:35
I will not be able to upload the Excel and into Google sheets and convert it. Is there any possibilities to force and save as the csv sheets in UTF-8 CSV? — punith, Mar 07 '18 at 09:09

score -1 · Answer 1 · edited Nov 20 '19 at 12:50

-1

Try 62 instead of 6 in the save line like this:

sheet.SaveAs strScriptPath & sheet.Name & cell & ".csv", 62

edited Nov 20 '19 at 12:50

double-beep

5,031
17
33
41

answered Nov 19 '19 at 10:28

p4n1

107
1
7

1

You should tell why this change will fix the problem. Just "try that" does not provide an answer. – Lorenzo Isidori Nov 19 '19 at 10:48
You can check here: https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat – p4n1 Nov 20 '19 at 09:14

How to save the converted CSV from Excel in UTF-8 CSV format using VBScript?

1 Answers1