SaveAs PDF Dialog Box

I have a workbook with a couple of commandbuttons for saving and printing pdf files. Paul got me fixup
on the SaveAs problem I was having, one more problem. When I save a workbook it becomes that name and then I print out pdf
file. When I save the file it shows up in the print dialog box to save, if I skip the save as button and just want the pdf file I have to type in the name. Here are my two codes,is it possible to have the pdf dialog box show the value in c16 without saving first.

Dim varFullName As Variant
    Dim strFileName As String

    Cancel = True

    strFileName = ThisWorkbook.Sheets("proposal").Range("c16").Value
varFullName = Application.GetSaveAsFilename(InitialFileName:=strFileName, _
                                                fileFilter:="Microsoft Excel Binary Workbook (*.xlsb), *.xlsb")

    If varFullName <> Cancel And varFullName <> False Then
          On Error GoTo FileNotSaved
              Application.EnableEvents = False
                 ThisWorkbook.SaveAs Filename:=varFullName, FileFormat:=50
              Application.EnableEvents = True
    End If

    Exit Sub

PDF Button Code

Dim DEFprinter As String
    'store default printer
    DEFprinter = Application.ActivePrinter
Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, ActivePrinter:= _
        "CutePDF Writer on CPW2:", Collate:=True
       ' change back to default printer
    Application.ActivePrinter = DEFprinter

Thanks

If you had Excel 2007+ you could simply use the SaveAs PDF option built into the newer versions of Excel. With Excel 2003 you're stuck with CutePDF, pdfCreator or Adobe Acrobat (or other). Apparently CutePDF (the non-pro version) doesn't allow access to its interface using VBA, but pdfCreator (free) does.

See examples here:
http://www.excelguru.ca/content.php?...Early-Binding)
http://www.excelguru.ca/content.php?...-Late-Binding)

Here's another user (different forum) that has a similar problem and their discussion:
http://www.ozgrid.com/forum/showthread.php?t=51635

Hope that helps!

It should. If you record a macro while saving the file as PDF you can see the code it creates, e.g.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Paul\Desktop\Book1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

There are other options as well (saving only a selection as PDF, the entire workbook (all sheets) as PDF, etc.). Here's some info: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

Also, your profile lists Excel 2003. Does that need to be updated? If so, click "My Profile" at the top of this page, then select the "About Me" tab in the profile. Click the small pencil next to My Microsoft Office Version.

If you add a reference to C16 it should work. Also change the file path to your actual file path. Example:

Sub test()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Paul\Desktop\" & Sheets("Sheet1").Range("C16").Value & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Hmm, with some fiddling I may have figured this out..

Application.Dialogs(xlDialogSaveAs).Show arg1:=Sheets("Sheet1").Range("C16").Value, arg2:=57

This will open the SaveAs dialog window with the filename from cell C16 on Sheet1, set to save as PDF. You can then choose where to save the file.

Thanks to these two sites for the info:

http://social.msdn.microsoft.com/For...6-46630bc02f95 (in Phil Hoff's reply)
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx (PDF fileformat type in comments at very bottom)