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

 

[Logit]

.
The following macro with copy the range that is highlighted then place it into Notepad in an aligned manner.

Review the comments in the code. At present, the delimiter is a space, but you can use a comma or other symbol as well.

I hesitate to interact with your macro due to the REGISTRY string present. Hopefully you can implement this macro for your
purposes or someone else might be able to assist.

Public Sub OutPutSelectionToNotePad()
Dim strPath As String: strPath = "C:\Users\My\Desktop\TempFile.txt" 'Temp file path
Dim intFF As Integer: intFF = FreeFile()
Dim rString As String: rString = ""
Dim intShow As Variant
Dim i As Long, j As Long


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


For i = 1 To UBound(myArray, 1)
    For j = 1 To UBound(myArray, 2)
        rString = rString & IIf(Len(rString) > 0, " " & myArray(i, j), myArray(i, j)) ' change separator as needed; located between quote symbols after  > 0,
    Next j
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] , rString & vbCrLf
    rString = ""
Next i


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

 

[kelly mort]

@Logit,

Your code is cool:

Index Fruit Fish Animal
1. Apple Tuna Dog
2. Orange Tilapia Cat

I want the it aligned like:

Index Fruit    Fish    Animal
1.    Apple    Tuna    Dog
2.    Orange   Tilapia Cat

The code I posted came close to doing that as I use "David Libre" font.

I wish there is a work around for this.

 

[Logit]

.
http://www.databison.com/export-excel-as-fixed-width-text-file-format-using-vba/


You can also save the selected range as .PRN or TAB DELIMITED.

 

[kelly mort]

I gave the code a look i tried it. It's clearing the selection instead.

I am still reading it to understand it.

If someone understands it and can tweak it for me , please help.

 

[Akuini]

The code I posted came close to doing that as I use "David Libre" font.

Is "David Libre" a monospace font? As far as I know, aligning text in a text editor can only be done if the font is monospace.

 

[kelly mort]

Is "David Libre" a monospace font? As far as I know, aligning text in a text editor can only be done if the font is monospace.

I have no idea about the monospace font. Just that that font works fine for the tests I ran.

 

[Akuini]

A monospaced font, also called a fixed-pitch, fixed-width, or non-proportional font, is a font whose letters and characters each occupy the same amount of horizontal space.
https://en.wikipedia.org/wiki/Monospaced_font

Example for monospace font: Courier New, Consolas, Fixedsys

In my system the default font in Notepad is Consolas.
Try this :
I modified Logit's code in post #2 (credit to him).
Just select the range & run the code.
Set the font in notepad to Consolas.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1089462a()
[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]
[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] 
myArray = Selection.Value
[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,

This your fix is very powerful!

I am really excited.

Thanks to you @Akuini and @Logit.

For now I am cool to go.

 

[kelly mort]

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

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