将每个工作表导出到单独的 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.

理想情况下，脚本会将源文件路径/文件名.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

推荐答案

A 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 对象作为字符串、工作簿等变暗(因此您的初始错误).你只能调暗它们

• 你不能在 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:	emp	est.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屋！