vba 将每个工作表导出到单独的 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8434994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export each sheet to a separate csv file
提问by Adam Mills
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.
我需要通过 VBA/VBS 脚本以编程方式将所有工作表（总共 4 个，我知道名称）导出到同一文件夹中名为 csv 文件的工作表，而无需加载 excel 并手动运行宏。
Ideally the script would take in the source filepath/filename.xls and the export filepath as command line arguments.
理想情况下，脚本会将源文件路径/文件名.xls 和导出文件路径作为命令行参数。
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:
我已经阅读了许多 Excel VBA 脚本来在 Excel 中执行此操作，并且我看到了一些用于加载 excel 工作簿以导出第一个工作表的脚本。但是，当我尝试将两者混合时，出现此错误：
(1,12) Expected End of Statement
(1,12) 预期的语句结束
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
回答by brettdj
A vbsto run this code would look something like this.
一个VBS运行该代码会是这个样子。
- The vbs file can be executed from the commandline
- The folder name is redundant as if the file exists (the FSO object tests for this) then the folder it resides in must also exist
- The code automates Excel to separate the sheets
- 可以从命令行执行 vbs 文件
- 文件夹名称是多余的，就好像文件存在一样（FSO 对象对此进行了测试），那么它所在的文件夹也必须存在
- 该代码使 Excel 自动分离工作表
two key points to note compared to your VBA above
与上面的 VBA 相比需要注意的两个关键点
- 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
xlCSVin vbscript, hence the use of 6 below as the CSV formatDim 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
- 您不能将 vbs 对象作为字符串、工作簿等变暗（因此您的初始错误）。你只能调暗它们
您不能使用命名常量，例如
xlCSV在 vbscript 中，因此使用下面的 6 作为 CSV 格式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
回答by Ekkehard.Horner
To get you started:
让您开始：
Given an Excel workbook containing a sheet Demo like
给定一个包含工作表 Demo 的 Excel 工作簿，例如
-------------------------------
SELECT * FROM Demo
-------------------------------
|F1|F2 |F3 |F4 |
| 1|1.1|12/10/2011|text elm 1|
| 2|2.2|12/11/2011|text elm 2|
| 3|4.4|12/12/2011|text elm 3|
-------------------------------
and an ADODB.Connection with a ConnectionString like:
和一个带有 ConnectionString 的 ADODB.Connection 像：
Provider=MSDASQL.1;Extended Properties="DBQ=<FullPathToYourXls>;Driver={Microsoft
Excel Driver (*.xls)};
all you need to .Execute is a SELECT INTO statement like
所有你需要 .Execute 是一个 SELECT INTO 语句，如
SELECT * INTO [Demo.csv] IN '<PathToYourCsvFolder>' 'Text;' FROM Demo
to get:
要得到：
type ..\data\ExcelCsv2\Demo.csv
"F1";"F2";"F3";"F4"
1;1,10;10.12.2011 00:00:00;"text elm 1"
2;2,20;11.12.2011 00:00:00;"text elm 2"
3;4,40;12.12.2011 00:00:00;"text elm 3"
(german locale)
（德国语言环境）
The SELECT INTO statement will create the appropriate section
SELECT INTO 语句将创建适当的部分
[Demo.csv]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
Col1=F1 Integer
Col2=F2 Float
Col3=F3 Date
Col4=F4 Char Width 50
in the schema.ini file automagically.
在 schema.ini 文件中自动。
