如何用Excel VBA将输出工作表保存为无乱码的.CSH文件？

你当前的代码只是修改了文件扩展名，但实际保存的仍是Excel二进制格式，因此记事本打开会显示乱码。要生成纯文本格式的.CSH文件，需直接导出工作表的文本内容并指定正确编码。以下是两种可行方案：

方案1：ANSI编码纯文本导出（适合英文/数字内容） #

该方法直接读取单元格内容，以制表符分隔每行数据，保存为ANSI编码的.CSH文件，兼容性强：

Sub SaveOutputAsCSH()
    Dim ws As Worksheet
    Dim savePath As String
    Dim fileNum As Integer
    Dim lastRow As Long, lastCol As Integer
    Dim i As Long, j As Integer
    Dim lineText As String
    
    ' 指定目标工作表
    Set ws = ThisWorkbook.Sheets("OUTPUT")
    
    ' 弹出保存对话框，指定初始路径和文件格式
    savePath = Application.GetSaveAsFilename( _
        InitialFileName:=ThisWorkbook.Path & "\OUTPUTFILE", _
        FileFilter:="CSH Files (*.csh), *.csh")
    
    ' 用户取消操作则退出
    If savePath = "False" Then Exit Sub
    
    ' 获取数据区域的边界
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 打开文件准备写入
    fileNum = FreeFile()
    Open savePath For Output As #fileNum
    
    ' 逐行逐列拼接文本并写入
    For i = 1 To lastRow
        lineText = ""
        For j = 1 To lastCol
            If j > 1 Then lineText = lineText & vbTab ' 用制表符分隔列（可改为逗号等）
            lineText = lineText & ws.Cells(i, j).Value
        Next j
        Print #fileNum, lineText
    Next i
    
    ' 关闭文件
    Close #fileNum
    
    MsgBox "CSH文件已保存完成！", vbInformation
End Sub

方案2：UTF-8编码导出（适合含中文/非ASCII字符内容） #

如果数据包含中文或特殊字符，使用UTF-8编码可避免乱码，需借助ADODB.Stream对象：

Sub SaveOutputAsCSH_UTF8()
    Dim ws As Worksheet
    Dim savePath As String
    Dim lastRow As Long, lastCol As Integer
    Dim i As Long, j As Integer
    Dim lineText As String
    Dim stream As Object
    
    Set ws = ThisWorkbook.Sheets("OUTPUT")
    
    savePath = Application.GetSaveAsFilename( _
        InitialFileName:=ThisWorkbook.Path & "\OUTPUTFILE", _
        FileFilter:="CSH Files (*.csh), *.csh")
    
    If savePath = "False" Then Exit Sub
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 创建流对象实现UTF-8编码写入
    Set stream = CreateObject("ADODB.Stream")
    With stream
        .Charset = "UTF-8"
        .Open
        ' 逐行拼接内容并写入
        For i = 1 To lastRow
            lineText = ""
            For j = 1 To lastCol
                If j > 1 Then lineText = lineText & vbTab
                lineText = lineText & ws.Cells(i, j).Value
            Next j
            .WriteText lineText & vbCrLf ' 添加换行符
        Next i
        ' 保存文件（覆盖已存在文件）
        .SaveToFile savePath, 2
        .Close
    End With
    
    Set stream = Nothing
    MsgBox "UTF-8编码的CSH文件已保存完成！", vbInformation
End Sub

注意事项 #

• 可根据需求修改列分隔符（将vbTab改为","即可生成逗号分隔的文本）
• 若使用方案2，需确保Excel允许创建ADODB.Stream对象（默认环境支持）

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