Microsoft Office Forums - View Single Post - [Solved] How to change save location and file to print name change?

albertc · 05-13-2012, 12:27 PM

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

I want to do a few more things like;

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