Button to Save File as PDF

[magmike]

I am using Excel 2007, and it already has the ability to Save As PDF.
However, I would like to add a button to the sheet that allows me to
click it and then a dialog box pops up to let me browse to where I
want to save the file and what I want to name it, and only allowing it
to be Saved as a PDF.

I have found some information, but most of what I have found involves
using other data on the sheet to pre-deternine the name and/or
location of the file.

Any help would be appreciated.

thanks!

magmike

 

[isabelle]

hi magmike,

fileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")

 

[magmike]

hi magmike,

fileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")

Thank you for your help Isabell, however there are two problems with
this...

(1) The name field for the file is filled in with a suggestion based
on the file name of the workbook - is it possible for it to be blank?
(2) Once named, browsed to and then saved - the file doesn't actually
save anywhere. Where is it going? Or am I missing a command?

Thanks in advance for your help.

magmike

 

[Auric__]

Thank you for your help Isabell, however there are two problems with
this...

(1) The name field for the file is filled in with a suggestion based
on the file name of the workbook - is it possible for it to be blank?

fileSaveName = Application.GetSaveAsFilename( _
InitialFilename:="", FileFilter:="PDF Files (*.pdf), *.pdf")

(2) Once named, browsed to and then saved - the file doesn't actually
save anywhere. Where is it going? Or am I missing a command?

Are you doing SaveAs and just not finding the file? Try searching for the
file name to see where it's going.

If you're not doing SaveAs, that's your problem right there. From the help
file:
GetSaveAsFilename Method
Displays the standard Save As dialog box and gets a file name from the
user without actually saving any files.

 

[isabelle]

hi magmike,

VBA Help "Built In Dialog Arguments" lists the second argument of xlSaveAs as type_num
on xl2002, type_num = 1 gives .xls and there is no possibility for pdf
but on xl2007 it will be different you have to find the type_num for pdf

Application.Dialogs(xlDialogSaveAs).Show , 2 'adap type_num

'if you want to propose a file name

Application.Dialogs(xlDialogSaveAs).Show "default fileName", 2

 

[isabelle]

i can not verify this, if you will do the test


Dim fd As FileDialog, ffs As FileDialogFilters
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
Set ffs = .Filters("PDF Files (*.pdf), *.pdf")
End With


anotherway with Common Dialog Control
http://access.mvps.org/access/api/api0001.hhttp://access.mvps.org/access/api/api0001.htm

 

[GS]

isabelle presented the following explanation :

i can not verify this, if you will do the test


Dim fd As FileDialog, ffs As FileDialogFilters
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
Set ffs = .Filters("PDF Files (*.pdf), *.pdf")
End With


anotherway with Common Dialog Control
http://access.mvps.org/access/api/api0001.hhttp://access.mvps.org/access/api/api0001.htm

Common Dialog Control is no longer supported/provided as of Win 6.x and
so would have to be shipped and registered to all machines using the
project if the ocx was not present. I'd stick with using
Application.FileDialog()!