VBA code to pasting to Notepad, without tabs

[blondeambition]

Hi,
I need help with figuring out vba code to paste data from Excel to Notepad, without the spaces in between each word/number. I would like to replace the space with these, "|".

Any ideas?
Thanks.

 

[Andrew Poulsom]

What do you mean by "any notepad file"? It has to have a name and a path.

 

[blondeambition]

Sorry Andrew, please bear with me. This is the coding I'm using to open Notepad and to paste the data from Excel. Only problem is, as you know, I need to replace the tabs (spaces between) with the " | ".


Range("A1:EX100").Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"

 

[Andrew Poulsom]

You don't need to open Notepad. Excel is capable of reading and writing text files.

 

[DocAElstein]

Sorry Andrew, please bear with me. This is the coding I'm using to open Notepad and to paste the data from Excel. Only problem is, as you know, I need to replace the tabs (spaces between) with the " | ". ........

Hi „blondeambition“,
. The Text File given by Andrew Poulson here
http://www.mrexcel.com/forum/excel-questions/213591-pipe-delimited-files.html
became something of a standard I use when answering threads requiring a simple quick write of an Excel sheet to a Text file
. This simple adaption below will either create or replace a File with the your name and the current date. It is placed in the same workbook as your Excel file is in
. The contents come up exactly as You want, (at least by me herein XL 2007) delimitated with the Pipe Character.
. (Again you will need to change the sheet name to suit your sheet)
. The point I think, and characteristic of this method, compared with others is that you are effectively writing to “any Notebook file” (or rather text .txt File). You could rename it to “AnyNotepadFile” . I do not think you can get any closer. As Andrew Poulsom pointed out, I think you must have some name, and it must go somewhere, so you must have a file path also. And again the point that Andrew Poulsom said, you do not need to open Notepad with this method. The “Open” bit in the code is wot I like to think of “Opening up a data “Highway”” that leads into the file which is either there or is sort of (I thoink?) instantly made as the higway is “Opened” or first used.

Code:

 [color=darkblue]Sub[/color] PoulsomDatensaetzeSchreibenblondeambition()    [color=green]' schreib ein excel tabelle aus als einfach text datei getrennt mit ;[/color]
   [color=green]'   test datei wurde neue geshreiben jedesmal, bzw ALTE UBERSCHREIBEN!!![/color]
   
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color], k [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] T() [color=darkblue]As[/color] [color=darkblue]String[/color], DateBodge [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Let[/color] DateBodge = Date
    [color=darkblue]Let[/color] DateBodge = Left(DateBodge, 6)
    [color=darkblue]Let[/color] DateBodge = DateBodge & Right(Date, 2)
    [color=darkblue]Let[/color] DateBodge = Replace(DateBodge, ".", "")
    [color=darkblue]Dim[/color] RowNumber [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRowNumber [color=darkblue]As[/color] [color=darkblue]Long[/color], ColumnNumber [color=darkblue]As[/color] [color=darkblue]Long[/color], LastColumnNumber [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Let[/color] LastColumnNumber = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    [color=darkblue]Let[/color] LastRowNumber = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    [color=darkblue]ReDim[/color] T(1 [color=darkblue]To[/color] LastColumnNumber)     [color=green]'  T ist ein tempory Array[/color]
     
    ThisWorkbook.Worksheets("PoulsomTextPipe").Activate
    [color=red]ActiveSheet.Cells(1, 1).Activate[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] Fehler
    
    [color=green]' Datei öffnen zum Schreiben[/color]
    [color=darkblue]Open[/color] ThisWorkbook.Path & "\blondeambition  " & DateBodge & ".txt" [color=darkblue]For[/color] [color=darkblue]Output[/color] [color=darkblue]As[/color] 1
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRowNumber
        [color=darkblue]For[/color] k = 1 [color=darkblue]To[/color] LastColumnNumber  [color=green]'  k range determines welcher von Temp array spalters wurde gelesen[/color]
            T(k) = Cells(i, k).Value
        [color=darkblue]Next[/color] k
        [color=green]' Zusammengefügte Zeile schreiben[/color]
        [color=darkblue]Print[/color] #1, Join(T, "|")   [color=green]'    ganz temp array wurde geschreiben  auch leer platz, values[/color]
        [color=green]' seperated mit    ;[/color]
    [color=darkblue]Next[/color] i
    
    [color=green]' Datei schließen[/color]
    [color=darkblue]Close[/color] 1
    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
Fehler:
    MsgBox (Err.Description)
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'PoulsomDatensaetzeSchreibenblondeambition()[/color]

. The only issue I have ever had with this code and others doing something similar is that they either crash or give incorrect results the first time around. No one could ever explain this. I have found, however, by practice that adding a line to activate the first cell always (to date) eliminates this problem. (That has also become something of a standard for me to guard against inconsistent “one off” errors which are impossible to track down as they only occur occasionally once!!.)
. This “Bodge” is shown in Red in the above code

Alan

 

[blondeambition]

You don't need to open Notepad. Excel is capable of reading and writing text files.

I need this to be in Notepad.

 

[blondeambition]

Thanks DocAElstein but I was hoping for something a little more compact, less code, that would include the below range.

Range("A1:EX100").Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"

 

[Andrew Poulsom]

You need to forget that Notepad even exists.

 

[blondeambition]

You need to forget that Notepad even exists.

OK fine. I'm a little confused about the filename...this macro needs to be used by others too, not just me so I can't use a file from my personal drive.

 

[DocAElstein]

OK fine. I'm a little confused about the filename...this macro needs to be used by others too, not just me so I can't use a file from my personal drive.

My modified version puts a text ( .txt) file in the same directory as that in which the Excel File is (This Excel File is that has the data you want sent to a text File and also has that macro in it

 

[DocAElstein]

Thanks DocAElstein but I was hoping for something a little more compact, less code, that would include the below range.

Range("A1:EX100").Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"

...... Rick Gave yesterday a somewot more compact version of Andrew Poulsom's original code in that original Thread....
http://www.mrexcel.com/forum/excel-questions/213591-pipe-delimited-files.html
He posted it in that Thread.