是否可以使用VBA中的FileSystemObject读写csv文件?
当然可以。
基本语法,例如
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)
将使用 FSO
创建/打开一个 CSV
文件。
随后,可以通过向其写入内容来修改该 CSV
文件。
虽然这是一个 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
看起来是可以的。
FileSystemObject(FSO)提供了一个API,可访问Windows文件系统,提供对文件、驱动器、文本流等的访问。 FSO嵌入在Microsoft Scripting运行时中,并可用于独立应用程序(例如使用Visual Basic编码),用于使用VBScript或JScript的网页设计人员以及使用Visual Basic for Applications(VBA)的Microsoft Office应用程序的用户。
一些参考资料:
Set objTF = objFSO.OpenTextFile("C:\test\myfile.csv", 8)
这一行后,程序才能正常运行。在我选择的任何文件夹中都会出现 70 - Permission Denied 错误。 - chiliNUT