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]

OK,
I have to have it as an Excel file first.
I guess I could try to have a 2nd one??? Something like
strfilepdf = Range("S16").Value & ".pdf"

The only problem is how do I change my "Case Select" options to convert to a pdf file using your code????

Michael

 

[Blade Hunter]

OK,
I have to have it as an Excel file first.
I guess I could try to have a 2nd one??? Something like
strfilepdf = Range("S16").Value & ".pdf"

The only problem is how do I change my "Case Select" options to convert to a pdf file using your code????

Michael

You don't need to change it you can just grab the filename minus the xlsm and whack a pdf on the end.

Your case statement remains, simply change the following

WB1.SaveCopyAs Filename:=strfilename

to

WB1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=left(strfilename,len(strfilename)-4) & "pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[daniels012]

Very nice!
You know, sometimes I try to make things so difficult.
Thank you for your help!

Michael

 

[daniels012]

ok, I thought all was good, because it was creating a pdf.

It is making a pdf of a range from my Sheet 1 that has nothing to do with what i am trying to copy??????

Any ideas or help on this would be great!

Michael

 

[daniels012]

Not sure why just the range?

 

[daniels012]

I had to change this:

WB1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=left(strfil...etc.

to this:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=left(strfil...etc.

It seems to be working now.
Thank you for all your help.
Michael