Create text Files from every row in an Excel spreadsheet

by

@nutsch’s answer is perfectly fine and should work 99.9% of the time. In the rare occasion that FSO is not available, here’s a version that doesn’t have a dependency. As is, it does require that the source worksheet doesn’t have any blank rows in the content section.

Sub SaveRowsAsCSV()

Dim wb As Excel.Workbook, wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim r As Long, c As Long

    Set wsSource = ThisWorkbook.Worksheets("worksheet")

    Application.DisplayAlerts = False 'will overwrite existing files without asking

    r = 1
    Do Until Len(Trim(wsSource.Cells(r, 1).Value)) = 0
        ThisWorkbook.Worksheets.Add ThisWorkbook.Worksheets(1)
        Set wsTemp = ThisWorkbook.Worksheets(1)

        For c = 2 To 7
            wsTemp.Cells((c - 1) * 2 - 1, 1).Value = wsSource.Cells(r, c).Value
        Next c

        wsTemp.Move
        Set wbNew = ActiveWorkbook
        Set wsTemp = wbNew.Worksheets(1)
        'wbNew.SaveAs wsSource.Cells(r, 1).Value & ".csv", xlCSV 'old way
        wbNew.SaveAs "textfile" & r & ".csv", xlCSV 'new way
        'you can try other file formats listed at http://msdn.microsoft.com/en-us/library/office/aa194915(v=office.10).aspx
        wbNew.Close
        ThisWorkbook.Activate
        r = r + 1
    Loop

    Application.DisplayAlerts = True

End Sub

More Related Contents:

  1. VBA check if file exists
  2. How to highlight selected text within excel
  3. How to convert to percentage [closed]
  4. VBA – Search and replace with increment number
  5. How to modify a text file?
  6. Is there any event that fires when keys are pressed when editing a cell?
  7. Excel: last character/string match in a string
  8. Does the VBA “And” operator evaluate the second argument when the first is false?
  9. Excel UNIQUE Across Columns
  10. Split cell values into multiple rows and keep other data
  11. Save each sheet in a workbook to separate CSV files
  12. Is VBA an OOP language, and does it support polymorphism?
  13. Export each sheet to a separate csv file
  14. What are .NumberFormat Options In Excel VBA?
  15. How to construct Dataframe from a Excel (xls,xlsx) file in Scala Spark?
  16. Detect if range is empty
  17. Checking if a worksheet-based checkbox is checked
  18. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  19. Excel: Searching for multiple terms in a cell
  20. Extract Data from Word Document to an Excel SpreadSheet
  21. Excel: Use a cell value as a parameter for a SQL query
  22. VBA equivalent to Excel’s mod function
  23. Convert from US to UK format
  24. Pointers needed for speeding up nested loop macro in VBA
  25. Comparing two columns on sheet1 to two other columns on sheet2 and returning another column in sheet 2.
  26. ‘1004’: “The sort reference is not valid.”
  27. Select entire column in table using Excel VBA
  28. Create hyperlink to another sheet
  29. Reducing WithEvent declarations and subs with VBA and ActiveX
  30. How to run a string as a command in VBA

Leave a Comment