excel - 用于 ActiveWorkbook 的 VBA 到 VBS

Question

我正在尝试将一些嵌入在 Excel 电子表格中的 VBA 代码转换为将所有选项卡作为 CSV 保存到 VBS 脚本中，我可以从命令行运行该脚本来执行相同的操作。

Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
Dim worksheetCount
workSheetCount = oExcel.Worksheets.Count
oExcel.DisplayAlerts = False
Dim counter
Dim currentWorkSheet
For counter = 1 to workSheetCount
    Set currentWorkSheet = oBook.Worksheets(counter)
    currentWorkSheet.Copy
    oExcel.ActiveWorkbook.SaveAs oBook & "-" & currentWorkSheet.Name & ".csv", 6
    oExcel.ActiveWorkbook.Close False
    ThisWorkbook.Activate
Next    
oExcel.DisplayAlerts = True
oBook.Close False
oExcel.Quit

错误发生在“oExcel.ActiveWorkbook.SaveAs”行的开头，并表示对象不支持此方法。所以我假设 Excel 对象没有 ActiveWorkbook 方法？这里有 VBA->VBS 转换，我应该使用其他一些方法吗？

编辑

这现在对我有用:

Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))

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

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

Dim strCurDir
strCurDir = WshShell.CurrentDirectory & "\" & oBook.Name & "-"

Dim worksheetCount
workSheetCount = oExcel.Worksheets.Count

oExcel.DisplayAlerts = False

Dim counter
Dim currentWorkSheet
For counter = 1 to workSheetCount
    Set currentWorkSheet = oBook.Worksheets(counter)
    currentWorkSheet.Copy
    oExcel.ActiveWorkbook.SaveAs strCurDir & currentWorkSheet.Name & ".csv", 6
    oExcel.ActiveWorkbook.Close False
Next

oExcel.DisplayAlerts = True
oBook.Close False
oExcel.Quit

Answer 1

问题不在于 ActiveWorkbook .问题是您没有指定完整的路径。

指定路径，它将起作用。例如

oExcel.ActiveWorkbook.SaveAs "C:\MyFolder\" & _
oBook.Name & "-" & currentWorkSheet.Name & ".csv", 6

您将在 ThisWorkbook.Activate 上收到下一个错误.完全限定它，它将起作用。

提示

您可能想要删除文件扩展名，然后保存文件；)像这样

oExcel.ActiveWorkbook.SaveAs "C:\MyFolder\" & _
Left(oBook.Name, (InStrRev(oBook.Name, ".", -1, vbTextCompare) - 1)) & _
"-" & currentWorkSheet.Name & ".csv", 6