将数据导出为 CSV - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19275943/
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
Exporting Data into a CSV - Excel VBA
提问by Thatdude1
Say I have a function that generates some data into cells into the current worksheet like :
假设我有一个函数可以将一些数据生成到当前工作表的单元格中，例如：
Cells(1, "A").Value = ...
Cells(2, "A").Value = ...
Cells(3, "A").Value = ...
Cells(4, "A").Value = ...
Instead of the being the current worksheet in the current workbook, I want to create and load it into a csv file, to a give path
而不是当前工作簿中的当前工作表，我想创建并将其加载到一个 csv 文件中，到一个给定的路径
Say C:\USERS\Documents\Sample.csv.
说C:\USERS\Documents\Sample.csv。
I've seen stuff like
我见过类似的东西
ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False
But this will just save the current workbook to another location, but I don't want to generate data in the current worksheet and then save, rather I want to export right away? Is there anyway I can do that. Maybe making like ActiveWorkbook = //pathnameand then Activating it ?
但这只会将当前工作簿保存到另一个位置，但我不想在当前工作表中生成数据然后保存，而是想立即导出？无论如何我可以做到这一点。也许制作喜欢ActiveWorkbook = //pathname然后激活它？
回答by The_Barman
You can write to a CSV quite simply using VBA. An example could be:
您可以使用 VBA 非常简单地写入 CSV。一个例子可能是：
Sub WriteCSVFile()
Dim My_filenumber As Integer
Dim logSTR As String
My_filenumber = FreeFile
logSTR = logSTR & Cells(1, "A").Value & " , "
logSTR = logSTR & Cells(2, "A").Value & " , "
logSTR = logSTR & Cells(3, "A").Value & " , "
logSTR = logSTR & Cells(4, "A").Value
Open "C:\USERS\Documents\Sample.csv" For Append As #My_filenumber
Print #My_filenumber, logSTR
Close #My_filenumber
End Sub
回答by CharlieSmith
Use the .move to make a new book of the target sheet, then .saveas the newly created book as a CSV. Adjust the Pathname to adjust the directory where you want your csv saved.
使用 .move 制作目标工作表的新书，然后将新创建的书保存为 CSV。调整路径名以调整要保存 csv 的目录。
Pathname = "" & Thisworkbook.path & "YourName.csv"
Sheets("Sheet you want as CSV").Move
ActiveWorkbook.SaveAs Filename:=PathName, _
FileFormat:=xlCSV, CreateBackup:=False
回答by Praveen Agarwal
Just modified the code by @CharlieSmith to a fairly simple and more usable code, which will convert all the sheets in your workbook to new csv files named with respective sheet names.
刚刚将@CharlieSmith 的代码修改为一个相当简单且更有用的代码，这会将工作簿中的所有工作表转换为以各自工作表名称命名的新 csv 文件。
Sub WriteCSVFile()
Dim i As Integer
Dim WS_Count As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(i)
PathName = "" & ThisWorkbook.Path & "\" & ws.Name & ".csv"
ws.Copy
ActiveWorkbook.SaveAs Filename:=PathName, _
FileFormat:=xlCSV, CreateBackup:=False
Next i
End Sub
Hope this helps
希望这可以帮助
