Excel PDF viewer

DavidH7470
DavidH7470 used Ask the Experts™
on
I use a spreadsheet that produces a pdf print of a page automotically from a macro.  The pdf's are gathered in a folder by date as they are printed so that in that days folder there are many pdf's gathered for that days work.  I would like to know is there is way to write a macro that could take the date from a cell that the user inputs and have it compile all the pdf's in the corresponding folder and return it as a single pdf with all of the pages from the folder?  Sort of like in a book form?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's a solution that appears identical to your needs and can be customized from there:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26980351.html

You can append PDF's together, but you have to have software that supports that - Acrobat Writer or Pro, for example.  Do you have this software?  If so, then we can use VBA to append the PDF's based on the date range.

Please advise what PDF writing software/version number you have.

Dave

Author

Commented:
Hi Dave:

I reviewed your answer(s) and I can follow some of it.  To make it more clear for me I thought I would give you an example.  All of my pdf's are in folders representing the day they were created.  ie

C:\MyPDFs\2012\May\21\

In this folder there are numerours PDFs with different names that I would like merged into 1 PDF.

So the Marco I would like would need to have the file folder named in a cell (C:\MyPDFs\2012\May\21\) and take all the PDF's in that folder and merge them into a single PDF.  Ideally it would ask the user if they would like to view the files now and also create a new PDF with all the PDF's merged incase the user wants to look at them again later without going through the same routine.

That being said, where in the code you sent does it use C:\MyPDFs\2012\May\21\ as a variable?  Can I then name the output PDF by a name that I want it be saved as?

Hope this helps.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Dave - The solution I posted was an EXAMPLE working solution.  You could actually download that solution to see if it worked with your configuration.

I'll help you set this up (re: the details you outlined, above), however, you need to advise that you have the appropriate software to allow this to happen.  Do you and your users have Acrobat Standard, Writer, or Pro?  If they just have READER, this will not work.

Dave
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Hi Dave:

I do have the Adobe X so it should work fine.  I alrealy use a macro from a spreadsheet to create the individual pdf's which has distiller selected in the references in VBA.

Thanks again for your help.  

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok.  Thanks for that clarity.

You want an app that will:

1.  Prompt for the folder of PDF's (or just take the path from the cell?)
2.  Prompt the user to view the files as part of the merge process?  How would you see them viewed?
3.  Prompt the user (or take from a cell) the final PDF filename.

Can you opine on the 3 above, so we're clear?

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok, I've given you what I think in the best of both worlds.  You can hard code your folder and save as filename in the Control Panel tab I've created in this workbook.  If either one is empty, you'll be prompted for the path and/or merged pdf filename.

The app traverses all PDF files in the identified folder and merges them together (you have, as yet, to specify the order, so at this point, they are just sorted by name).  The status bar at the bottom of Excel shows you what file is currently being processed/merged.  You'll get a "Process Complete!" prompt at the end, where you'll be asked if you want to view the merged PDF file.

