reading and writing a csv file using FileSystemObject

by

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

More Related Contents:

  1. Loop Through All Subfolders Using VBA [duplicate]
  2. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  3. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  4. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  5. How to create a separate CSV file from VBA?
  6. to make this code simpler
  7. How to create and write to a txt file using VBA
  8. Outlook VBA – Run a code every half an hour
  9. Excel VBA deleting rows in a for loop misses rows
  10. Can I iterate through all Outlook emails in a folder including sub-folders?
  11. What is the lifetime of a global variable in excel vba?
  12. Embed picture in outlook mail body excel vba
  13. How to check for empty array in vba macro [duplicate]
  14. Where does VBA Debug.Print log to?
  15. VBA Adding a class to a collection [duplicate]
  16. MS Access VBA Error: Run time error ’70’ Permission Denied
  17. What is the function of FormulaR1C1?
  18. Compiler Error: User-defined types not defined
  19. How to send files via HTTP_POST with Excel using VBA?
  20. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  21. How to Add Date Picker To VBA UserForm
  22. Vba macro to copy row from table if value in table meets condition
  23. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  24. Set a cell value from a function
  25. What are the differences between VBA 6.0 and VBA 7.0?
  26. How to determine if a date falls on the weekend?
  27. Overflow when multiplying Integers and assigning to Long
  28. How to get the procedure or function name at runtime?
  29. Copy an array reference in VBA
  30. What does the To and Step mean in VBA?

Leave a Comment