Opening Notepad and Pasting, from Excel?

Ste_Moore01 · Sep 14, 2006

Hello again,

I was wondering, is it possible to make a macro in Excel that copies the contents of column A then opens Notepad and pastes it?

I need this because we have dot matrix printer in work. If we printed to it from Excel it doesn't print correctly and will take ages to print, whereas if you print from Notepad it works perfectly.

Does anyone know whether this is possible?

Andrew Poulsom · Sep 14, 2006

Couldn't you just save the data in a txt file, open it in Notepad and print it?

Ste_Moore01 · Sep 14, 2006

Yes I can do that, but I was hoping to automate it using a macro so it would save time.

philR · Sep 14, 2006

I would suggest writing the data to a text file and then using a shell command to open the text file with notepad may be easier.

I can show you how to write the info to a text file, I am not sure of the shell command off the top of my head. I will look into it unless someone else knows it.

philR · Sep 14, 2006

Found it: Suppose the text file is in c:\temp and is called testing.txt. This will open it up:

Code:

RSP = Shell("C:\WINDOWS\NOTEPAD.EXE C:\TEMP\testing.txt", vbNormalFocus)

Oorang · Sep 14, 2006

It's very possible, but you really don't need to copy/paste it in, you can just write it to the file via text stream. I wrote up some code for you. When run it will take the column your cursor currently resides in and write it to a text file then ask you if you want to print it. I tried to comment it so you could maintain it without too much trouble. If you have any questions let me know!

<hr>
Option Explicit
'Make sure this is set to the location of notepad on your PC.
Sub SendSelectedColumnToTextFile()
'This code was originally written by Aaron Bush 2006
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copywrite notice is left unchanged.
'
'Code Courtesy of
'Aaron Bush
'
'Copywrite verbage courtesy of Dev Ashish
On Error GoTo Err_Hnd
Dim strFileName As String
Dim strNotepadPath As String
Dim rngCell As Excel.Range
Dim rngData As Excel.Range
Dim ws As Excel.Worksheet
Dim win As Excel.Window
'*******These require a reference to the Microsoft Scripting Runtime Library.*******
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
'***********************************************************************************
'Prompt user for file name.
strFileName = Excel.Application.GetSaveAsFilename(FileFilter:="Text Files,*.txt")
'Exit sub if user hits the Cancel button.
If strFileName = "False" Then Exit Sub
'Make the file scripting object here so you can use it to verify you are not
'about to overwrite a file that already exists.
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
'Check to see if file exists.
If fso.FileExists(strFileName) Then
 'If file exists then verify with user they want to overwrite. Exit sub if they do not.
 If MsgBox("You have selected a file name that already exists. " & _
 "If you continue you will overwrite the pre-existing file." _
 , vbExclamation + vbOKCancel, "Confirm Overwrite") = vbCancel Then GoTo Exit_Sub
 End If
'This action both sets the text-stream object and creates the file.
Set ts = fso.CreateTextFile(strFileName, True)
'Set the excel objects we will be working with.
Set ws = Excel.ActiveSheet
Set win = Excel.ActiveWindow
'Set the range to send to our text file. nteresect the selected column with the used range to
'get only the cells in column that actually have data.
Set rngData = Excel.Intersect(Excel.Columns(win.ActiveCell.Column), ws.UsedRange)
'Loop through the range and send each cell value to the text file as one line.
For Each rngCell In rngData
 ts.WriteLine rngCell.Value
Next rngCell
'Close the file you just created so you can print it if you want to.
ts.Close
MsgBox "Export complete.", vbInformation, "Export Complete."
'Ask user if they want to print the file.
If MsgBox("Do you want to print the file you just created?", vbYesNo + vbQuestion, "Print File?") = vbYes Then
 strNotepadPath = "C:\WINDOWS\System32\Notepad.exe"
 'This will find notepad if it's location is different then the default.
 If Not fso.FileExists(strNotepadPath) Then strNotepadPath = NotepadPath
 'If notpad not found then abort.
 If LenB(strNotepadPath) = 0 Then GoTo Exit_Sub
 'Print using the print command line option in notepad.
 Shell strNotepadPath & " /P " & strFileName
 End If
