求助：使用VBScript或宏将XLSX转为UTF-8格式CSV

Let's fix your UTF-8 CSV conversion issue step by step.

First, let's break down why your original scripts didn't work:

• The first script uses Excel's xlCSV format (code 6), which saves as ANSI-encoded CSV, not UTF-8.
• The second script tries to read an XLSX file directly with ADODB.Stream—this is invalid because XLSX is a compressed binary file (a ZIP archive), not plain text, so reading it as text will always result in garbage characters.

Here are two reliable solutions depending on your Excel version:

Solution 1: For Excel 2016 and later (direct UTF-8 CSV support) #

Excel 2016 introduced the xlCSVUTF8 format (code 62), which lets you save directly to UTF-8 encoded CSV. Modify your first script like this:

if WScript.Arguments.Count < 2 Then 
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xlsx Destination.csv" 
    Wscript.Quit 
End If 

Dim oExcel 
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False ' Run in background without showing Excel
oExcel.DisplayAlerts = False ' Suppress save prompts

Dim oBook 
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

' Save as UTF-8 CSV using format code 62 (xlCSVUTF8)
oBook.SaveAs WScript.Arguments.Item(1), 62 

oBook.Close False 
oExcel.Quit 
WScript.Echo "Done! UTF-8 CSV generated successfully."

This script generates a UTF-8 CSV with a BOM (Byte Order Mark), which is compatible with most tools like Excel, Google Sheets, and text editors.

Solution 2: For older Excel versions (2013 and earlier) #

Older Excel versions don't have the xlCSVUTF8 option, so we'll first save the file as Unicode text (UTF-16) and then convert it to UTF-8 using ADODB.Stream:

if WScript.Arguments.Count < 2 Then 
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xlsx Destination.csv" 
    Wscript.Quit 
End If 

' Create a temporary Unicode text file path
Dim tempUnicodePath
tempUnicodePath = Wscript.Arguments.Item(1) & ".tmp.txt"

Dim oExcel 
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False

Dim oBook 
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
' Save as Unicode text (UTF-16LE format, code 42 = xlUnicodeText)
oBook.SaveAs tempUnicodePath, 42 
oBook.Close False 
oExcel.Quit 

' Convert UTF-16 text to UTF-8
Const adTypeText = 2
Const adSaveCreateOverWrite = 2

Dim streamSrc, streamDst
Set streamSrc = CreateObject("ADODB.Stream")
Set streamDst = CreateObject("ADODB.Stream")

' Load the temporary Unicode file
streamSrc.Type = adTypeText
streamSrc.Charset = "Unicode" ' Matches UTF-16LE encoding
streamSrc.Open
streamSrc.LoadFromFile tempUnicodePath

' Write to UTF-8 CSV
streamDst.Type = adTypeText
streamDst.Charset = "UTF-8"
streamDst.Open
streamSrc.CopyTo streamDst

streamDst.SaveToFile Wscript.Arguments.Item(1), adSaveCreateOverWrite

' Clean up the temporary file
CreateObject("Scripting.FileSystemObject").DeleteFile tempUnicodePath

' Cleanup objects
streamSrc.Close
streamDst.Close
Set streamSrc = Nothing
Set streamDst = Nothing

WScript.Echo "Done! UTF-8 CSV generated successfully."

Key Notes: #

• Always set oExcel.Visible = False and oExcel.DisplayAlerts = False to make the script run smoothly in the background.
• The BOM in the UTF-8 CSV from Solution 1 is normal—it helps tools recognize the encoding correctly.
• Never try to read an XLSX file directly as plain text—always use Excel's object model to extract the data first.

内容的提问来源于stack exchange，提问作者Shrangi Tandon