Closed and save PDF from Excel VBA

[SPoulis19]

Closed and save PDF from Excel VBA
I use the following code to convert an excel file to PDF.

Is it possible to save the PDF and closed it from VBA and not from the dialog box?

The code leads to the SaveAs dialog box.

The problem is that i use the code in a loop to save many excel files in a folder as pdf.
Excel must not closed because contains the addresses of the excel workbooks that i intented to convert to PDF.

for example:
C:\Users\sp\Desktop\CombineFolder - 20131211 - 1912\04 10 2013 3631.xls
C:\Users\sp\Desktop\CombineFolder - 20131211 - 1912\04 10 2013 3635.xls
C:\Users\sp\Desktop\CombineFolder - 20131211 - 1912\04 10 2013 3644.xls


Sub ExcelToPDF()

Dim iPtr As Long
Dim sFileName As String

iPtr = InStrRev(ActiveWorkbook.FullName, ".")


If iPtr = 0 Then
sFileName = ActiveWorkbook.FullName & ".pdf"
Else
sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".pdf"
End If


sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, FileFilter:="PDF Files (*.pdf), *.pdf")


If sFileName = "False" Then Exit Sub

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:=xlQualityStandard, openAfterPublish:=False

End Sub

 

[kchiba]

Hi,

I used Macro recording and got the following, that converts the sheet to a PDF, saves and closes the file

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

 

[SirJB7]

Hi, SPoulis19!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, I think that both your code and kchiba's one should accomplish the issue, via the ExportAsFixedFormat parameter OpenAfterPublish set to false, since it's the way to do the job. I usually use this method and the parameter works fine, doesn't it for you?

Regards!

PS: Now reading again, let me see if you wanted this:
- create the .pdf file and get it opened (from procedure 1)
- do anything with the .pdf started application
- close the .pdf file (from procedure 2, later)