Exit_Sub:
'Close the textstream again in case of an error prior to it's normal close.
'Turn error handling off to prevent an infinite error loop if text stream
'is already closed.
On Error Resume Next
ts.Close
Err.Clear
'Restore Error Handling
On Error GoTo Err_Hnd
'Release all objects
Set ts = Nothing
Set fso = Nothing
Set win = Nothing
Set ws = Nothing
Set ts = Nothing
Exit Sub
'Error handling routine.
Err_Hnd:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
Resume Exit_Sub
End Sub
Function NotepadPath() As String
'This code was originally written by Aaron Bush 2006
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copywrite notice is left unchanged.
'
'Code Courtesy of
'Aaron Bush
'
'Copywrite verbage courtesy of Dev Ashish
On Error GoTo Err_Hnd
Dim fs As FileSearch
Dim strSource As String
Set fs = Excel.Application.FileSearch
fs.LookIn = "C:\"
fs.SearchSubFolders = True
fs.Filename = "Notepad.exe"
fs.MatchTextExactly = True
fs.Execute
If fs.FoundFiles.Count > 0 Then
 NotepadPath = fs.FoundFiles(1)
 Exit Function
 Else
 strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function NotepadPath"
 Err.Raise 513, strSource, "Can not find Notepad installed on system."
 End If
Exit_Function:
If Not fs Is Nothing Then Set fs = Nothing
Exit Function
'Error handling routine.
Err_Hnd:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
Resume Exit_Function
End Function

Legacy 98055 · Sep 14, 2006

The original proposed method of sending the data to Notepad is just fine in my opinion. No disc footprint is created when sent directly to the window as opposed to creating a file that must be saved to disc before it can be shelled out. You can do this dependably using several API functions. Download the file below to see a working example.

OpenNotepadPasteData.zip

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Option Explicit

 Private Declare Function GetForegroundWindow Lib "user32" () As Long
 Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
 Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
 Private Declare Function IsWindow Lib "user32" (ByVal hWnd As Long) As Long

 Private Const WM_SETTEXT = &HC

 'to copy column 1 of the activesheet to a new instance of notepad
 Public Sub ExampleUsage()
 Call CopyRangeToNotepad(ActiveSheet.Columns(1))
 End Sub

 'set a reference to Microsoft Forms x.x Object Library
 Private Sub CopyRangeToNotepad(CopyTarget As Range)
 Dim hWnd As Long, SendThisText As String, EditHwnd As Long
 Dim DatObj As New MSForms.DataObject

 'start notepad
 Shell "notepad", vbNormalFocus
 'copy the range's text into the clipboard
 CopyTarget.Copy
 'get the window handle for notepad
 hWnd = GetForegroundWindow
 'make sure we have a valid window
 If IsWindow(hWnd) = 0 Then Exit Sub
 'grab the data in the clipboard and place it in a dataobject
 DatObj.GetFromClipboard
 'assign the DataObject's contents to a string variable
 SendThisText = DatObj.GetText(1)
 'get the window handle to the child edit class window in notepad
 EditHwnd = FindWindowEx(hWnd, 0, "Edit", vbNullString)
 'send a WM_SETTEXT window message to the edit window
 SendMessage EditHwnd, WM_SETTEXT, ByVal Len(SendThisText) + 1, ByVal SendThisText
 Application.CutCopyMode = False
 End Sub
</td></tr></table><button onclick='document.all("9142006151939765").value=document.all("9142006151939765").value.replace(/ \s\s/g,"");document.all("9142006151939765").value=document.all("9142006151939765").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("9142006151939765").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9142006151939765" wrap="virtual">
Option Explicit

