Thread: Code to Export Excel Data to Text File

I need some script to export excel data to a text file. I have found a lot of these online, but my situation is a little tricky. I have 100s of excel sheets with a variable number of groups. These goups consist of 5 columns full of data and between each group there is a blank column. For example, I could have 2 groups as shown below (letters are to show columns).

A B C D E F G H I J K
1 4 7 1 4 1 2 3 4 5
2 5 8 2 5 6 7 8 9 1
3 6 9 3 6 2 3 4 5 6

Here column F is blank and I want the data exported to a text file so each group goes vertically under the previous group. So the above excel data would be exported to a text file as follows:
1 4 7 1 4
2 5 8 2 5
3 6 9 3 6
1 2 3 4 5
6 7 8 9 1
2 3 4 5 6

Each of my excel worksheets has a variable number of groups and each group does contain a blank column between it. I need a script that would take an excel file with a variable number of these groups and export the data to a text file like I described above. I am new to this and not very farmiliar with it and therefore do not really know how to go about doing this. I am trying to save a lot of time rather than manually going into each worksheet and copying the data to a text file following the above guidelines.

Any help is appreciated. thanks.

Hello Ashkash,

Are all your data numbers?

Sincerely,
Leith Ross

Mostly numbers, but there are cells that also contain characters.

Hello Ashkash,

Are all the worksheets in the same workbook? Are you using Office 2007?

Sincerely,
Leith Ross

Yes, all the worksheets are in the same workbook. Using Excel 2003.

Hello Ashkash,

I am working on the code now, and almost have it working. It shouldn't take me much longer to finish it.

Sincerely,
Leith Ross

Hello Ashkash,

Here is the macro. You will need to change the text file name (marked in blue) to what you want. Place this in a standard VBA Module.

Sub CreateTextFile()

  Const ForAppending As Long = 8
  Const DefaultFormat As Long = -2
  Const UnicodeFormat As Long = -1
  Const AsciiFormat As Long = 0

    Dim C As Long, R As Long
    Dim FileName As String
    Dim Filepath As String
    Dim fso As Object
    Dim LastCol As Long
    Dim LastRow As Long
    Dim Rng As Range
    Dim StartCol As Long
    Dim StartRow As Long
    Dim TxtData As String
    Dim TxtFile As Object
    Dim Wks As Worksheet

      StartRow = 1
      StartCol = 1
      
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set TxtFile = fso.OpenTextFile("c:\TestFile.txt", ForAppending, DefaultFormat)
      
      For Each Wks In ThisWorkbook.Worksheets
        C = StartCol
        LastCol = Wks.Cells(StartRow, Columns.Count).End(xlToLeft).Column
        LastRow = Wks.Cells(Rows.Count, StartCol).End(xlUp).Row
          For R = StartRow To LastRow
            Set Rng = Wks.Range(Cells(StartRow, C), Cells(LastRow, C))
              If WorksheetFunction.CountIf(Rng, "<>*") <> 0 Then
                For C = StartCol To LastCol
                  TxtData = TxtData & Wks.Cells(R, C).Value & vbTab
                Next C
                TxtFile.WriteLine (TxtData)
                TxtData = ""
              End If
          Next R
      Next Wks
      
  TxtFile.Close
  Set fso = Nothing
  Set TxtFile = Nothing
  
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

Sincerely,
Leith Ross

Just tried the code and I get the following error when running the macro:
Runtime error '1004':
Method 'Range of object' _Worksheet' failed

Attached is the excel file I am testing it with. Any ideas? thanks.

I am still getting the same error as above, but it does create the text file but it is not formatted to the specifications. Could this be because it is not going through the code correctly because of the error? If you look at the attached text excel file in my previous post, I need the data in columns A-E exported to the text file and then the text file should go to the next line and export columns G-K and then it should go to the next line and export columns M-Q and so on. The test excel file only has 3 five column groups, but the number of these 5 column groups in my data is variable. So each group of 5 columns is exported to the text file and being vertically stacked in format. The code might be doing this, but maybe because of the error it is not executing as it should. Attached is an example of how the created text file should look for the test excel data.

