Opening Notepad and Pasting, from Excel?

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Yes I can do that, but I was hoping to automate it using a macro so it would save time.
 

philR

Active Member
Joined
Feb 25, 2002
Messages
257
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

Active Member
Joined
Feb 25, 2002
Messages
257

ADVERTISEMENT

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

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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! :LOL:
<hr>
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#007F00">'Make sure this is set to the location of notepad on your PC.</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> SendSelectedColumnToTextFile()
<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> rngCell <SPAN style="color:#00007F">As</SPAN> Excel.Range
<SPAN style="color:#00007F">Dim</SPAN> rngData <SPAN style="color:#00007F">As</SPAN> Excel.Range
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet
<SPAN style="color:#00007F">Dim</SPAN> win <SPAN style="color:#00007F">As</SPAN> Excel.Window
<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(FileFilter:="Text Files,*.txt")
<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> 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">'Set the excel objects we will be working with.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> ws = Excel.ActiveSheet
<SPAN style="color:#00007F">Set</SPAN> win = Excel.ActiveWindow
<SPAN style="color:#007F00">'Set the range to send to our text file. nteresect the selected column with the used range to</SPAN>
<SPAN style="color:#007F00">'get only the cells in column that actually have data.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngData = Excel.Intersect(Excel.Columns(win.ActiveCell.Column), ws.UsedRange)
<SPAN style="color:#007F00">'Loop through the range and send each cell value to the text file as one line.</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> rngData
    ts.WriteLine rngCell.Value
<SPAN style="color:#00007F">Next</SPAN> rngCell
<SPAN style="color:#007F00">'Close the file you just created so you can print it if you want to.</SPAN>
ts.Close
MsgBox "Export complete.", vbInformation, "Export Complete."
<SPAN style="color:#007F00">'Ask user if they want to print the file.</SPAN>
<SPAN style="color:#00007F">If</SPAN> MsgBox("Do you want to print the file you just created?", vbYesNo + vbQuestion, "Print File?") = vbYes <SPAN style="color:#00007F">Then</SPAN>
    strNotepadPath = "C:\WINDOWS\System32\<SPAN style="color:#00007F">Not</SPAN>epad.exe"
    <SPAN style="color:#007F00">'This will find notepad if it's location is different then the default.</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Not fso.FileExists(strNotepadPath) <SPAN style="color:#00007F">Then</SPAN> strNotepadPath = NotepadPath
    <SPAN style="color:#007F00">'If notpad not found then abort.</SPAN>
    <SPAN style="color:#00007F">If</SPAN> LenB(strNotepadPath) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Exit_Sub
    <SPAN style="color:#007F00">'Print using the print command line option in notepad.</SPAN>
    Shell strNotepadPath & " /P " & strFileName
    <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">Set</SPAN> win = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> ws = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> ts = <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:
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> NotepadPath() <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> fs <SPAN style="color:#00007F">As</SPAN> FileSearch
<SPAN style="color:#00007F">Dim</SPAN> strSource <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Set</SPAN> fs = Excel.Application.FileSearch
fs.LookIn = "C:\"
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>
    NotepadPath = fs.FoundFiles(1)
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
    strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function <SPAN style="color:#00007F">Not</SPAN>epadPath"
    Err.Raise 513, strSource, "Can not find Notepad installed on system."
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Exit_Function:
<SPAN style="color:#00007F">If</SPAN> Not fs <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> fs = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
<SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> Function
</FONT>
 
L

Legacy 98055

Guest

