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.

 

[DRJ]

Hi

You can do something like this to make text files and write to them.


Sub test()

Open ThisWorkbook.Path & "\File1.txt" For Append As #1
Print #1, Sheets(1).Range("A1").Value
Close #1

Open ThisWorkbook.Path & "\File2.txt" For Append As #1
Print #1, Sheets(1).Range("A2").Value
Close #1

Open ThisWorkbook.Path & "\File3.txt" For Append As #1
Print #1, Sheets(1).Range("A3").Value
Close #1

End Sub


Hope this helps

Jacob

 

[Egress1]

You can use the OPEN statement in VBA to accomplish this something like

Dim MyCol As Integer, MyRow As Integer
Open "C:\DATA\File1.txt" For Output As #1
Open "C:\DATA\File2.txt" For Output As #2
For MyRow = 4 To 26
For MyCol = 1 To 3
Output #1, Cells(MyRow, MyCol)
Output #2, Cells(MyRow, MyCol +5)
Next MyCol
Next MyRow
Close #1
Close #2

HTH

 

[jenshay]

notepad files have been created from the first script suggested, but they are blank.

how does the respective data get pasted into the files without having to paste each one?

 

[dk]

Hi,

You can open Notepad by using the Shell method and you can also pass a filename to it e.g.

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

The method I used was to create a text file based on each range and then use the Shell method to open that text file with Notepad. See if the code below is of any use.

Sub ExportToNotepad()

    WriteRangeToTextFile Range("A4:C26"), "H:\temp\file1.txt", ","
    WriteRangeToTextFile Range("F4:H26"), "H:\temp\file2.txt", ","


    Shell "notepad.exe h:\temp\file1.txt", vbMaximizedFocus
    Shell "notepad.exe h:\temp\file2.txt", vbMaximizedFocus

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) & vbCrLf
            Else
                oFSTS.Write Source.Cells(lngRow, lngCol) & Delimiter
            End If

        Next lngCol

    Next lngRow

    oFSTS.Close

    Set oFSTS = Nothing
    Set oFSO = Nothing

End Sub

 

[DRJ]

Where I put "Print #1, Sheets(1).Range("A1").Value " change that to what you want to put in the cell.

Lets see what we can do here to get the values from "a4:c26"

Sub Test()

Dim MyNotes as String
Dim x as Integer

For x = 4 to 26
MyNotes = MyNotes & Sheets("Sheet3").range("A" & x).value & vbtab & Sheets("Sheet3").range("B" & x).value & vbtab & Sheets("Sheet3").range("C" & x).value & vbcrlf
next x

Open ThisWorkbook.Path & "\File1.txt" For Append As #1
Print #1, MyNotes
Close #1

end sub

 

[jenshay]

the one from dan worked except for that there are commas and i want tabs between the data in the columns.

i can't seem to get the others to work, notepad files are being created, but no data transferred in them.

 

[jenshay]

one other thing is that the format of the numerical cells have been changed to number with no decimal places from number with two decimal places, but when the data is transferred using dan's script, the numbers became changed back to numbers with two decimal places. is there anyway to keep the numbers as integers with zero decimals places?

 

[jenshay]

jacob's is working with the correct tabs, but one last problem persists, the original format of the cells are number with 2 decimal places that i have changed to the format of number with no decimal places, but when the data is transferred to notepad the 2 decimal places returns. reformatting doesn't seem to do the trick. when the cursor is over the cell the original number still appears.

 

[dk]

Hi,

This amended code will use tabs instead of commas and will use the value as it is formatted in the cell.

Sub ExportToNotepad()

    WriteRangeToTextFile Range("A4:C26"), "H:\temp\file1.txt", vbTab
    WriteRangeToTextFile Range("F4:H26"), "H:\temp\file2.txt", vbTab


    Shell "notepad.exe h:\temp\file1.txt", vbMaximizedFocus
    Shell "notepad.exe h:\temp\file2.txt", vbMaximizedFocus

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