Hello Ashkash,

This turned about to be more difficult than I expected. Finally have it working. You can run the macro using CTRL+SHIFT+S. This will automatically save every worksheet in the workbook using the custom format. Here is the macro code. It is already installed in the attached workbook.

Sub CopyToTextFile(Wks As Worksheet)

  Const ForAppending As Long = 8
  Const AsciiFormat As Long = 0
  
  Dim C As Long
  Dim ColArray() As Long
  Dim BlankCols As Range
  Dim N As Long
  Dim R As Long
  Dim Rng As Range
  
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set TxtFile = fso.OpenTextFile("c:\TestFile.txt", ForAppending, DefaultFormat)
    
    With Wks.UsedRange
      StartRow = .Row
      LastRow = .Rows.Count + StartRow - 1
      StartCol = .Column
      LastCol = .Columns.Count + StartCol - 1
      On Error Resume Next
        Set BlankCols = .SpecialCells(xlCellTypeBlanks)
          If Err.Number = 1004 Then
             Err.Clear
             GoTo NoMoreBlanks
          End If
      On Error GoTo 0
    End With
    
      For Each Rng In BlankCols.Areas
        ReDim Preserve ColArray(N)
          ColArray(N) = Rng.Column
        N = N + 1
      Next Rng
        
        For N = 0 To UBound(ColArray)
          StopCol = ColArray(N) - 1
          Set Rng = Wks.Range(Cells(StartRow, StartCol), Cells(LastRow, StopCol))
             GoSub WriteDataToFile
          StartCol = ColArray(N) + 1
         Next N
         
NoMoreBlanks:
         If Err.Number <> 0 Then GoTo Finished
         Set Rng = Wks.Range(Cells(StartRow, StartCol), Cells(LastRow, LastCol))
         GoSub WriteDataToFile
         GoTo Finished
         
WriteDataToFile:
        For R = 1 To Rng.Rows.Count
          For C = 1 To Rng.Columns.Count
            TxtData = TxtData & Rng.Cells(R, C) & vbTab
          Next C
          TxtData = Left(TxtData, Len(TxtData) - 1)
            TxtFile.WriteLine (TxtData)
          TxtData = ""
        Next R
    Return
      
Finished:
  TxtFile.Close
  Set fso = Nothing
  Set TxtFile = Nothing
  
End Sub

Sub SaveWorksheets()

  Dim Wks As Worksheet
    
   Application.ScreenUpdating = False
   
      For Each Wks In ThisWorkbook.Worksheets
        Wks.Activate
        CopyToTextFile Wks
      Next Wks
    
   Application.ScreenUpdating = True
    
End Sub

Sincerely,
Leith Ross

Thanks a lot. Works prefectly. This will definetly save me a lot of time. I really appreciate all your help. thanks again.

Sub teset()
Dim r As Range, txt1 As String, txt2 As String
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
    With Application.WorksheetFunction
        txt1 = txt1 & vbCrLf & Join(.Transpose(.Transpose(r.Resize(,5).Value)),vbTab)
        txt2 = txt2 & vbCrLf & Join(.Transpose(.Transpose(r.Offset(,6).Resize(,5).Value)),vbTab)
    End With
Next
Open "c:\test.txt" For Output As #1
      Print #1, Mid$(txt1,2) & txt2
Close #1
End Sub

I think one article is very useful to export data to Text file. It introduces us a simple way to complete the exportation quickly. If you are interested in it, you can view it on
http://www.codeproject.com/KB/cs/Exc...ExportWiz.aspx

Hello
I have a work book with 60 sheets each sheet has alot of rows
I would like to export each row as a text file, nameing the text file from column A placing everything in the row into the text file.
in total it will be over 32000 text files way to many to do manualy

can anyone offer some generious help it would be greatly appriciated.
thanks