将每个工作表导出到单独的csv文件 [英] Export each sheet to a separate csv file

问题描述

我需要通过一个VBA / VBS脚本程序条件导出所有工作表（总共4个，我知道名称）到同一文件夹中名为csv文件的工作表，不加载excel并手动运行宏。

I need to programmaticaly via a VBA/VBS script export all worksheets (4 in total and I know the names) to worksheet named csv files in the same folder, without loading excel and running the macro manually.

理想情况下，脚本会将源文件路径/ filename.xls和导出文件路径作为命令行参数。

Ideally the script would take in the source filepath/filename.xls and the export filepath as command line arguments.

许多Excel VBA脚本只是在Excel中做，我已经看到一些加载excel工作簿导出第一张表。然而，当我尝试混合两个，我得到这个错误：

I have read many Excel VBA scripts for doing just that within Excel and I have seen some for loading an excel workbook to export the first sheet. However when I try to blend the two I get this error:

（1,12）期望结束语句

(1,12) Expected End of Statement

Dim source As Workbook
Set source = Application.Workbooks.Open(WScript.Arguments.Item(0), ReadOnly:=True)
For Each sheet In source.Sheets
.SaveAs Filename:= WScript.Arguments.Item(1) & Source.Sheets.Name, FileFormat:=xlCSV
Next sheet
wb.Close

推荐答案

vbs 运行这个代码看起来像这样。

A vbs to run this code would look something like this.

1. 可以从命令行执行vbs文件


2. 该文件夹名称是冗余的，如果该文件存在（FSO对象测试此文件夹），那么它驻留的文件夹也必须存在


3. 该代码自动化Excel分离

两个要注意的要点，与您上面的VBA相比

two key points to note compared to your VBA above

• 您不能将vbs对象作为字符串，Workbook等（因此您的初始错误）。您只能调暗它们



• 您不能在vbscript中使用命名常量，例如 xlCSV 6下面作为CSV格式

• you can't Dim a vbs object as a string, Workbook etc (hence your initial error). You can only Dim them

• you can't used a named constant such as xlCSV in vbscript, hence the use of 6 below as the CSV format

Dim strFilename  
Dim objFSO  
Set objFSO = CreateObject("scripting.filesystemobject")  
strFilename = "C:\temp\test.xlsx"  
If objFSO.fileexists(strFilename) Then  
  Call Writefile(strFilename)  
Else  
  wscript.echo "no such file!"  
End If  
Set objFSO = Nothing  

Sub Writefile(ByVal strFilename)  
Dim objExcel  
Dim objWB  
Dim objws  

Set objExcel = CreateObject("Excel.Application")  
Set objWB = objExcel.Workbooks.Open(strFilename)  

For Each objws In objWB.Sheets  
  objws.Copy  
  objExcel.ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", 6  
  objExcel.ActiveWorkbook.Close False  
Next 

objWB.Close False  
objExcel.Quit  
Set objExcel = Nothing  
End Sub  

这篇关于将每个工作表导出到单独的csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！