如何从 VBA 创建单独的 CSV 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8419828/
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
How to create a separate CSV file from VBA?
提问by BuZz
I need to output some results as a .csvfile, that gets parsed later on by another process. In order to produce these results, I have a huge workbook containing all the macros and functions that I need.
我需要将一些结果作为.csv文件输出，稍后由另一个进程解析。为了产生这些结果，我有一个巨大的工作簿，其中包含我需要的所有宏和函数。
- Is it possible to "create" a separate
.csvfile fromVBA? - Is it possible to use
VBAfeatures to write into it instead of just writing in a "raw textual" approach ?
- 是否可以“创建”一个单独的
.csv文件VBA？ - 是否可以使用
VBA功能写入其中而不是仅以“原始文本”方法写入？
Thank you :)
谢谢 ：）
回答by Tony Dallimore
Is something like this what you want?
这样的事情是你想要的吗？
Option Explicit
Sub WriteFile()
Dim ColNum As Integer
Dim Line As String
Dim LineValues() As Variant
Dim OutputFileNum As Integer
Dim PathName As String
Dim RowNum As Integer
Dim SheetValues() As Variant
PathName = Application.ActiveWorkbook.Path
OutputFileNum = FreeFile
Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum
Print #OutputFileNum, "Field1" & "," & "Field2"
SheetValues = Sheets("Sheet1").Range("A1:H9").Value
ReDim LineValues(1 To 8)
For RowNum = 1 To 9
For ColNum = 1 To 8
LineValues(ColNum) = SheetValues(RowNum, ColNum)
Next
Line = Join(LineValues, ",")
Print #OutputFileNum, Line
Next
Close OutputFileNum
End Sub
Don't forget you will need to put quotes around any field containing a comma.
不要忘记您需要在包含逗号的任何字段周围加上引号。
回答by user3707264
Tony's answer generally works but doesn't handle the case where your text contains commas or quotes. You may prefer to use Workbook.SaveAs method.
托尼的回答通常有效，但不处理您的文本包含逗号或引号的情况。您可能更喜欢使用 Workbook.SaveAs 方法。
Here is an example if you want to save the content of the Sheet1 as a separated csv file.
如果您想将 Sheet1 的内容保存为单独的 csv 文件，这里是一个示例。
Sub create_csv()
Dim FileName As String
Dim PathName As String
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
FileName = "filename.csv"
PathName = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs FileName:=PathName & "\" & FileName, _
FileFormat:=xlCSV, CreateBackup:=False
End Sub
Imagine that your Sheet1 contains :
想象一下，您的 Sheet1 包含：
lorem ipsum
lore,m ips"um"
逻辑推理
绝杀，我 ips“嗯”
The output csv file will be :
输出 csv 文件将是：
lorem,ipsum
"lore,m","ips""um"""
定理，假说
“绝杀，米”，“ips”“嗯”“”
回答by jatanp
You may write a macro like to save the current workbook (opened excel file) in CSV from VBA:
您可以编写一个宏，例如从 VBA 以 CSV 格式保存当前工作簿（打开的 excel 文件）：
ActiveWorkbook.SaveAs Filename:="C:\Book1.csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False
