Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-13-2012, 12:27 PM
albertc albertc is offline How to change save location and file to print name change? Windows 7 64bit How to change save location and file to print name change? Office 2010 64bit
Novice
How to change save location and file to print name change?
 
Join Date: May 2012
Posts: 1
albertc is on a distinguished road
Question 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
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

Last edited by macropod; 05-13-2012 at 06:46 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 05-13-2012, 07:05 PM
macropod's Avatar
macropod macropod is offline How to change save location and file to print name change? Windows 7 64bit How to change save location and file to print name change? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,953
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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)
might become:
Code:
sFileName = InputBox("Save PDF to desktop as:", _
ActiveSheet.Range("A1").Value & "' to PDF...", ActiveSheet.Name)
Do note, though, that certain characters can't be used in filenames and you'll get an error in that case. Extra code would be required to strip out invalid characters.

Similarly, to change the folder, you could replace the line:
Code:
sFolder = Environ("USERPROFILE") & "c:\Invoices\"
with something like:
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
to control the number of copies, you could change:
Code:
ActiveSheet.PrintOut
to:
Code:
ActiveSheet.PrintOut Copies:=3
or even:
Code:
ActiveSheet.PrintOut Copies:=InputBox("How many copies to print?", "Print Count", 3)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change save location and file to print name change? change the view to final, my change bars disappear. I need them visible anna.a.julin Word 1 03-01-2012 08:05 PM
How to change save location and file to print name change? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft