Export multiple worksheets as separate text files in Excel

Tutorial

If there are large number of worksheets (tabs) in your excel file that you need to export as a separate text file, follow these guidelines. Note that the worksheet label will be used as file name for the text file with the .txt extension.

Before proceeding, check if you have enabled the macros, i.e., if you don't see DEVELOPER tab in you empty spreadsheet, click on FILE, OPTIONS and Customize Ribbon. You should see a check box on the right hand side, for the DEVELOPER tab, check it and click OK.

Click on DEVELOPER and then Macros, type in some name (eg. import_text), click create.

Paste the below code on the popped window:

  1. ' ---------------------- Directory Choosing Helper Functions -----------------------
  2. ' Excel and VBA do not provide any convenient directory chooser or file chooser
  3. ' dialogs, but these functions will provide a reference to a system DLL
  4. ' with the necessary capabilities
  5. Private Type BROWSEINFO ' used by the function GetFolderName
  6. hOwner As Long
  7. pidlRoot As Long
  8. pszDisplayName As String
  9. lpszTitle As String
  10. ulFlags As Long
  11. lpfn As Long
  12. lParam As Long
  13. iImage As Long
  14. End Type
  15.  
  16. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  17. Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
  18. Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
  19. Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
  20.  
  21. Function GetFolderName(Msg As String) As String
  22. ' returns the name of the folder selected by the user
  23. Dim bInfo As BROWSEINFO, path As String, r As Long
  24. Dim X As Long, pos As Integer
  25. bInfo.pidlRoot = 0& ' Root folder = Desktop
  26. If IsMissing(Msg) Then
  27. bInfo.lpszTitle = "Select a folder."
  28. ' the dialog title
  29. Else
  30. bInfo.lpszTitle = Msg ' the dialog title
  31. End If
  32. bInfo.ulFlags = &H1 ' Type of directory to return
  33. X = SHBrowseForFolder(bInfo) ' display the dialog
  34. ' Parse the result
  35. path = Space$(512)
  36. r = SHGetPathFromIDList(ByVal X, ByVal path)
  37. If r Then
  38. pos = InStr(path, Chr$(0))
  39. GetFolderName = Left(path, pos - 1)
  40. Else
  41. GetFolderName = ""
  42. End If
  43. End Function
  44. '---------------------- END Directory Chooser Helper Functions ----------------------
  45.  
  46. Public Sub DoTheExport()
  47. Dim FName As Variant
  48. Dim Sep As String
  49. Dim wsSheet As Worksheet
  50. Dim nFileNum As Integer
  51. Dim csvPath As String
  52.  
  53.  
  54. Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _
  55. "Export To Text File")
  56. 'csvPath = InputBox("Enter the full path to export CSV files to: ")
  57.  
  58. csvPath = GetFolderName("Choose the folder to export CSV files to:")
  59. If csvPath = "" Then
  60. MsgBox ("You didn't choose an export directory. Nothing will be exported.")
  61. Exit Sub
  62. End If
  63.  
  64. For Each wsSheet In Worksheets
  65. wsSheet.Activate
  66. nFileNum = FreeFile
  67. Open csvPath & "\" & _
  68. wsSheet.Name & ".csv" For Output As #nFileNum
  69. ExportToTextFile CStr(nFileNum), Sep, False
  70. Close nFileNum
  71. Next wsSheet
  72.  
  73. End Sub
  74.  
  75.  
  76.  
  77. Public Sub ExportToTextFile(nFileNum As Integer, _
  78. Sep As String, SelectionOnly As Boolean)
  79.  
  80. Dim WholeLine As String
  81. Dim RowNdx As Long
  82. Dim ColNdx As Integer
  83. Dim StartRow As Long
  84. Dim EndRow As Long
  85. Dim StartCol As Integer
  86. Dim EndCol As Integer
  87. Dim CellValue As String
  88.  
  89. Application.ScreenUpdating = False
  90. On Error GoTo EndMacro:
  91.  
  92. If SelectionOnly = True Then
  93. With Selection
  94. StartRow = .Cells(1).Row
  95. StartCol = .Cells(1).Column
  96. EndRow = .Cells(.Cells.Count).Row
  97. EndCol = .Cells(.Cells.Count).Column
  98. End With
  99. Else
  100. With ActiveSheet.UsedRange
  101. StartRow = .Cells(1).Row
  102. StartCol = .Cells(1).Column
  103. EndRow = .Cells(.Cells.Count).Row
  104. EndCol = .Cells(.Cells.Count).Column
  105. End With
  106. End If
  107.  
  108. For RowNdx = StartRow To EndRow
  109. WholeLine = ""
  110. For ColNdx = StartCol To EndCol
  111. If Cells(RowNdx, ColNdx).Value = "" Then
  112. CellValue = ""
  113. Else
  114. CellValue = Cells(RowNdx, ColNdx).Value
  115. End If
  116. WholeLine = WholeLine & CellValue & Sep
  117. Next ColNdx
  118. WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
  119. Print #nFileNum, WholeLine
  120. Next RowNdx
  121.  
  122. EndMacro:
  123. On Error GoTo 0
  124. Application.ScreenUpdating = True
  125.  
  126. End Sub