Microsoft Excel - VBA code to print and save excel files from a folder into PDF files in the same folder

Asked By Cherifa Hima on 28-Aug-13 01:41 PM
Hi,

Every month, I create many reports in excel for external use. I need to print all those files in PDF using a vba code. I am using the code below. But I need to be able to automate the save as ( same name as excel file).
Can anyone assist? Thanks.

Sub PrintWorkbooksintoPDF()

    PrintAllWorkbooksInFolder "C:\Users\username\Desktop\Production", "*.xlsx"

End Sub

Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)

    Dim fn As String
    Dim wb As Workbook
    Application.ScreenUpdating = False
    TargetFolder = "C:\Users\username\Desktop\Production"
    If Right(TargetFolder, 1) <> Application.PathSeparator Then
    TargetFolder = TargetFolder & Application.PathSeparator
    End If
    If FileFilter = "" Then FileFilter = "*.xls"
    fn = Dir(TargetFolder & FileFilter)
    While Len(fn) > 0
    If fn <> ThisWorkbook.Name Then
      Application.StatusBar = "Printing " & fn & "..."
      Workbooks.Open TargetFolder & fn, UpdateLinks:=False
     
      ActiveWorkbook.PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, Collate:=True, PrToFileName:="", IgnorePrintAreas:=True
      Application.SendKeys "~", True
      ActiveWorkbook.Close False
    End If
   
    fn = Dir
    Wend
    Application.StatusBar = False
   
End Sub

Robbe Morris replied to Cherifa Hima on 28-Aug-13 01:51 PM
What tool are you using to convert the Excel to PDF.  I don't recall Microsoft offering a native conversion tool.  As for printing programmatically, you'll either need to open the pdf file open with Adobe Reader via their command line arguments or purchase a PDF component from aspose.com.  You can't just "print" a PDF file.  You have to open it in a viewer of some sort and then issue a print command.
Cherifa Hima replied to Robbe Morris on 28-Aug-13 02:32 PM
I just go to excel file, select file, print, in the printer select Adobe. I have Adobe acrobat 9 standard.
But then I don't want to print a pdf file. I need to convert excel files into PDF using the print options from excel like I mentioned previously.
Harry Boughen replied to Cherifa Hima on 28-Aug-13 06:08 PM
Hello Cherifa,
I notice that you appear to pass the path to the routine but then have it hard coded in the routine.
There is a piece of freeware out there called TinyPDF that installs as a printer and saves the  output as YourFilename.pdf.  Not sure whether you can dynamically control the output folder though.  Perhaps it could be moved later with the VBA.
Will try to look at it more later when I get time.
Regards
Harry