#1
|
|||
|
|||
How to change save location and file to print name change?
Hello everybody.
I am new to the forum and am looking for experts in the VBA field. I am not a programmer nor understand it but have for the past week or so dealt with VBA more than I have did in 15 years fixing and repairing pcs. What I want to do is the following; The code bellow prints (saves the spread sheet to PDF more like it) and opens it. Code:
Sub PrintSheet(Optional sFileName As String = "", Optional confirmOverwrite As Boolean = True) Dim oPrinterSettings As Object Dim oPrinterUtil As Object Dim sFolder As String Dim sCurrentPrinter As String Dim xmldom As Object Dim sProgId As String Dim sPrintername As String Dim sFullPrinterName As String Rem -- Documentation of the used COM interface Is available at the link below. Rem -- http://www.biopdf.com/guide/dotnet/chm/html/T_bioPDF_PdfWriter_PdfSettings.htm Rem -- Create the objects To control the printer settings. Rem -- Replace biopdf With bullzip If you have the bullzip printer installed instead Rem -- of the biopdf printer. Set oPrinterSettings = CreateObject("biopdf.PdfSettings") Set oPrinterUtil = CreateObject("biopdf.PdfUtil") Rem -- Get default printer name sPrintername = oPrinterUtil.DefaultPrintername oPrinterSettings.Printername = sPrintername Rem -- Get the full name of the printer sFullPrinterName = FindPrinter(sPrintername) sFullPrinterName = GetFullNetworkPrinterName(sFullPrinterName) Rem -- Prompt the user For a file name sFolder = Environ("USERPROFILE") & "c:\Invoices\" If sFileName = "" Then sFileName = InputBox("Save PDF to desktop as:", "Sheet '" & _ ActiveSheet.Name & "' to PDF...", ActiveSheet.Name) Rem -- Abort the process If the user cancels the dialog If sFileName = "" Then Exit Sub sFileName = sFolder & sFileName End If Rem -- Make sure that the file name ends With .pdf If LCase(Right(sFileName, 4)) <> ".pdf" Then sFileName = sFileName & ".pdf" End If Rem -- Write the settings To the printer Rem -- Settings are written To the runonce.ini Rem -- This file Is deleted immediately after being used. With oPrinterSettings .SetValue "Output", sFileName If confirmOverwrite Then .SetValue "ConfirmOverwrite", "yes" Else .SetValue "ConfirmOverwrite", "no" End If .SetValue "ShowSettings", "never" .SetValue "ShowPDF", "yes" .WriteSettings True End With Rem -- Change To PDF printer sCurrentPrinter = ActivePrinter ActivePrinter = sFullPrinterName Rem -- Print the active work sheet ActiveSheet.PrintOut Rem -- Restore the printer selection ActivePrinter = sCurrentPrinter End Sub 1 - Automaticly name the file being printed to PDF. From what I have learn from this code, the name is being extracted from the current sheet name. I want to also add a cell value to the end of that name. For example the document to be saved in pdf is to be Invoice(InvoiceNumber).pdf. Pop-up if file has already been printed/saved with ok button to close dialog. 2 - Change the location where I want these saved for example c:/Invoices, 3 - and print 3 copies of the current sheet. Any comments and help are very much appreciated. Cheers, Albert Last edited by macropod; 05-13-2012 at 06:46 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
Hi Albert,
To change the filename, you'd change 'ActiveSheet.Name' to point to the cell you want to get the name from (eg: 'ActiveSheet.Range("A1").Value'). Thus: Code:
sFileName = InputBox("Save PDF to desktop as:", "Sheet '" & _ ActiveSheet.Name & "' to PDF...", ActiveSheet.Name) Code:
sFileName = InputBox("Save PDF to desktop as:", _ ActiveSheet.Range("A1").Value & "' to PDF...", ActiveSheet.Name) Similarly, to change the folder, you could replace the line: Code:
sFolder = Environ("USERPROFILE") & "c:\Invoices\" Code:
GetFolder: sFolder = Environ("USERPROFILE") & "c:\Invoices\" sFolder = InputBox("Save PDF folder:", sFolder, "Save Folder") If Dir(sFolder, vbDirectory) = "" Then MsgBox "Not a valid folder", vbCritical GoTo GetFolder End If Code:
ActiveSheet.PrintOut Code:
ActiveSheet.PrintOut Copies:=3 Code:
ActiveSheet.PrintOut Copies:=InputBox("How many copies to print?", "Print Count", 3)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
change the view to final, my change bars disappear. I need them visible | anna.a.julin | Word | 1 | 03-01-2012 08:05 PM |
Outlook 2010 - Change default data file. | siouxnami | Outlook | 4 | 01-26-2012 12:24 PM |
Change style of 'open file' window? | markg2 | Word | 0 | 07-08-2011 11:15 AM |
PowerPoint 2003 - Change default location for Insert Picture | Fee | PowerPoint | 0 | 05-12-2011 02:15 AM |
[PowerPoint 2010] Change font for all slides in an exist *.PPTX file | LongTTH | PowerPoint | 0 | 12-15-2010 02:07 AM |