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.

 

[jenshay]

that is the ticket; thank you very much for your time and help. i could not have done it without you

 

[dk]

No worries mate

 

[jenshay]

the text in the cells range b1:ao1 sheet2(range is constant) are the names of the wanted notepad files (always up to 40 of them and changing daily). is there a way to incorporate the text into the script written by dk where you put the writerangetotextfile and the shelling"file1.txt" etc names

so that your notepad text file name 1"file1.txt" is cell text of b1sheet2
notepad text file name 2 is cell text of c1sheet2
and so forth without having to copy them all into the code

 

[DRJ]

instead of this

Shell "notepad.exe h:\temp\file1.txt"

try this

Shell "notepad.exe h:\temp\" & sheets("Sheet2").cells(2,1).value & ".txt"

the cells function has the format cells(column number, row number)

you can set up a loop for multiple columns like

for x = 2 to 10

... cells(x,1) ...

next x

Jacob

 

[jenshay]

i have tried to work with what you have written and i get either error messages or a window popping up saying that it cannot find the file, do i want to create a new file? if i say yes it opens but has no data in it

past the explanation of the cell function, i don't understand the looping part, or maybe not at all

do i need to copy the new shell code into the writerangetotextfile code as well, should they be the same or will it continue to read \file1.txt?

 

[dk]

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

 

[jenshay]

hello

text to go into notepad files are on sheet3
names of notepad files are on sheet2

i want notepad files named from sheet2 b1:ao1 and i want the text from sheet3 a4:c26 in notepad file named the text in sheet2 b1; text from sheet3 f4:h26 in notepad file named the text in sheet2 c1...

 

[dk]

Have you had a go at modifying the code yourself? I think I've provided enough information for you to go on. Doing what you need shouldn't involve too much work.

Regards,
Dan

 

[forbesy]

Thanks DRJ, I based my solution off yours. I have saved with ".dat" extension as this is required by program with which I interact with but should be able to replace this with ".txt".

'save text from named range "test_range" as text file with .dat extension
Sub SaveAsDat()

Dim r As Excel.Range
Dim x As Integer
Dim strData As String

'open it and erase everything
Open ThisWorkbook.Path & "\test.dat" For Output As #1
Print #1, ""
Close #1

'open it and append all desired data
For Each r In Range("test_range")

x = r.Column

'if x is first column in the named range, then write cell contents to string variable
If x = Range("Test_Range").Columns(1).Column Then
strData = r.Value
Else
strData = strData & vbTab & r.Value
End If

'if x = last column in named range, write strData to .dat file
If x = Range("Test_Range").Columns(1).Column + Range("Test_Range").Columns.Count - 1 Then

Open ThisWorkbook.Path & "\test.dat" For Append As #1
Print #1, strData
Close #1

End If

Next r

End Sub

 

[TGY]

.