Private Declare Function GetForegroundWindow Lib "user32" () As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function IsWindow Lib "user32" (ByVal hWnd As Long) As Long

Private Const WM_SETTEXT = &HC

'to copy column 1 of the activesheet to a new instance of notepad
Public Sub ExampleUsage()
Call CopyRangeToNotepad(ActiveSheet.Columns(1))
End Sub

'set a reference to Microsoft Forms x.x Object Library
Private Sub CopyRangeToNotepad(CopyTarget As Range)
Dim hWnd As Long, SendThisText As String, EditHwnd As Long
Dim DatObj As New MSForms.DataObject

'start notepad
Shell "notepad", vbNormalFocus
'copy the range's text into the clipboard
CopyTarget.Copy
'get the window handle for notepad
hWnd = GetForegroundWindow
'make sure we have a valid window
If IsWindow(hWnd) = 0 Then Exit Sub
'grab the data in the clipboard and place it in a dataobject
DatObj.GetFromClipboard
'assign the DataObject's contents to a string variable
SendThisText = DatObj.GetText(1)
'get the window handle to the child edit class window in notepad
EditHwnd = FindWindowEx(hWnd, 0, "Edit", vbNullString)
'send a WM_SETTEXT window message to the edit window
SendMessage EditHwnd, WM_SETTEXT, ByVal Len(SendThisText) + 1, ByVal SendThisText
Application.CutCopyMode = False
End Sub</textarea>

OpenNotepadPasteData.zip

TTom · Sep 14, 2006

Could I modify some of the code to take a selected range (I have a proceedure that determines and selects range as active), copy into text file, do a SaveAs in current folder using a given name from inputbox, BUT SaveAs with the extension .iif instead of .txt? (this will be a Csv text file)

Oorang · Sep 14, 2006

Try this:
<hr>
Option Explicit
Dim CB As MSForms.DataObject
Sub SendSelectionToIIF()
 'Run this to test.
 SendRangeToIIFFile ActiveWindow.Selection
End Sub
Sub SendRangeToIIFFile(rng As Excel.Range)
 'This code was originally written by Aaron Bush 2006
 'It is not to be altered or distributed,
 'except as part of an application.
 'You are free to use it in any application,
 'provided the copywrite notice is left unchanged.
 '
 'Code Courtesy of
 'Aaron Bush
 '
 'Copywrite verbage courtesy of Dev Ashish
 On Error GoTo Err_Hnd
 Dim strFileName As String
 Dim strNotepadPath As String
 Dim strWriteValue As String
 '*******These require a reference to the Microsoft Scripting Runtime Library.*******
 Dim fso As Scripting.FileSystemObject
 Dim TS As Scripting.TextStream
 '***********************************************************************************
 'Prompt user for file name.
 strFileName = Excel.Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="IIF Files (*.iif),*.iif")
 'Exit sub if user hits the Cancel button.
 If strFileName = "False" Then Exit Sub
 'Make the file scripting object here so you can use it to verify you are not
 'about to overwrite a file that already exists.
 Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 'Check to see if file exists.
 If fso.FileExists(strFileName) Then
 'If file exists then verify with user they want to overwrite. Exit sub if they do not.
 If MsgBox("You have selected a file name that already exists. " & _
 "If you continue you will overwrite the pre-existing file." _
 , vbExclamation + vbOKCancel, "Confirm Overwrite") = vbCancel Then GoTo Exit_Sub
 End If
 'This action both sets the text-stream object and creates the file.
 Set TS = fso.CreateTextFile(strFileName, True)
 'Convert Range to a CSV string
 strWriteValue = ConvertRangeToCSV(rng)
 'Send entire range to file.
 TS.Write strWriteValue
 'Close the file you just created so you can print it if you want to.
 TS.Close
 MsgBox "Export complete.", vbInformation, "Export Complete."
 'Ask user if they want to print the file.
 If MsgBox("Do you want to print the file you just created?", vbYesNo + vbQuestion, "Print File?") = vbYes Then
 strNotepadPath = "C:\WINDOWS\System32\Notepad.exe"
 'This will find notepad if it's location is different then the default.
 If Not fso.FileExists(strNotepadPath) Then strNotepadPath = NotepadPath
 'If notpad not found then abort.
 If LenB(strNotepadPath) = 0 Then GoTo Exit_Sub
 'Print using the print command line option in notepad.
 Shell strNotepadPath & " /P " & strFileName
 End If
