Excel Macro: Copy All Data on Excel Sheet, then Paste in Notepad, then Copy all data in Notepad back to Excel?

[MEUserII]

I am trying to find out how to write an Excel macro that would allow me to: 1) copy all the data on my existing open worksheet, 2) Open a new Notepad file, and paste all the data in that new Notepad file, 3) Select all the pasted data in that Notepad file, and copy it back to Excel? What macro would allow me to do this?

 

[MEUserII]

Bump, does anyone know the answer to this Macro question as I am still pretty stuck on this?

 

[Leith Ross]

Hello MEUserII,

Do you really want to copy the worksheet to the clipboard and then paste the data into Notepad? Alternately, you can copy the worksheet data and then save it as a text file, which is much simpler. Here is a macro I wrote recently to save the worksheet's data to a CSV in Unicode format. This might be a good starting point.

Macro Code

' Written:  January 21, 2018
' Authpor:  Leith Ross
' Summary:  Copies all of the cells in the supplied range with
'           values to a new Unicode text file. Each row is terminated
'           with the specified new line character(s) and the fields
'           in each row are separated with the character(s) in the
'           string separator.
'
' NOTE:     If you open this file with Excel, you will need to run
'           TextToColumns to separate the data into the worksheet columns.




Private Declare PtrSafe Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)


Sub CreateUnicodeFile(ByVal File As String, ByRef Rng As Range, Optional Separator As String, Optional NewLine As String)


    Dim Bytes() As Byte
    Dim Divider As String
    Dim k       As Integer
    Dim n       As Long
    Dim Row     As Long
    Dim Text    As String
    
        ' Defaults: Separator = ",", NewLine = CrLf
        Separator = IIf(Separator = "", ",", Separator)
        NewLine = IIf(NewLine = "", vbCrLf, NewLine)
            
            ' Add the Unicode Byte Order Mark for little-endian.
            ReDim Bytes(1)
                Bytes(0) = 255
                Bytes(1) = 254
        
            For Row = 1 To Rng.Rows.Count
                
                For Col = 1 To Rng.Columns.Count
                    Text = Text & Rng.Cells(Row, Col)
                    If Col <> Rng.Columns.Count Then
                        Text = Text & Separator
                    End If
                Next Col
                
                Text = Text & NewLine
            Next Row
            
        Text = StrConv(Text, vbUnicode)
        n = Len(Text)
        
        k = UBound(Bytes)
        ReDim Preserve Bytes(k + n)
        CopyMemory Bytes(k + 1), ByVal Text, n
                    
        Open File For Binary Access Write As #1 
            Put #1 , , Bytes
        Close #1 
        
End Sub

Calling the MAcro

Sub MacroTest()


    Call CreateUnicodeFile("C:\Test\Unicode.csv", ActiveSheet.UsedRange)
    
End Sub

 

[Jon Peltier]

Let's go one question further than Leith has done ("Do you really want to copy the worksheet to the clipboard and then paste the data into Notepad?").

Why do you want to put the data into {either Notepad or a text file} and then put it back into Excel? Have you found that doing it manually somehow cleans up the data? What is messy about the data that this roundtrip to Notepad fixes? There are probably much easier ways than text files and/or other applications.