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?

Oorang · Sep 15, 2006

Using the /p command line on notepad automatically prints to the default printer. It is possible to specify the printer using /pt (I think it's PT) but you also have to specify the driver dll and printer port and it's just a pain. You can work around this by just switching the default printer in code before you print and then switching it back. It would look like this:
<hr>
Option Explicit
Dim CB As MSForms.DataObject
Sub SendSelectionToIIF()
 'Run this to test. (Will use Adobe Pro's "print to pdf print driver"
 'If printer is specied you will automatically print to that printer.
 'If printer is not specified you will be prompted.
 SendRangeToIIFFile ActiveWindow.Selection, "Adobe PDF"
 'If boolSkipPrint is set to true then you will just export with no print
 'prompt. Ex:
 'SendRangeToIIFFile ActiveWindow.Selection, boolSkipPrint:=True
 'Note however that it is highly inadvisable to run this sub more than once
 'in the same procedure on the SAME file
End Sub
Sub SendRangeToIIFFile(rng As Excel.Range, Optional strPrinter As String = "Default", Optional boolSkipPrint As Boolean = False)
 '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
 Dim boolPrint As Boolean
 '*******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 VBA.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
 VBA.MsgBox "Export complete.", vbInformation, "Export Complete."
 If Not boolSkipPrint Then
 'Ask user if they want to print the file. (Will automaticly print if a printer
 'is specified.)
 If strPrinter <> "Default" Then boolPrint = True
 If Not boolPrint Then
 If VBA.MsgBox("Do you want to print the file you just created?", _
 vbYesNo + vbQuestion, "Print File?") = vbYes Then boolPrint = True
 End If
 If boolPrint Then
 strNotePadPath = ReturnNotepadPath("C:\WINDOWS\System32\Notepad.exe")
 If VBA.LenB(strNotePadPath) = 0 Then Err.Raise 513, "", "Cannot print. Notepad not found."
 If strPrinter <> "Default" Then
 PrintToNotePad strPrinter, strFileName, strNotePadPath
 Else
 'If you left the printer on default then you can just print.
 PrintToNotePad GetActivePrinterName, strFileName, strNotePadPath
 End If
 End If
 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:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Sub
End Sub
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:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function
Sub ChangeDefaultPrinter(strPrinter 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
 '************************************************
 'This requires a reference to the Microsoft Script Host Obhect Model
 Dim ntwrk As IWshRuntimeLibrary.WshNetwork
 '************************************************
 If Not VerifyPrinterExists(strPrinter) Then Err.Raise 513, "Sub ChangeDefaultPrinter", "Cannot find printer " & strPrinter
 Set ntwrk = VBA.CreateObject("WScript.Network")
 ntwrk.SetDefaultPrinter strPrinter
Exit_Sub:
 Set ntwrk = Nothing
 Exit Sub
Err_Hnd:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Sub
End Sub
Function VerifyPrinterExists(strPrinter) As Boolean
 '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
 '************************************************
 'This requires a reference to the Microsoft Script Host Obhect Model
 Dim ntwrk As WshNetwork
 '************************************************
 Dim bytLC As Byte
 Set ntwrk = VBA.CreateObject("WScript.Network")
 'Verify the printer you want to print to exists.
 For bytLC = 1 To ntwrk.EnumNetworkDrives.Count - 1
 If ntwrk.EnumPrinterConnections(bytLC) = strPrinter Then
 VerifyPrinterExists = True
 Exit Function
 End If
 Next bytLC
Exit_Function:
 Set ntwrk = Nothing
 Exit Function
Err_Hnd:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function
Function GetActivePrinterName() 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 strPrntrNm As String
 Const strDlmtr As String = " on "
 strPrntrNm = Excel.Application.ActivePrinter
 GetActivePrinterName = VBA.Left$(strPrntrNm, InStr(strPrntrNm, strDlmtr) - 1)
Exit_Function:
 Exit Function
Err_Hnd:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function
Sub PrintToNotePad(strPrinter As String, strFileName As String, Optional strNotePadPath As String = "Default")
 '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 strOriginalPrinter As String
 '********************************************************
 'Requires a reference to the microsoft scripting runtime.
 Dim fso As Scripting.FileSystemObject
 '********************************************************
 'Verify we have a good notepad path before printing.
 Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If Not fso.FileExists(strNotePadPath) Then
 'Get notepad path if path is not specied
 strNotePadPath = ReturnNotepadPath(strNotePadPath)
 End If
 'Store the original default printer so you can change it back once
 'you have printed.
 strOriginalPrinter = GetActivePrinterName
 'You can print to a specific printer using notepad command line PT switch,
 'but it requires you to know the printer DLL port etc. It's easier to just
 'change the default printer and then change it back.
 ChangeDefaultPrinter strPrinter
 'Print using the print command line option in notepad.
 Shell strNotePadPath & " /P " & strFileName
Exit_Sub:
 Set fso = Nothing
 'Default printer is restored as part of exit sub so an error will not interfere with it's restoration.
 ChangeDefaultPrinter strOriginalPrinter
 Exit Sub
Err_Hnd:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Sub
End Sub
Function ReturnNotepadPath(strCheckFirst As String) 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
 '********************************************************
 'Requires a reference to the microsoft scripting runtime.
 Dim fso As Scripting.FileSystemObject
 Dim drvs As Scripting.Drives
 Dim drv As Scripting.Drive
 '********************************************************
 Dim FS As Office.FileSearch
 Dim strSource As String
 Dim bytLC As Byte
 'Check the default first before doing a more time intesive file search.
 Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If fso.FileExists(strCheckFirst) Then
 ReturnNotepadPath = strCheckFirst
 Exit Function
 End If
 Set FS = Excel.Application.FileSearch
 Set drvs = fso.Drives
 For Each drv In drvs
 If drv.DriveType = Fixed Then
 FS.NewSearch
 FS.LookIn = drv
 FS.SearchSubFolders = True
 FS.Filename = "Notepad.exe"
 FS.MatchTextExactly = True
 FS.Execute
 If FS.FoundFiles.Count > 0 Then
 ReturnNotepadPath = FS.FoundFiles(1)
 Exit For
 Else
 strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function ReturnNotepadPath"
 Err.Raise 513, strSource, "Can not find Notepad installed on system."
 End If
 End If
 Next drv
Exit_Function:
 Set FS = Nothing
 Set drvs = Nothing
 Set fso = Nothing
 Exit Function
 'Error handling routine.
Err_Hnd:
 VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
 Resume Exit_Function
End Function

Legacy 98055 · Sep 15, 2006

Yes. You can change the printer on the fly. You can also skip notepad altogether and send your text directly to the printer. Oorang put a lot of work into his post. See if his code accomplishes what you are after. If not, post back.

Oorang · Sep 15, 2006

Hi Tom,
I liked your API solution, I just sterred clear because Iwas not sure what the impact would be on an dm printer. But I know *I* would have a use for sending text directly to a printer... would you mind posting that up?

Ste_Moore01 · Sep 19, 2006

I've entered the code into my spreadsheet and I'm getting the error message.

"Compile error:

User-defined type not defined"

It highlights the "Dim CB As MSForms.DataObject" line .

I can't seem to find "Microsoft Script Host Object Model" in my references. Is there something needed to install this?

Oorang · Sep 19, 2006

Set a reference to ms forms 2.0

Ste_Moore01 · Sep 20, 2006

I've done that and it still brings up the same message.

Oorang · Sep 21, 2006

You might be able to browse to them, msforms 2.0 is here:
c:\WINDOWS\system32\FM20.DLL
And Microsoft Script Host Object Model is here:
c:\WINDOWS\system32\wshom.ocx

Ste_Moore01 · Sep 22, 2006

I've tried browsing to them and still get the same error message.

"Compile error:

User-defined type not defined"

But it highlights the " Dim fso As Scripting.FileSystemObject" now.

Ste_Moore01 · Sep 22, 2006

Scratch that!

I forgot to reset the microsoft scripting runtime reference.

For some reason it's struggling to find the printer I want to print to.

Any suggestions?

Oorang · Sep 22, 2006

You might not be using the Exact name the computer is using. You do ctrl-g to show the immediate window in the Visual Basic Editor (VBE). Then run this code (requires reference to Scripting Host). It will display all the printer names you have mapped. You can then just copy paste the printer name you want:

Code:

Function DebugPrintAllPrinters() As Boolean
Dim ntwrk As WshNetwork
Dim bytLC As Byte
Set ntwrk = New WshNetwork
For bytLC = 1 To ntwrk.EnumNetworkDrives.Count - 1
    Debug.Print ntwrk.EnumPrinterConnections(bytLC)
Next bytLC
End Function

Opening Notepad and Pasting, from Excel?

Ste_Moore01

Active Member

Oorang

Well-known Member

Excel Facts

Legacy 98055

Guest

Oorang

Well-known Member

Ste_Moore01

Active Member

Oorang

Well-known Member

Ste_Moore01

Active Member

Oorang

Well-known Member

Ste_Moore01

Active Member

Ste_Moore01

Active Member

Oorang

Well-known Member

Similar threads

Forum statistics

Share this page

Opening Notepad and Pasting, from Excel?

Active Member

Well-known Member

Excel Facts

Legacy 98055

Guest

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Active Member

Well-known 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