Pasted data into notepad not aligning properly

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Code:
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
.
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.

Code:
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
 
Upvote 0
@Logit,

Your code is cool:
Code:
Index Fruit Fish Animal
1. Apple Tuna Dog
2. Orange Tilapia Cat

I want the it aligned like:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.


Code:
[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]
 
Upvote 0
Can the code paste the text as they are displayed in the cells?

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

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top