如何用VBS将制表符分隔的CSV文件转换为XLSX？

我帮你调整了原来的VBS脚本，让它能像你录制的VBA宏那样，正确解析制表符分隔的CSV文件，解决默认逗号分隔导致的格式错位问题。下面是修改后的完整代码：

'Constants
Const xlOpenXMLWorkbook = 51 '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50 '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56 '(97-2003 format in Excel 2007-2016, xls)
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Const xlInsertDeleteCells = 1

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFolder = "B:\EE\EE29088597\Files"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Then
        ' Display to console each file being converted
        Wscript.Echo "Converting """ & strPath & """"
        
        ' 创建新工作簿来导入CSV（替代直接打开）
        Set objWorkbook = objExcel.Workbooks.Add()
        Set objWorksheet = objWorkbook.ActiveSheet
        
        ' 配置QueryTable，对应你录制的VBA宏参数
        Set qt = objWorksheet.QueryTables.Add( _
            Connection:="TEXT;" & strPath, _
            Destination:=objWorksheet.Range("$A$1") _
        )
        With qt
            .CommandType = 0
            .Name = objFSO.GetBaseName(strPath)
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True ' 关键：设置制表符为分隔符
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False ' 关闭默认的逗号分隔
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        
        ' 删除QueryTable（可选，避免后续编辑时的提示）
        qt.Delete
        
        ' 保存为XLSX格式
        strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
        objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

关键改动说明： #

• 替换CSV打开逻辑：不再用Workbooks.Open直接读取CSV（默认逗号分隔），而是创建新工作簿，通过QueryTables.Add导入CSV，和你录制的VBA宏逻辑完全对齐。
• 强制制表符分隔：明确开启.TextFileTabDelimiter = True，同时关闭逗号分隔选项，确保脚本按制表符识别列。
• 完整匹配宏参数：把你VBA宏里的格式保留、数据类型、列宽调整等参数都同步到VBS配置中，保证导入效果和宏一致。

这样修改后，脚本就能正确解析制表符分隔的CSV内容，输出符合预期的XLSX文件了。

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