excel - 在vbscript中用管道分隔文本文件到csv文件

Question

我有一个带有 | 的 txt 文件 划定的。我想将该文件保存在excel中。这是我的代码

' Define constants
Const xlDelimited = 1   
'The TSV file to be converted
strProdTextFile = "C:\Users\laxmi\Documents\Payroll\CompareFiles\SEACRDPRD01SVM_xLearnCom_10292018.txt"

'The Excel file to be created
strProdExcelFile = "C:\Users\laxmi\Documents\Payroll\CompareFiles\SEACRDPRD01SVM_xLearnCom_10292018.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE

Set objWorkbook = objExcel.Workbooks.OpenText(strProdTextFile,,,xlDelimited,,,,,,,True,"|")
objExcel.ActiveWorkbook.SaveAs strProdExcelFile, 51

objExcel.ActiveWorkbook.Close False
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit 

这是我得到的错误：

所需对象：'objExcel.Workbooks.OpenText(...)'

第 (14) 行：“设置 objWorkbook = objExcel.Workbooks.OpenText(strProdTextFile,,,xlDelimited,,,,,,,True,"|")"

标签： exceltextvbscriptcsv

Answer 1

Workbooks.OpenText不返回值，因此您的脚本失败

Set objWorkbook = objExcel.Workbooks.OpenText(...)

因为Set objWorkbook =需要右侧的对象。由于您的下一行使用的是，ActiveWorkbook您可以简单地Set objWorkbook =从行的开头删除。