In 2007, need code to save as pdf not excel sheet

[daniels012]

How can I change my existing code to save as pdf?

Here is the code:

Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    
    Application.ScreenUpdating = False
    
    Set WB1 = ActiveWorkbook
    
    WB1.Save
    
    CurrPath = WB1.Path
    
    strfilename = Range("S16").Value & ".xlsm"
    
    strPath = "C:\Documents and Settings\Owner\My Documents\Completed Proposals\"
    
    strPath2 = "C:\Documents and Settings\Owner\My Documents\Surface Systems\"
    
    On Error Resume Next
   
    WB1.SaveAs Filename:=strPath & strfilename
    On Error GoTo 0
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xlsm")
    Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "C:\Documents and Settings\Owner\Desktop\Mike Prop\" & strfilename
            
        Case "TD"
            strfilename = "C:\Documents and Settings\Owner\Desktop\Tom Prop\" & strfilename
        Case "JK"
            strfilename = "C:\Documents and Settings\Owner\Desktop\Jon Knorr\" & strfilename
        Case "CP"
            strfilename = "C:\Documents and Settings\Owner\Desktop\Chuck Prop\" & strfilename
        Case "DA"
            strfilename = "C:\Documents and Settings\Owner\Desktop\David Prop\" & strfilename         
    End Select
    WB1.SaveCopyAs Filename:=strfilename
    WB1.ActiveSheet.Shapes("Button 53").Visible = False
    ChDir CurrPath
    Application.ScreenUpdating = True
    WB1.Close

I think this is the line I need changed? Not sure what i need it to change to?

WB1.SaveCopyAs Filename:=strfilename

Any help is greatly appreciated!
Michael

 

[daniels012]

bump

 

[daniels012]

bump

 

[hammerhead]

I searched over on MicroSoft and found this:

<snip..snip>

The 2007 Microsoft Office system introduces multiple changes to the way developers can work with Excel 2007. Specifically, Microsoft Office Excel 2007 has support for the Microsoft Save as PDF Add-in for the 2007 Office release program, which allows an Excel workbook to export and save to the PDF format and XPS format. This is very useful for building Office business applications in which the data input and analysis is dynamic, but the application output is static. For example, a mortgage calculator application may require input and analysis using Excel's core features, but the final workbook is a PDF or XPS document.

http://msdn.microsoft.com/en-us/library/bb407651.aspx

 

[Blade Hunter]

Mine does it with:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strfilename, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Not sure if that is because of an acrobat plugin though this is a ghost build.

 

[daniels012]

Hammerhead,
I have the download added. I just need to find out how to execute it using code.
Blade Hunter,
I see the code. I guess, I just am not sure how/where to put the code.

Michael

 

[Engineer Joe]

I'm pretty sure you'd put that code in place of where you regularly use the 'saveas' line.

 

[daniels012]

I tried that. the problem I am having is the "Case Select" I need something that has a .pdf extension not an .xls extention.

Other Ideas?
Michael

 

[daniels012]

bump

 

[Blade Hunter]

bump

change

strfilename = Range("S16").Value & ".xlsm"

to

strfilename = Range("S16").Value & ".pdf"