Export data to text file (change macro)

Browning Zed

New Member
Joined
Aug 22, 2021
Messages
5
Hi All,

To save the data of a specific sheet to a text file (without exporting the top row), I use the macro posted below. In addition, when exporting data to a file, the macro also removes unnecessary quotes, this is important.
I have two questions about modifying this macro.
1. Is it possible to make the macro export all sheets of the book, not just the current sheet? Each sheet must be exported to a separate file, where the file name must match the sheet name.
2. Is it possible to add a condition if the first cell of any row is empty, then this row should not be exported to a file?
As a priority, I really need to solve the first question, but if manage to implement the solution to the second question, it will also be great.
Thank you in advance.

The macro (with minor changes) is taken from this thread:
VBA Code:
Sub ExportToText()


    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
    Dim rowCount As Long


    sFname = ThisWorkbook.Path & "\MyFile.txt"
    lFnum = xlCSV
    rowCount = 1


    Open sFname For Output As lFnum
    For Each rRow In ActiveSheet.UsedRange.Rows
        If rowCount > 1 Then
            For Each rCell In rRow.Cells
                sOutput = sOutput & rCell.Text & ";"
            Next rCell
            sOutput = Left(sOutput, Len(sOutput) - 1)
            Print #lFnum, sOutput
            sOutput = ""
        End If
        rowCount = rowCount + 1
    Next rRow
    Close #lFnum


End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Try this:
VBA Code:
Sub ExportToText()

Dim sh as worksheet
    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
    Dim rowCount As Long

    lFnum = xlCSV
For each sh in thisworbook.worksheets
sFname = ThisWorkbook.Path & "\" & sh.name & ".txt"
    rowCount = 1

    Open sFname For Output As lFnum
    For Each rRow In sh.UsedRange.Rows
        If rowCount > 1 and not isempty(rrow.cells(1,1).value) Then

            For Each rCell In rRow.Cells
                sOutput = sOutput & rCell.Text & ";"
            Next rCell
            sOutput = Left(sOutput, Len(sOutput) - 1)
            Print #lFnum, sOutput
            sOutput = ""
        End If
        rowCount = rowCount + 1
    Next rRow
    Close #lFnum
Next sh

End Sub
I apologize for any potential mistakes, but I'm typing on my phone.
 
Solution

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Aaah, like i said i'm typing on my phone,
But this type of errors you should learn to detect and correct yourself, if you are going to do this.
VBA Code:
For each sh in thisworKbook.worksheets
 

Forum statistics

Threads
1,143,839
Messages
5,721,093
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top