Pasted data into notepad not aligning properly

[kelly mort]

Sub OpenNotepadWithTempFileWithClipboardContent()


    Dim rngData As Range
    Dim strData As String
    Dim strTempFile As String


    ' copy some range values
    Set rngData = Sheet3.Range("A1:E15")
    rngData.Copy


    ' get the clipboard data
    ' magic code for is for early binding to MSForms.DataObject
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipBoard
        strData = .GetText
    End With


    ' write to temp file
    strTempFile = "D:\temp.txt"
    With CreateObject("Scripting.FileSystemObject")
        ' true to overwrite existing temp file
        .CreateTextFile(strTempFile, True).Write strData
    End With


    ' open notepad with tempfile
    Shell "cmd /c ""notepad.exe """ & strTempFile & """", vbHide


End Sub

Hi

I got this code that I am using to copy part of my data into notepad then open it afterwards.

It's working great.

Just that the alignment of the data is altered. The columns are not aligned properly. I have to do that manually.

Can someone fix that for me?

Thanks

 

[Akuini]

Can the code paste the text as they are displayed in the cells?

It's displaying dates differently than they appear in my cells.

Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1089462b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1089462-pasted-data-into-notepad-not-aligning-properly.html#post5237042[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] strPath [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]: strPath = [COLOR=brown]"D:\zz\try monospace.TXT"[/COLOR] [I][COLOR=seagreen]'Temp file path[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] intFF [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Integer[/COLOR]: intFF = FreeFile()
[COLOR=Royalblue]Dim[/COLOR] rString [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]: rString = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] intShow [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va


Open strPath [COLOR=Royalblue]For[/COLOR] Output [COLOR=Royalblue]As[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] 


[COLOR=Royalblue]ReDim[/COLOR] myArray([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] Selection.Rows.count, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] Selection.Columns.count)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]2[/COLOR])
        k = k + [COLOR=crimson]1[/COLOR]
        myArray(i, j) = Selection(k).[COLOR=Royalblue]Text[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]ReDim[/COLOR] va([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

[COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]2[/COLOR])
x = [COLOR=crimson]0[/COLOR]
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]1[/COLOR])
        [COLOR=Royalblue]If[/COLOR] x < Len(myArray(i, j)) [COLOR=Royalblue]Then[/COLOR] x = Len(myArray(i, j))
    [COLOR=Royalblue]Next[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(myArray, [COLOR=crimson]1[/COLOR])
        [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        va(i, [COLOR=crimson]1[/COLOR]) = myArray(i, j) & Space(x - Len(myArray(i, j)))
        [COLOR=Royalblue]Else[/COLOR]
        va(i, [COLOR=crimson]1[/COLOR]) = va(i, [COLOR=crimson]1[/COLOR]) & [COLOR=brown]" "[/COLOR] & myArray(i, j) & Space(x - Len(myArray(i, j)))
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] , va(i, 1)
    rString = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] 
intShow = Shell([COLOR=brown]"Notepad.exe "[/COLOR] & strPath, vbNormalFocus)

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[kelly mort]

@Akuini,

You are great! !!


Working great

With the selection I used code for that like:

With Sheet1
ReDim myArray(1 To . [A1:G20].Rows.count, ........
.........
End With

I hope that's cute.