使用 VBA 将 Excel 工作表的（部分）范围导出为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44396264/
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 Range of (Part of) an Excel Sheet to CSV with VBA
提问by Itsallgonepearshaped
I know exporting to csv has been asked about a billion times, and I've done it quite a few times, but I'm trying to figure out how to export only a portion of the sheet to csv without cycling through each row or cell and printing them individually to file. I need to maintain local formatting as one requirement.
我知道导出到 csv 已经被问到了大约 10 亿次，我已经做了很多次了，但我试图弄清楚如何只将工作表的一部分导出到 csv 而不循环遍历每一行或单元格并将它们单独打印到文件中。我需要将本地格式作为一项要求来维护。
Sheets("PI_OUTPUT").Copy
This copies the entire sheet, warts and all. I have formula's in rows 1 to 20000 and columns A to X, but with a variable amount of blank rows after the data has been processed. If I use the copy method I copy all the empty rows, which outputs as rows of commas. ,,,,,,, etc....
这将复制整个工作表、疣和所有内容。我在第 1 到 20000 行和 A 到 X 列中有公式，但在处理数据后有可变数量的空白行。如果我使用 copy 方法，我会复制所有空行，这些行输出为逗号行。，，，，，，， 等等....
I tried using activesheet.deleterows to trim the output file after the copy, but that gives me an error.
我尝试使用 activesheet.deleterows 在复制后修剪输出文件，但这给了我一个错误。
I've tried using:
我试过使用：
Worksheets("PI_OUTPUT").Activate
Sheets("PI_OUTPUT").Range("A2:X5000").Copy
I've tried to use another suggestion: specialcells(xlCellTypeVisible), but I can't seem to get it to work:
我尝试使用另一个建议：specialcells(xlCellTypeVisible)，但我似乎无法让它工作：
Set rng = Sheets("PI_OUTPUT").SpecialCells(xlCellTypeVisible)
Set Workbook = Workbooks.Add
With Workbook
Call rng.Copy
.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
.SaveAs filename:=MyPath & MyFileName, FileFormat:=xlCSV
.Close
End With
To make it worse I have to do it across a number of sheets, all with variable number of columns, rows, all saved to separate files, so I'm looking for something I can repeat a number of times. I've got a folder path pop up to select the location, and have a dynamically constructed file name, but the copy / paste evades to file me.
更糟糕的是，我必须在多张工作表上进行操作，所有工作表都有可变数量的列、行，所有这些都保存到单独的文件中，因此我正在寻找可以重复多次的内容。我有一个文件夹路径弹出来选择位置，并有一个动态构造的文件名，但复制/粘贴回避了我。
Currently tearing what little hair I had out after countless google searches, any help gratefully received.
目前在无数次谷歌搜索后撕掉了我的小头发，感激地收到任何帮助。
回答by M--
You can use the code below;
您可以使用下面的代码；
Sub testexport()
'Adpated from OzGrid;
' export Macro as CSV
Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("PI_OUTPUT")
With wsh.Range("A2:X20000")
.AutoFilter 1, "<>" 'Filter to get only non-blank cells
'assuming there is no blank cell within a filled row:
wsh.Range(wsh.Cells(2, 1), wsh.Cells(24, 2).End(xlDown)).Copy 'copy non-blank cells
'.AutoFilter should not cancel autofilter as it clears cutcopy mode
End With
Application.DisplayAlerts = False 'avoid from "save prompt window"
Workbooks.Add
ActiveSheet.Paste
'Saves to C drive as Book2.csv
ActiveSheet.SaveAs Filename:= _
"C:\Book2.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
wsh.Range("A2:X20000").AutoFilter 'clear the filter
Application.DisplayAlerts = True 'set to default
End Sub