Exit_Sub:
 'Close the textstream again in case of an error prior to it's normal close.
 'Turn error handling off to prevent an infinite error loop if text stream
 'is already closed.
 On Error Resume Next
 TS.Close
 Err.Clear
 'Restore Error Handling
 On Error GoTo Err_Hnd
 'Release all objects
 Set TS = Nothing
 Set fso = Nothing
 Exit Sub
 'Error handling routine.
Err_Hnd:
 MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Sub
End Sub
Function NotepadPath() As String
 'This code was originally written by Aaron Bush 2006
 'It is not to be altered or distributed,
 'except as part of an application.
 'You are free to use it in any application,
 'provided the copywrite notice is left unchanged.
 '
 'Code Courtesy of
 'Aaron Bush
 '
 'Copywrite verbage courtesy of Dev Ashish
 On Error GoTo Err_Hnd
 Dim FS As FileSearch
 Dim strSource As String
 Set FS = Excel.Application.FileSearch
 FS.LookIn = "C:\"
 FS.SearchSubFolders = True
 FS.Filename = "Notepad.exe"
 FS.MatchTextExactly = True
 FS.Execute
 If FS.FoundFiles.Count > 0 Then
 NotepadPath = FS.FoundFiles(1)
 Exit Function
 Else
 strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function NotepadPath"
 Err.Raise 513, strSource, "Can not find Notepad installed on system."
 End If
Exit_Function:
 If Not FS Is Nothing Then Set FS = Nothing
 Exit Function
 'Error handling routine.
Err_Hnd:
 MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function
Function ConvertRangeToCSV(rng As Range) As String
 'Written By Aaron Bush 11/30/2005 Free for Public Use.
 'Modified 9/13/2006 by Aaron Bush
 'Requires refrence to Microsoft Form 2.0 Object Library.
 '(Usually C:\WINDOWS\System32\FM20.DLL).
 On Error GoTo Err_Hnd
 Dim strRecord As String
 Set CB = New MSForms.DataObject
 rng.Copy
 CB.GetFromClipboard
 strRecord = CB.GetText(1)
 'Replace Column dividers with commas
 strRecord = VBA.Replace$(strRecord, Chr(9), Chr(44))
 'Writes record to file minus final comma.
 ConvertRangeToCSV = VBA.Left$(strRecord, Len(strRecord) - 2)
 CB.Clear
Exit_Function:
 Set CB = Nothing
 Exit Function
 'Error handling routine.
Err_Hnd:
 MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function

Ste_Moore01 · Sep 15, 2006

Thanks for all your help guys!

Right_Click, the demo file you posted worked brilliantly but unfortunately, the code that Oorang does everything that I want my file to do.

The only thing it doesn't allow me to do is to select the printer which I would like to print to from the Notepad.

Is there anything I can do to change this or do I just need to change the activeprinter?

Opening Notepad and Pasting, from Excel?

Ste_Moore01

Active Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Ste_Moore01

Active Member

philR

Active Member

philR

Active Member

Oorang

Well-known Member

Legacy 98055

Guest

TTom

Well-known Member

Oorang

Well-known Member

Ste_Moore01

Active Member

Similar threads

Forum statistics

Share this page

Opening Notepad and Pasting, from Excel?

Active Member

Excel Facts

MrExcel MVP

Active Member

Active Member

Active Member

Well-known Member

Legacy 98055

Guest

Well-known Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock