自动将Xls转换为CSV [英] Auto convert Xls to CSV

问题描述

我使用在stackoverflow上找到的以下VBS脚本将xls转换为csv.它工作正常.我想使用底部的批处理文件来运行它.我不知道如何实现自己想要的.批处理文件为csv文件提供了与xls文件相同的名称.因为xls文件中包含两个工作表，所以我需要为每个xls文件生成两个csv文件

I am using the following VBS script found on stackoverflow to convert xls to csv. It works fine. I want to run it with the batch file at the bottom. I don't know how to achieve what I want. The batch file gives the csv file the same name as the xls file. Because the xls file has two worksheets in it I need to produce two csv's for each xls file

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(1))
oBook.SaveAs WScript.Arguments.Item(2), 6

oBook.Close False
oExcel.Quit
WScript.Echo "Done"

这是批处理文件

FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%i.csv"

我需要传递2个输出.csv文件名，其中一个应为nnnnn_1.csv，另一个应为nnnnn_2.csv，以说明xls文件中的2个工作表.

I need to pass in 2 output .csv file names one should be nnnnn_1.csv the other should be nnnnn_2.csv to account for the 2 worksheets in the xls files.

感谢您的帮助

推荐答案

如果将VBS脚本更改为此，它应该可以工作:

If you change the VBS script to this it should work:

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

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

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

oBook.Worksheets(1).Copy
Dim oBookNew1     
Set oBookNew1 = oExcel.ActiveWorkbook
oBookNew1.SaveAs Replace(WScript.Arguments.Item(1),".csv","_1.csv"), 6
oBookNew1.Close False

oBook.Worksheets(2).Copy
Dim oBookNew1     
Set oBookNew1 = oExcel.ActiveWorkbook
oBookNew2.SaveAs Replace(WScript.Arguments.Item(1),".csv","_2.csv"), 6
oBookNew2.Close False

oBook.Close False
oExcel.Quit
WScript.Echo "Done"

这篇关于自动将Xls转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！