For Excel 2007+:
[VBA]Sub Test_PublishToPDF()
Dim sDirectoryLocation As String, sName As String

sDirectoryLocation = ThisWorkbook.Path
sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
PublishToPDF sName, activeworksheet
End Sub


Sub PublishToPDF(fName As String, ws As Worksheet)
Dim rc As Variant

'ChDrive "c:"
'ChDir GetFolderName(fName)
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If Not rc Then Exit Sub

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub[/VBA]