from excel to notepad

[jenshay]

can you export multiple ranges from one excel worksheet into separate notepad files all at once using a formula or a macro? a4:c26 sheet3 into one notepad file, f4:h26 sheet3 into a second notepad file, etc.

 

[zhinghur]

Hi,

You don't need to change the WriteRangeToTextFile just the ExportToNotePad. Where are the addresses that you want written to text file stored? Are they on your sheet along with the file names?

This modified code will export range A4:C26 to each of the file name specified in range B1:AO1

Sub ExportToNotepad()
    Dim rngEachCell As Range


    For Each rngEachCell In Sheets("Sheet2").Range("B1:AO1").Cells

        WriteRangeToTextFile Range("A4:C26"), rngEachCell.Value, vbTab
        Shell "notepad.exe " & rngEachCell.Value, vbMaximizedFocus

    Next rngEachCell

End Sub


Sub WriteRangeToTextFile(Source As Range, Path As String, Delimiter As String)
    Dim oFSO As Object
    Dim oFSTS As Object
    Dim lngRow As Long, lngCol As Long


    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFSTS = oFSO.CreateTextFile(Path, True)

    For lngRow = 1 To Source.Rows.Count

        For lngCol = 1 To Source.Columns.Count

            If lngCol = Source.Columns.Count Then
                oFSTS.Write Source.Cells(lngRow, lngCol).Text & vbCrLf
            Else
                oFSTS.Write Source.Cells(lngRow, lngCol).Text & Delimiter
            End If

        Next lngCol

    Next lngRow

    oFSTS.Close

    Set oFSTS = Nothing
    Set oFSO = Nothing

End Sub

Hi All,

This code works well to export data into text file for day 1. However, for day 2, I want to append the excel data in the already created text file created of day 1. Could anyone help please ?