Here's the code in a public module:
Sub pdfViewMerge()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fName As String
Dim dialogFile As FileDialog
Dim strPath As String
Dim fNamePDF As String
Dim iCountPDFs As Long
Dim xMsg As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    strPath = wks.Range("A1").Value
    fNamePDF = wks.Range("A3").Value
    
    'prompt user for new, merged PDF filename
    If fNamePDF = vbNullString Then
        fNamePDF = Application.GetSaveAsFilename(InitialFileName:=wkb.Path & "\TestPDF", filefilter:="PDF Files, *.PDF", FilterIndex:=1, Title:="Enter new PDF filename")
    End If
    
    If fNamePDF <> "False" Then 'got merged pdf file name
        If Dir(fNamePDF) <> vbNullString Then
            Kill fNamePDF
        End If
    
        If strPath = vbNullString Then
            strPath = wkb.Path & "\"
            'prompt user for path of PDF files
            Set dialogFile = Application.FileDialog(msoFileDialogFolderPicker)
            With dialogFile
                .AllowMultiSelect = False
                .InitialView = msoFileDialogViewDetails
                .InitialFileName = strPath
                .Title = "Select PDF Merge Path"
                .Show
            End With
            
            If dialogFile.SelectedItems.Count > 0 Then 'found path
                strPath = dialogFile.SelectedItems(1)
            Else
                strPath = vbNullString
            End If
        End If
        
        If strPath <> vbNullString Then
            fName = Dir(strPath & "\*.PDF")
            If fName <> vbNullString Then

                Do
                    Application.StatusBar = "Processing File: " & fName
                    'merge to pdf file
                    If iCountPDFs = 0 Then 'make initial PDF from source
                        FileCopy Source:=strPath & "\" & fName, Destination:=fNamePDF
                    Else 'start the merge/append process
                        Call MergePDF(fNamePDF, strPath & "\" & fName, fNamePDF, True)
                    End If
                    iCountPDFs = iCountPDFs + 1
                    fName = Dir()
                Loop While fName <> vbNullString
            End If
            xMsg = MsgBox("Process Complete!" & vbCrLf & vbCrLf & "Do you want to view your newly merged PDF File?: " & fNamePDF, vbYesNo, "Hit YES to view Merged PDF File")
            If xMsg = vbYes Then
                Call viewPDF(fNamePDF)
            End If
        End If
    End If
    Application.StatusBar = False
End Sub
Private Sub viewPDF(fNamePDF As String)
'Relies on the Adobe Acrobat X.X Type Library - just add Tools->References->Acrobat and it will work, accordingly
Dim objAcroApp As Object
Dim objCAcroPDDoc As Object
Dim avDOC As Object
Dim xMsg As Integer
Dim bSuccess As Boolean

    'Initialize the objects
    Set objAcroApp = CreateObject("AcroExch.App")
    Set objCAcroPDDoc = CreateObject("AcroExch.PDDoc")

    If objCAcroPDDoc.Open(fNamePDF) Then
            
            objAcroApp.Show
            
            Set avDOC = objCAcroPDDoc.OpenAVDoc("")
            
            MsgBox "Hit Ok to close it out and continue...", vbOKOnly, "Hit Ok, Ok? :)"
            
            objAcroApp.Hide
            
            avDOC.Close (True)

            Set avDOC = Nothing
        
    End If
    
    objCAcroPDDoc.Close
    
    Set objCAcroPDDoc = Nothing
    
    objAcroApp.Exit
    
    Set objAcroApp = Nothing

End Sub
Private Sub MergePDF(sourceAppend As String, destStart As String, finishOut As String, bSilent As Boolean)
'Relies on the Adobe Acrobat X.X Type Library - just add Tools->References->Acrobat and it will work, accordingly
Dim objAcroApp As Object
Dim objCAcroPDDocDest As Object
Dim objCAcroPDDocSource As Object
Dim avDOC As Object
Dim xMsg As Integer
Dim bSuccess As Boolean

    'Initialize the objects
    Set objAcroApp = CreateObject("AcroExch.App")
    Set objCAcroPDDocDest = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")

    If objCAcroPDDocDest.Open(destStart) Then

          objCAcroPDDocSource.Open (sourceAppend)
          If objCAcroPDDocDest.InsertPages(objCAcroPDDocDest.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            bSuccess = True
          Else
            '0 problem
          End If
          objCAcroPDDocSource.Close
        
        objCAcroPDDocDest.Save 1, finishOut
        
    End If

    If bSuccess And Not bSilent Then

        xMsg = MsgBox("Documents Merged!  Open and Review?", vbYesNo, "Hit Yes to open PDF for review")
        
        If xMsg = vbYes Then
            
            objAcroApp.Show
            
            Set avDOC = objCAcroPDDocDest.OpenAVDoc("")
            
            MsgBox "Hit Ok to close it out and continue...", vbOKOnly, "Hit Ok, Ok? :)"
            
            objAcroApp.Hide
            
            avDOC.Close (True)

            Set avDOC = Nothing
        End If
        
    End If
    
        objCAcroPDDocSource.Close
        objCAcroPDDocDest.Close
        
        Set objCAcroPDDocSource = Nothing
        Set objCAcroPDDocDest = Nothing
        
        objAcroApp.Exit
        
        Set objAcroApp = Nothing

End Sub

Open in new window


See attached.

Enjoy!

Dave
PDF-Merge-View-r1.xls

Author

Commented:
That works great.  Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial