使用FileSystemObject读取和写入csv文件 [英] reading and writing a csv file using FileSystemObject

问题描述

是否可以使用VBA中的FileSystemObject读取和写入csv文件？

解决方案

基本语法如

 设置objFSO = CreateObject（scripting.filesystemobject） 
'创建一个csv文件
设置objTF = objFSO.createtextfile（C：\test\myfile.csv，True，False）
'打开一个具有写入能力的现有csv文件
 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工作表
 Dim lRow As Long 
 Dim lCol As Long 
 Dim strTmp As String 
 Dim lFnum As Long 
 
设置objFSO = CreateObject（scripting.filesystemobject）
设置objTF = objFSO.createtextfile（sFilePath，True，False）
 
对于每个ws在ActiveWorkbook.Worksheets 
'测试该工作表已被使用
设置rng1 = ws.UsedRange 
如果Not rng1 is Nothing那么
'只能将多单元格范围写入2D数组
如果rng1.Cells.Count> 1 Then 
 X = ws.UsedRange.Value2 
'代码TRANSPOSES COLUMNS和ROWS按列写入字符串
对于lCol = 1到UBound（X，2）
'在循环之外写入初始值
 strTmp = IIf（InStr（X（1，lCol），strDelim）> 0，& X（1,1Col）& 1，lCol））
 For lRow = 2 To UBound（X，1）
'concatenate long string& （短字符串，短字符串）
 strTmp = strTmp& （strDelim& IIf（InStr（X（lRow，lCol），strDelim）> 0，& X（lRow，lCol）& $ b Next lRow 
每行写入CSV 
 objTF.writeline strTmp 
下一个lCol 
否则
 objTF.writeline IIf（InStr（ws.UsedRange.Value， strDelim）> 0，& ws.UsedRange.Value&，ws.UsedRange.Value）
结束如果
结束如果
接下来ws 
 
 objTF.Close 
设置objFSO =没有
 MsgBoxDone！，vbOKOnly 
 
 End Sub 
  

Is it possible to read and write csv files using FileSystemObject in VBA?

解决方案

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 CSV with FSO.

The CSV can then be modified by writing to it

While this is an Excel example you can use the same technique to write records from Outlook, Access, Word etc

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读取和写入csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！