Glad that the print to file works. I agree that having to manually enter a filename and path is a pain.
Excel does allow access to the 'Save As' dialog in VBA
The following code will bring up the Save As box, navigate to where you want to save the print file, give it a name or select an old one to overwrite, click save and the selected path/filename will be used for your print file.
' Routine to get a 'save as' dialog box to get the location
' and filename for a print file for all Worksheets
' in the active workbook
' Humar 11 September 2009
Dim strFN As String
strFN = Application.GetSaveAsFilename
ActiveWorkbook.PrintOut Copies:=1, PrintToFile:=True, _
Put this in a standard module in Personal.xls
Note that the code starting 'ActiveWorkbook.PrintOut' to '=strFN' is all one line
Create a button on a suitable toolbar and link it to this Macro.
If you are going to print to the same place all the time you could dispense with the GetSaveAsFilename and hard code the path and filename
or you could use the time/date to make a filename.
strFN = "C:\" & Format(Now, "hhmm") & ".prn"
will give you a filename that looks like this
PS I'm not in my office, so I haven't tested that it keeps the colour setting!!!