vba 宏循环遍历行直到空值，复制值并将这些结果保存到 CSV？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14266356/
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
Macro to Loop Through Rows Until Empty Value, Copy Values, and Save Those Results to CSV?
提问by Scott Holtzman
I want to create a macro that will loop through rows in the active Excel worksheet. Each loop iteration will copy the values of column A, B, and C. If the first cell is blank, I want to end the loop UNLESS the empty cell is in row two. Then I want to continue with the loop. Ultimately, after all the data is copied, I want to write that copied data to a CSV file. Is this possible?
我想创建一个宏，它将遍历活动 Excel 工作表中的行。每次循环迭代都会复制 A、B 和 C 列的值。如果第一个单元格为空，我想结束循环，除非空单元格位于第二行。然后我想继续循环。最终，在复制所有数据后，我想将复制的数据写入 CSV 文件。这可能吗？
I tried recording a macro, but the solution isn't at all robust, as it doesn't use loops, rather hard-coded Range Selects:
我尝试录制一个宏，但该解决方案一点也不强大，因为它不使用循环，而是硬编码Range Selects：
Sub Export_to_CSV()
'
' Export_to_CSV Macro
'
'
Range("A1:C1,A3:C27").Select
Range("A3").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
ChDir "C:\CSV"
ActiveWorkbook.SaveAs Filename:="C:\CSV\MacroCSV.csv", FileFormat:=xlCSV, _
CreateBackup:=False
End Sub
Thanks!
谢谢！
回答by Scott Holtzman
If you requirements are exactlyas you have written them, this should work.
如果您的要求与您编写的完全一样，那么这应该可行。
Option Explicit
Sub Export_To_CSV()
Dim wkbMain as Workbook, wkbCopy as Workbook
Dim wksMain as Worksheet, wksCopy as WOrksheet
Set wkbMain = ThisWorkbook
Set wkbCopy = Workbooks.Add
Set wksMain = wkbMain.Sheets("mySheet")
Set wksCopy = wkbCopy.Sheets(1)
With wksMain
.Range(.Range("A3"),.Range("C3").End(xlDown)).Copy wksCopy.Range("A1")
End With
With wkbCopy
Application.DisplayAlerts = False
Dim x as Integer
For x = 2 to .Worksheets.Count
.Sheets(x).Delete
Next
Application.DisplayAlerts = True
.SaveAs Filename:="C:\CSV\MacroCSV.csv", FileFormat:=xlCSV, CreateBackup:=False
End With
End Sub
