vba 将工作表从 Excel 导出到 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2686695/
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 sheet from Excel to CSV
提问by Mike Wills
I am creating a spread sheet to help ease the entry of data into one of our systems. They are entering inventory items into this spread sheet to calculate the unit cost of the item (item cost + tax + S&H). The software we purchased cannot do this.
我正在创建一个电子表格，以帮助简化将数据输入我们的系统之一。他们将库存项目输入此电子表格以计算项目的单位成本（项目成本 + 税费 + S&H）。我们购买的软件无法做到这一点。
Aan invoice can have one or more lines (duh!) and I calculate the final unit cost. This is working fine. I then want to take that data and create a CSV from that so they can load it into our inventory system. I currently have a second tab that is laid out like I want the CSV, and I do an equal cell (=Sheet!A3) to get the values on the "export sheet". The problem is when they save this to a CSV, there are many blank lines that need to be deleted before they can upload it. I want a file that only contains the data that is needed.
一张发票可以有一行或多行（废话！），我计算最终的单位成本。这工作正常。然后我想获取该数据并从中创建一个 CSV，以便他们可以将其加载到我们的库存系统中。我目前有第二个选项卡，它的布局就像我想要的 CSV，我做了一个相等的单元格 (=Sheet!A3) 来获取“导出表”上的值。问题是当他们将其保存到 CSV 时，在上传之前需要删除许多空白行。我想要一个只包含所需数据的文件。
I am sure this could be done in VBA, but I don't know where to start or know how to search for an example to start. Any direction or other options would be appreciated.
我确信这可以在 VBA 中完成，但我不知道从哪里开始，也不知道如何搜索一个示例来开始。任何方向或其他选择将不胜感激。
采纳答案by Mike Wills
回答by dwo
Look at Range.SpecialCells(xlBlanks).EntireRow.Delete, I think this is what you are looking for!
看Range.SpecialCells(xlBlanks).EntireRow.Delete，我想这就是你要找的！
回答by Allan Bowe
expanding on @dwo's answer - this code will allow you to both remove the blank rows and export to CSV:
扩展@dwo 的答案 - 此代码将允许您删除空白行并导出到 CSV：
Sub export_to_csv(sheetname As String, OutDir As String)
Sheets(sheetname).Select
Set wb = ActiveWorkbook
Set newwb = Workbooks.Add()
wb.ActiveSheet.Copy newwb.ActiveSheet 'copy sheet to new workbook
newwb.ActiveSheet.Activate
ActiveSheet.UsedRange 'refresh the used range
Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select 'select relevant cells
Selection.SpecialCells(xlBlanks).EntireRow.Delete 'remove empty rows
Application.DisplayAlerts = False 'avoid warning message when overwriting existing files
newwb.SaveAs OutDir & sheetname, xlCSVWindows 'save as CSV
newwb.Close 'close new workbook
Application.DisplayAlerts = True 'reset warning messages
End Sub
Sub test()
export_to_csv sheetname:="Sheet1", OutDir:="C:\temp\"
End Sub