ADVERTISEMENT

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:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> GetForegroundWindow <font color="#0000A0">Lib</font> "user32" () <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindowEx <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowExA" (ByVal hWnd1 <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> hWnd2 <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpsz1 <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpsz2 <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SendMessage <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "SendMessageA" (ByVal hWnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> wMsg <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> wParam <font color="#0000A0">As</font> Long, lParam <font color="#0000A0">As</font> Any) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> IsWindow <font color="#0000A0">Lib</font> "user32" (ByVal hWnd <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> WM_SETTEXT = &HC

  <font color="#008000">'to copy column 1 of the activesheet to a new instance of notepad</font>
  <font color="#0000A0">Public</font> <font color="#0000A0">Sub</font> ExampleUsage()
       <font color="#0000A0">Call</font> CopyRangeToNotepad(ActiveSheet.Columns(1))
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#008000">'set a reference to Microsoft Forms x.x Object Library</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CopyRangeToNotepad(CopyTarget <font color="#0000A0">As</font> Range)
       <font color="#0000A0">Dim</font> hWnd <font color="#0000A0">As</font> Long, SendThisText <font color="#0000A0">As</font> String, EditHwnd <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       <font color="#0000A0">Dim</font> DatObj <font color="#0000A0">As</font> <font color="#0000A0">New</font> MSForms.DataObject

      <font color="#008000"> 'start notepad</font>
       Shell "notepad", vbNormalFocus
      <font color="#008000"> 'copy the range's text into the clipboard</font>
       CopyTarget.Copy
      <font color="#008000"> 'get the window handle for notepad</font>
       hWnd = GetForegroundWindow
      <font color="#008000"> 'make sure we have a valid window</font>
       <font color="#0000A0">If</font> IsWindow(hWnd) = 0 <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
      <font color="#008000"> 'grab the data in the clipboard and place it in a dataobject</font>
       DatObj.GetFromClipboard
      <font color="#008000"> 'assign the DataObject's contents to a string variable</font>
       SendThisText = DatObj.GetText(1)
      <font color="#008000"> 'get the window handle to the child edit class window in notepad</font>
       EditHwnd = FindWindowEx(hWnd, 0, "Edit", vbNullString)
      <font color="#008000"> 'send a WM_SETTEXT window message to the edit window</font>
       SendMessage EditHwnd, WM_SETTEXT, <font color="#0000A0">ByVal</font> Len(SendThisText) + 1, <font color="#0000A0">ByVal</font> SendThisText
       Application.CutCopyMode = <font color="#0000A0">False</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9142006151939765").value=document.all("9142006151939765").value.replace(/<br \/>\s\s/g,"");document.all("9142006151939765").value=document.all("9142006151939765").value.replace(/<br \/>/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

Well-known Member
Joined
Jan 19, 2005
Messages
518
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

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Try 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.</SPAN>
    SendRangeToIIFFile ActiveWindow.Selection
<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:#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:#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> 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
    MsgBox "Export complete.", vbInformation, "Export Complete."
    <SPAN style="color:#007F00">'Ask user if they want to print the file.</SPAN>
    <SPAN style="color:#00007F">If</SPAN> MsgBox("Do you want to print the file you just created?", vbYesNo + vbQuestion, "Print File?") = vbYes <SPAN style="color:#00007F">Then</SPAN>
        strNotepadPath = "C:\WINDOWS\System32\<SPAN style="color:#00007F">Not</SPAN>epad.exe"
        <SPAN style="color:#007F00">'This will find notepad if it's location is different then the default.</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Not fso.FileExists(strNotepadPath) <SPAN style="color:#00007F">Then</SPAN> strNotepadPath = NotepadPath
        <SPAN style="color:#007F00">'If notpad not found then abort.</SPAN>
        <SPAN style="color:#00007F">If</SPAN> LenB(strNotepadPath) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Exit_Sub
        <SPAN style="color:#007F00">'Print using the print command line option in notepad.</SPAN>
        Shell strNotepadPath & " /P " & strFileName
        <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:
    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> NotepadPath() <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> FS <SPAN style="color:#00007F">As</SPAN> FileSearch
    <SPAN style="color:#00007F">Dim</SPAN> strSource <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> FS = Excel.Application.FileSearch
    FS.LookIn = "C:\"
    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>
        NotepadPath = FS.FoundFiles(1)
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
        <SPAN style="color:#00007F">Else</SPAN>
        strSource = ThisWorkbook.FullName & "!" & ThisWorkbook.VBProject.Name & "!Function <SPAN style="color:#00007F">Not</SPAN>epadPath"
        Err.Raise 513, strSource, "Can not find Notepad installed on system."
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Exit_Function:
    <SPAN style="color:#00007F">If</SPAN> Not FS <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> FS = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
    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> 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">Nothing</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> Function
    <SPAN style="color:#007F00">'Error handling routine.</SPAN>
Err_Hnd:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    <SPAN style="color:#00007F">Resume</SPAN> Exit_Function
<SPAN style="color:#00007F">End</SPAN> Function
</FONT>
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,020
Members
410,646
Latest member
LegenDSlayeR
Top