Export data to text file (change macro)

[Browning Zed]

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:

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

 

[bobsan42]

Try this:

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.

 

[Browning Zed]

Unfortunately, an error occurs:
Run-time error '424':
Object required

 

[bobsan42]

Unfortunately, an error occurs:
Run-time error '424':
Object required

Where?

 

[bobsan42]

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.

For each sh in thisworKbook.worksheets

 

[Browning Zed]

Thanks a lot, it works great!