vba 使用 FileSystemObject 读取和写入 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9442215/
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
reading and writing a csv file using FileSystemObject
提问by user793468
Is it possible to read and write csv files using FileSystemObject in VBA?
是否可以在 VBA 中使用 FileSystemObject 读取和写入 csv 文件？
回答by brettdj
It certainly is.
那当然是。
Basic syntax such as
基本语法如
Set objFSO = CreateObject("scripting.filesystemobject")
'create a csv file
Set objTF = objFSO.createtextfile("C:\test\myfile.csv", True, False)
'open an existing csv file with writing ability
Set objTF = objFSO.OpenTextFile("C:\test\myfile.csv", 8)
will create/open a CSVwith FSO.
将创建/打开一个CSV带FSO。
The CSVcan then be modified by writing to it
该CSV可再通过写它被修改
While this is an Excel example you can use the same technique to write records from Outlook, Access, Word etc
虽然这是一个 Excel 示例，但您可以使用相同的技术从 Outlook、Access、Word 等写入记录
Const sFilePath = "C:\test\myfile.csv"
Const strDelim = ","
Sub CreateCSV_FSO()
Dim objFSO
Dim objTF
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim strTmp As String
Dim lFnum As Long
Set objFSO = CreateObject("scripting.filesystemobject")
Set objTF = objFSO.createtextfile(sFilePath, True, False)
For Each ws In ActiveWorkbook.Worksheets
'test that sheet has been used
Set rng1 = ws.UsedRange
If Not rng1 Is Nothing Then
'only multi-cell ranges can be written to a 2D array
If rng1.Cells.Count > 1 Then
X = ws.UsedRange.Value2
'The code TRANSPOSES COLUMNS AND ROWS by writing strings column by column
For lCol = 1 To UBound(X, 2)
'write initial value outside the loop
strTmp = IIf(InStr(X(1, lCol), strDelim) > 0, """" & X(1, lCol) & """", X(1, lCol))
For lRow = 2 To UBound(X, 1)
'concatenate long string & (short string with short string)
strTmp = strTmp & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
Next lRow
'write each line to CSV
objTF.writeline strTmp
Next lCol
Else
objTF.writeline IIf(InStr(ws.UsedRange.Value, strDelim) > 0, """" & ws.UsedRange.Value & """", ws.UsedRange.Value)
End If
End If
Next ws
objTF.Close
Set objFSO = Nothing
MsgBox "Done!", vbOKOnly
End Sub
回答by Siva Charan
It seems possible.
似乎有可能。
The FileSystemObject (FSO) provides an API to access the Windows filesystem, providing access to files, drives, text streams etc. The FSO is embedded within the Microsoft Scripting run-time, and is available to stand-alone applications (coded using Visual Basic, for example), to web page designers using VBScript or JScript and to users of Microsoft Office applications using Visual Basic for Applications (VBA).
FileSystemObject (FSO) 提供一个 API 来访问 Windows 文件系统，提供对文件、驱动器、文本流等的访问。 FSO 嵌入在 Microsoft 脚本运行时中，可用于独立应用程序（使用 Visual Basic 编码例如）、使用 VBScript 或 JScript 的网页设计者以及使用 Visual Basic for Applications (VBA) 的 Microsoft Office 应用程序用户。
Some references:-
一些参考资料：-
Using The FileSystemObject With VB and VBA
在 VB 和 VBA 中使用 FileSystemObject
