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>
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> CB <SPAN style="color:#00007F">As</SPAN> MSForms.DataObject
<SPAN style="color:#00007F">Sub</SPAN> SendSelectionToIIF()
<SPAN style="color:#007F00">'Run this to test. (Will use Adobe Pro's "print to pdf print driver"</SPAN>
<SPAN style="color:#007F00">'If printer is specied you will automatically print to that printer.</SPAN>
<SPAN style="color:#007F00">'If printer is not specified you will be prompted.</SPAN>
SendRangeToIIFFile ActiveWindow.Selection, "Adobe PDF"
<SPAN style="color:#007F00">'If boolSkipPrint is set to true then you will just export with no print</SPAN>
<SPAN style="color:#007F00">'prompt. Ex:</SPAN>
<SPAN style="color:#007F00">'SendRangeToIIFFile ActiveWindow.Selection, boolSkipPrint:=True</SPAN>
<SPAN style="color:#007F00">'Note however that it is highly inadvisable to run this sub more than once</SPAN>
<SPAN style="color:#007F00">'in the same procedure on the SAME file</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> SendRangeToIIFFile(rng <SPAN style="color:#00007F">As</SPAN> Excel.Range, <SPAN style="color:#00007F">Optional</SPAN> strPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Default", <SPAN style="color:#00007F">Optional</SPAN> boolSkipPrint <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN> = <SPAN style="color:#00007F">False</SPAN>)
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#00007F">Dim</SPAN> strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strNotePadPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strWriteValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> boolPrint <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#007F00">'*******These require a reference to the Microsoft Scripting Runtime Library.*******</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> fso <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject
<SPAN style="color:#00007F">Dim</SPAN> TS <SPAN style="color:#00007F">As</SPAN> Scripting.TextStream
<SPAN style="color:#007F00">'***********************************************************************************</SPAN>
<SPAN style="color:#007F00">'Prompt user for file name.</SPAN>
strFileName = Excel.Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="IIF Files (*.iif),*.iif")
<SPAN style="color:#007F00">'Exit sub if user hits the Cancel button.</SPAN>
<SPAN style="color:#00007F">If</SPAN> strFileName = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">'Make the file scripting object here so you can use it to verify you are not</SPAN>
<SPAN style="color:#007F00">'about to overwrite a file that already exists.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = VBA.CreateObject("Scripting.FileSystemObject")
<SPAN style="color:#007F00">'Check to see if file exists.</SPAN>
<SPAN style="color:#00007F">If</SPAN> fso.FileExists(strFileName) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#007F00">'If file exists then verify with user they want to overwrite. Exit sub if they do not.</SPAN>
<SPAN style="color:#00007F">If</SPAN> 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 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Exit_Sub
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#007F00">'This action both sets the text-stream object and creates the file.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> TS = fso.CreateTextFile(strFileName, <SPAN style="color:#00007F">True</SPAN>)
<SPAN style="color:#007F00">'Convert Range to a CSV string</SPAN>
strWriteValue = ConvertRangeToCSV(rng)
<SPAN style="color:#007F00">'Send entire range to file.</SPAN>
TS.Write strWriteValue
<SPAN style="color:#007F00">'Close the file you just created so you can print it if you want to.</SPAN>
TS.Close
VBA.MsgBox "Export complete.", vbInformation, "Export Complete."
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> boolSkipPrint <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#007F00">'Ask user if they want to print the file. (Will automaticly print if a printer</SPAN>
<SPAN style="color:#007F00">'is specified.)</SPAN>
<SPAN style="color:#00007F">If</SPAN> strPrinter <> "Default" <SPAN style="color:#00007F">Then</SPAN> boolPrint = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> boolPrint <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">If</SPAN> VBA.MsgBox("Do you want to print the file you just created?", _
vbYesNo + vbQuestion, "Print File?") = vbYes <SPAN style="color:#00007F">Then</SPAN> boolPrint = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> boolPrint <SPAN style="color:#00007F">Then</SPAN>
strNotePadPath = ReturnNotepadPath("C:\WINDOWS\System32\Notepad.exe")
<SPAN style="color:#00007F">If</SPAN> VBA.LenB(strNotePadPath) = 0 <SPAN style="color:#00007F">Then</SPAN> Err.Raise 513, "", "Cannot print. Notepad not found."
<SPAN style="color:#00007F">If</SPAN> strPrinter <> "Default" <SPAN style="color:#00007F">Then</SPAN>
PrintToNotePad strPrinter, strFileName, strNotePadPath
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#007F00">'If you left the printer on default then you can just print.</SPAN>
PrintToNotePad GetActivePrinterName, strFileName, strNotePadPath
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Exit_Sub:
<SPAN style="color:#007F00">'Close the textstream again in case of an error prior to it's normal close.</SPAN>
<SPAN style="color:#007F00">'Turn error handling off to prevent an infinite error loop if text stream</SPAN>
<SPAN style="color:#007F00">'is already closed.</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
TS.Close
Err.Clear
<SPAN style="color:#007F00">'Restore Error Handling</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#007F00">'Release all objects</SPAN>
<SPAN style="color:#00007F">Set</SPAN> TS = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Sub
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Function</SPAN> ConvertRangeToCSV(rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'Written By Aaron Bush 11/30/2005 Free for Public Use.</SPAN>
<SPAN style="color:#007F00">'Modified 9/13/2006 by Aaron Bush</SPAN>
<SPAN style="color:#007F00">'Requires refrence to Microsoft Form 2.0 Object Library.</SPAN>
<SPAN style="color:#007F00">'(Usually C:\WINDOWS\System32\FM20.DLL).</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#00007F">Dim</SPAN> strRecord <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Set</SPAN> CB = <SPAN style="color:#00007F">New</SPAN> MSForms.DataObject
rng.Copy
CB.GetFromClipboard
strRecord = CB.GetText(1)
<SPAN style="color:#007F00">'Replace Column dividers with commas</SPAN>
strRecord = VBA.Replace$(strRecord, Chr(9), Chr(44))
<SPAN style="color:#007F00">'Writes record to file minus final comma.</SPAN>
ConvertRangeToCSV = VBA.Left$(strRecord, Len(strRecord) - 2)
CB.Clear
Exit_Function:
<SPAN style="color:#00007F">Set</SPAN> CB = <SPAN style="color:#00007F"><SPAN style="color:#00007F">Not</SPAN>hing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ChangeDefaultPrinter(strPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#007F00">'************************************************</SPAN>
<SPAN style="color:#007F00">'This requires a reference to the Microsoft Script Host Obhect Model</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ntwrk <SPAN style="color:#00007F">As</SPAN> IWshRuntimeLibrary.WshNetwork
<SPAN style="color:#007F00">'************************************************</SPAN>
<SPAN style="color:#00007F">If</SPAN> Not VerifyPrinterExists(strPrinter) <SPAN style="color:#00007F">Then</SPAN> Err.Raise 513, "Sub ChangeDefaultPrinter", "Cannot find printer " & strPrinter
<SPAN style="color:#00007F">Set</SPAN> ntwrk = VBA.CreateObject("WScript.Network")
ntwrk.SetDefaultPrinter strPrinter
Exit_Sub:
<SPAN style="color:#00007F">Set</SPAN> ntwrk = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Sub
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Function</SPAN> VerifyPrinterExists(strPrinter) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#007F00">'************************************************</SPAN>
<SPAN style="color:#007F00">'This requires a reference to the Microsoft Script Host Obhect Model</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ntwrk <SPAN style="color:#00007F">As</SPAN> WshNetwork
<SPAN style="color:#007F00">'************************************************</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> bytLC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
<SPAN style="color:#00007F">Set</SPAN> ntwrk = VBA.CreateObject("WScript.Network")
<SPAN style="color:#007F00">'Verify the printer you want to print to exists.</SPAN>
<SPAN style="color:#00007F">For</SPAN> bytLC = 1 <SPAN style="color:#00007F">To</SPAN> ntwrk.EnumNetworkDrives.Count - 1
<SPAN style="color:#00007F">If</SPAN> ntwrk.EnumPrinterConnections(bytLC) = strPrinter <SPAN style="color:#00007F">Then</SPAN>
VerifyPrinterExists = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> Function
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> bytLC
Exit_<SPAN style="color:#00007F">Function</SPAN>:
<SPAN style="color:#00007F">Set</SPAN> ntwrk = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> Function
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> Function
<SPAN style="color:#00007F">Function</SPAN> GetActivePrinterName() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#00007F">Dim</SPAN> strPrntrNm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Const</SPAN> strDlmtr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = " on "
strPrntrNm = Excel.Application.ActivePrinter
GetActivePrinterName = VBA.Left$(strPrntrNm, InStr(strPrntrNm, strDlmtr) - 1)
Exit_<SPAN style="color:#00007F">Function</SPAN>:
<SPAN style="color:#00007F">Exit</SPAN> Function
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> PrintToNotePad(strPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> str<SPAN style="color:#00007F">Not</SPAN>ePadPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Default")
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#00007F">Dim</SPAN> strOriginalPrinter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'********************************************************</SPAN>
<SPAN style="color:#007F00">'Requires a reference to the microsoft scripting runtime.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> fso <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject
<SPAN style="color:#007F00">'********************************************************</SPAN>
<SPAN style="color:#007F00">'Verify we have a good notepad path before printing.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = VBA.CreateObject("Scripting.FileSystemObject")
<SPAN style="color:#00007F">If</SPAN> Not fso.FileExists(strNotePadPath) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#007F00">'Get notepad path if path is not specied</SPAN>
strNotePadPath = ReturnNotepadPath(strNotePadPath)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#007F00">'Store the original default printer so you can change it back once</SPAN>
<SPAN style="color:#007F00">'you have printed.</SPAN>
strOriginalPrinter = GetActivePrinterName
<SPAN style="color:#007F00">'You can print to a specific printer using notepad command line PT switch,</SPAN>
<SPAN style="color:#007F00">'but it requires you to know the printer DLL port etc. It's easier to just</SPAN>
<SPAN style="color:#007F00">'change the default printer and then change it back.</SPAN>
ChangeDefaultPrinter strPrinter
<SPAN style="color:#007F00">'Print using the print command line option in notepad.</SPAN>
Shell strNotePadPath & " /P " & strFileName
Exit_Sub:
<SPAN style="color:#00007F">Set</SPAN> fso = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#007F00">'Default printer is restored as part of exit sub so an error will not interfere with it's restoration.</SPAN>
ChangeDefaultPrinter strOriginalPrinter
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "<SPAN style="color:#00007F">Error</SPAN>: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Sub
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Function ReturnNotepadPath(strCheckFirst <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'This code was originally written by Aaron Bush 2006</SPAN>
<SPAN style="color:#007F00">'It is not to be altered or distributed,</SPAN>
<SPAN style="color:#007F00">'except as part of an application.</SPAN>
<SPAN style="color:#007F00">'You are free to use it in any application,</SPAN>
<SPAN style="color:#007F00">'provided the copywrite notice is left unchanged.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Code Courtesy of</SPAN>
<SPAN style="color:#007F00">'Aaron Bush</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Copywrite verbage courtesy of Dev Ashish</SPAN>
<SPAN style="color:#00007F">On</SPAN> Error <SPAN style="color:#00007F">GoTo</SPAN> Err_Hnd
<SPAN style="color:#007F00">'********************************************************</SPAN>
<SPAN style="color:#007F00">'Requires a reference to the microsoft scripting runtime.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> fso <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject
<SPAN style="color:#00007F">Dim</SPAN> drvs <SPAN style="color:#00007F">As</SPAN> Scripting.Drives
<SPAN style="color:#00007F">Dim</SPAN> drv <SPAN style="color:#00007F">As</SPAN> Scripting.Drive
<SPAN style="color:#007F00">'********************************************************</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FS <SPAN style="color:#00007F">As</SPAN> Office.FileSearch
<SPAN style="color:#00007F">Dim</SPAN> strSource <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> bytLC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
<SPAN style="color:#007F00">'Check the default first before doing a more time intesive file search.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = VBA.CreateObject("Scripting.FileSystemObject")
<SPAN style="color:#00007F">If</SPAN> fso.FileExists(strCheckFirst) <SPAN style="color:#00007F">Then</SPAN>
ReturnNotepadPath = strCheckFirst
<SPAN style="color:#00007F">Exit</SPAN> Function
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Set</SPAN> FS = Excel.Application.FileSearch
<SPAN style="color:#00007F">Set</SPAN> drvs = fso.Drives
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> drv <SPAN style="color:#00007F">In</SPAN> drvs
<SPAN style="color:#00007F">If</SPAN> drv.DriveType = Fixed <SPAN style="color:#00007F">Then</SPAN>
FS.NewSearch
FS.LookIn = drv
FS.SearchSubFolders = <SPAN style="color:#00007F">True</SPAN>
FS.Filename = "Notepad.exe"
FS.MatchTextExactly = <SPAN style="color:#00007F">True</SPAN>
FS.Execute
<SPAN style="color:#00007F">If</SPAN> FS.FoundFiles.Count > 0 <SPAN style="color:#00007F">Then</SPAN>
ReturnNotepadPath = FS.FoundFiles(1)
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function ReturnNotepadPath"
Err.Raise 513, strSource, "Can not find Notepad installed on system."
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> drv
Exit_Function:
<SPAN style="color:#00007F">Set</SPAN> FS = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> drvs = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fso = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> Function
<SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
VBA.MsgBox Err.Description, vbCritical, "Error: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> Function
</FONT>