Printing excel sheet to pdf

Hello

This is my first post in this forum, although I've being around reading some posts which had help me programming some VBA routines in excel (I'm a rookie). I've registered because I've went up against a wall with a problem in a coding I'm adapting. I've searched in web and in this forum I haven't found any similar thread, so here it goes.

I'm creating a routine in a excel workbook that:
1st: prints an excel sheet into a pdf file in directory 'Certificados' with the name of cell O5
2nd: combine the above pdf file with a 2nd pdf file (which have a varying name, according to cell D9 of the above mentioned sheet), located in the same directory. For this I've an private function 'MergePDFs'.

I've installed Adobe DC Pro and I've checked 'Adobe Acrobat 10.0 Type Library' in VBA references linked to a *.tlb file.
The 1st step of the routine works well and the 2nd retrieves the message 'Failed to combine all PDFs'. this could be due to inexisting pdf files to merge or some kind of error with the adobe library. The files are in the folder so I presume this problem in proceeding with the routine should be related with *.tlb file or some *.dll so It's possible to open the pdf files...Eventually this is due to some dumb error :think:
I thank you any help you can provide. Bellow the code.

______________________________

Code:

---

Private Function MergePDFs(arrFiles() As String, strSaveAs As String) As Boolean
    Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
    Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
    Dim i As Integer
    Dim iFailed As Integer
    On Error GoTo NoAcrobat:
    'Initialize the Acrobat objects
    Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
    ' Open Destination, all other documents will be added to this and saved with a new filename
    objCAcroPDDocDestination.Open (arrFiles(LBound(arrFiles))) 'open the first file
    ' Open each subsequent PDF that you want to add to the original
    ' Open the source document that will be added to the destination
    For i = LBound(arrFiles) + 1 To UBound(arrFiles)
        objCAcroPDDocSource.Open (arrFiles(i))
        If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MergePDFs = True
        Else
            ' failed to merge one of the PDFs
            iFailed = iFailed + 1
        End If
            objCAcroPDDocSource.Close
    Next i
    objCAcroPDDocDestination.Save 1, strSaveAs 'Save it as a new name
    objCAcroPDDocDestination.Close
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDestination = Nothing
    NoAcrobat:
    If iFailed <> 0 Then
        MergePDFs = False
    End If
    On Error GoTo 0
End Function

Sub Main()
    ' ExportAsPDF
    Dim FolderPath As String
    FolderPath = "C:\Users\ITG-0720\Certificados"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "" & Sheets("Análise CC").Range("O5"), _
    openafterpublish:=False, ignoreprintareas:=False
    MsgBox "All PDF's have been successfully exported."
    ' Combine_PDFs()
    Dim strPDFs(0 To 1) As String
    Dim bSuccess As Boolean
    FolderPath = "C:\Users\ITG-0720\Certificados"
    strPDFs(0) = FolderPath & "" & Sheets("Análise CC").Range("D9")
    strPDFs(1) = FolderPath & "" & Sheets("Análise CC").Range("O5")
    bSuccess = MergePDFs(strPDFs, FolderPath & "" & Sheets("Análise CC").Range("O5"))
    If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"
End Sub

---

Welcome to the forum!

When posting code, please paste between code tags. Click the # icon on reply toolbar to insert the tags.

Reference the Acrobat object. A Compile from VBE's Debug menu should show if your code has the proper reference set.

It is unclear what values are in D9 and 05. If they don't have a suffix of ".pdf" then you need to concatenate those. While you can leave them out in ExportAsFixedFormat, concatenating without them won't work in the MergePDFs(). It should be added to your StrSaveAs string value as well. Your passed string array should include the ()'s. e.g.

Code:

---

bSuccess = MergePDFs(strPDFs(), FolderPath & "\" & Sheets("Análise CC").Range("O5") & ".pdf") 'Only if O5 has suffix ".pdf".

---

Quote:

---

Originally Posted by Kenneth Hobs

Welcome to the forum!

When posting code, please paste between code tags. Click the # icon on reply toolbar to insert the tags.

Reference the Acrobat object. A Compile from VBE's Debug menu should show if your code has the proper reference set.

It is unclear what values are in D9 and 05. If they don't have a suffix of ".pdf" then you need to concatenate those. While you can leave them out in ExportAsFixedFormat, concatenating without them won't work in the MergePDFs(). It should be added to your StrSaveAs string value as well. Your passed string array should include the ()'s. e.g.

Code:

---

bSuccess = MergePDFs(strPDFs(), FolderPath & "\" & Sheets("Análise CC").Range("O5") & ".pdf") 'Only if O5 has suffix ".pdf".

---

---

Hi

Thanks for your reply. I tried the change you've proposed but with no success. The debug compile don't detect any error in the code so I presume the failure is related to adobe initialization...I even tried to put the full path and name of the files but with no success...Thanks for the advise.

Code:

---

' Combine_PDFs()
Dim strPDFs(0 To 1) As String
Dim bSuccess As Boolean
Dim strSaveAs As String
FolderPath = "C:\Users\ITG-0720\Certificados"
strPDFs(0) = FolderPath & "" & Sheets("Análise CC").Range("D9") & ".pdf"
strPDFs(1) = FolderPath & "" & Sheets("Análise CC").Range("O5") & ".pdf"
strSaveAs = FolderPath & "" & Sheets("Análise CC").Range("O5") & ".pdf"
bSuccess = MergePDFs(strPDFs, strSaveAs)
If bSuccess = False Then MsgBox "Failed to combine all PDFs", vbCritical, "Failed to Merge PDFs"
End Sub

---

Try comparing your code to what was posted and discussed in: http://www.vbaexpress.com/forum/show...dobe-acrobat-X

When typing as in your post #3 code tags, replace <>'s with []'s.

From your code in post #1, the backslash character is not being shown. e.g.

Code:

---

'strPDFs(0) = FolderPath & "" & Sheets("Análise CC").Range("D9")
strPDFs(0) = FolderPath & "\" & Sheets("Análise CC").Range("D9")

---

Hi

Strange, the backlash is in my code. For some reason it didn't copy for post #1.
I've tried the code you posted and found something interesting. The routine stops when is checking for the file presence because it don't find the file which name is in cell D9 (CL-2497GA-19.pdf). I change the name, both in the filename and in cell D9, and also change the location of the cell and the message is the same: 'file not found'. I imagine this is why the code I post in #1 is not working. But why is this happening?

I guess that is your username in the path? Not found is due to path or filename not existing.

Well, yes, is my username and the file exists and I've tried also to change the location of the files to the root directory but with no success. One of the file I include in 'Myfiles' is originated from the 1st part of the routine, which is the printing of an excel sheet into a pdf file with the name of cell O5 of the active worksheet. For that routine there is no problem for VBA to find the folder directory.

I still think your issue is file existing or not. You can easily test that but if the routine says it doesn't, that is the problem.

In a few days, I can test on a computer with acrobat.

Later today I could show a short exists test routine.

I thank you. It seems so. I don't think the problem is related with the location of the file but with the pdf file existence. I search the file in cmd and it gives 'file not found'.

Concatenating the .pdf is likely needed as I explained.

My column A files have the filename.pdf values. C2 has the drive:\path\ for the column A files. e.g. "c:\temp". B2 has the full drive:\path\filename.pdf for the merged file.

The test routine puts the debug.print results into VBE's Immediate window after a run.

Code:

---

Private Sub Test_FilesExist() 
    Dim e, a
    a = WorksheetFunction.Transpose(Range("A2", Range("A2").End(xlDown)).Value)
    For Each e In a
        Debug.Print e, "Exists: ", Dir(e) <> ""
    Next e
End Sub

Private Sub CommandButton1_Click()
    Dim a As Variant
    a = WorksheetFunction.Transpose(Range("A2", Range("A2").End(xlDown)).Value)
  MergePDFs Range("C2").Value2, Join(a, ","), Range("B2").Value2
End Sub

Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
    ' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
    ' Reference required: VBE - Tools - References - Acrobat
    Dim a As Variant, i As Long, n As Long, ni As Long, p As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Adjust MyPath string if needed.
    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))
    ' Save to MyPath folder if target folder for merged PDF file was not input. (ken)
    If InStr(DestFile, "\") = 0 Then DestFile = p & DestFile
    On Error GoTo exit_
    If fso.FileExists(p & DestFile) Then Kill p & DestFile
    For i = 0 To UBound(a)
        ' Check PDF file presence
        If Not fso.FileExists(p & a(i)) Then
            Debug.Print "File not found" & vbLf & p & a(i)
            GoTo ElseI
        End If
        ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & a(i)
        ni = PartDocs(i).GetNumPages()
        If i > 0 And ni > 0 Then
            ' Merge PDF to PartDocs(0) document
            If Not PartDocs(0).InsertPages(n, PartDocs(i), 0, ni, True) Then
                Debug.Print "Cannot insert pages of" & vbLf & p & a(i)
            End If
            ' Calc the number of pages in the merged document
            n = n + ni
            ' Release the memory
            PartDocs(i).Close
            Set PartDocs(i) = Nothing
        Else
            ElseI:
            ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next i
    If i > UBound(a) Then
        ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
        End If
    End If
    exit_:
    ' Inform about error/success
    If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
    ElseIf i > UBound(a) Then
        MsgBox "The resulting file was created in:" & vbLf & p & DestFile, vbInformation, "Done"
    End If
    ' Release the memory
    If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
    Set PartDocs(0) = Nothing
    ' Quit Acrobat application
    AcroApp.Exit
    Set AcroApp = Nothing
    Set fso = Nothing
End Sub


Function GetFolder(Optional sTitle As String = "Select Folder", Optional sInitialFilename As String)
    Dim myFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If sInitialFilename = "" Then sInitialFilename = ThisWorkbook.Path
            .InitialFileName = sInitialFilename
            .Title = sTitle
            If .Show = -1 Then
                GetFolder = .SelectedItems(1)
                If Right(GetFolder, 1) <> "\" Then
                  GetFolder = GetFolder & "\"
                End If
            Else: GetFolder = ""
        End If
    End With
End Function

---

Hi, thanks once again. I added your code to my module, adapting the cells for my sheet, but I'm getting a error related wiith function 'MergePDFs' in the 'Private Sub CommandButton1_Click' due to the arguments of MergePDFs which is defined in a sub-routine as MergePDFs(MyFiles As String, DestFile As String)...

Attach your file if you want me to troubleshoot it.

Hi, The file is available in the following dropbox link: https://www.dropbox.com/s/cjzmxuvh9j...vVBA.xlsm?dl=0

Thanks

It is best to attach a file using the forum's method. Dropbox method is good for forums that don't allow attachments.

It looks like your problem is the file not existing. The Adobe routine worked fine. I removed all of the Adobe references except for the Adobe Acrobat 10.0 Library.

This first routine is like the Main() in your Module7. Note how I concatenated the FolderPath that you had commented out. I like to add the trailing backslash character then for fewer concatenations.

Code:

---

Sub Module7_Ken1() 
    Dim MyFiles As String, DestFile As String
    Dim FolderPath As String, e
    FolderPath = "C:\Users\ITG-0720\OneDrive - INSTITUTO TECNOLOGICO DO GAS\ITG\DMM\"
    With Worksheets("Análise CC")
        MyFiles = FolderPath & .Range("N1").Value & "," & FolderPath & .Range("N2").Value
        DestFile = .Range("O1").Value
    End With
    For Each e In Split(MyFiles, ",")
        Debug.Print e, "Exists: ", dir(e) <> ""
    Next e
    Debug.Print DestFile, "Exists: ", dir(DestFile) <> ""
    ' MergePDFs01 MyFiles, DestFile
End Sub

' In this 2nd routine, I just used my paths and filenames and called the Acrobat routine.
Sub Module7_Ken2()
    Dim MyFiles As String, DestFile As String
    Dim FolderPath As String, e
    FolderPath = ThisWorkbook.Path & "\"
    FolderPath = "C:\Users\ken\Dropbox\_Excel\pdf\Acrobat\Sets\Set A\"
    MyFiles = FolderPath & "File 1.pdf" & "," & FolderPath & "File 2.pdf"
    DestFile = FolderPath & "File 12.pdf"
    For Each e In Split(MyFiles, ",")
        Debug.Print e, "Exists: ", dir(e) <> ""
    Next e
    Debug.Print DestFile, "Exists: ", dir(DestFile) <> ""
    MergePDFs01 MyFiles, DestFile
End Sub

---

Once you get it working, you can remove or comment out the Debug.Print lines...

Hi! In future threads I'll attach files directily. I thank you for the precious help. It's working! Well, it has some minor setbacks:
1. When I put the file 'CL-4268GA-19.pdf' (cell D9) before 'RIS 454.pdf' (cell O5) the code is not able to insert 'RIS 454.pdf' into the 1st one. But the code works perfectly when changing their positions
2. The code does not work correctly when the destfile has the same name as one of myfiles: it deletes the myfile file but does not replace for the new merged one (with the same file name) . Anyway, this can be overcome by creating a subfolder for destfiles and/or changing the name of the file to merge

I don't know that I have seen (1) but I can test in a 5 days maybe. I suspect it may be a (2) issue?

(2) prevents you from shooting yourself in the foot...

Attachment 23840

Hi! Sorry for not answering sooner but this week we had a carnival holiday...I thank you once again for your availability. I made some changes in the code (including changing the destiny folder) so I'm attachning the excel workbook along with one of the pdf files to be merged ('CL-5208PR-19.pdf'). The other pdf file is generated from the excel sheet 'Análise CC', which generates 'RIS 528.pdf' file. As I said In my previous thread, the code is working fine but it does not allow me to insert the 'RIS 528.pdf' file into 'CL-5208PR-19.pdf' (I would like this one to be the front page). If you can figure why is this happening I would appreciate.

The problem is likely with CL-5208PR-19.pdf. It has a signature that needs validating. Open the file and fix that and then test.


Modules 3 and 7 have similar named routines. I guess you mean Module3.Main() but it has no End Sub so I am not sure how you tested it.

I recommend:
1. Fixing name conflicts at start: _FilterDatabase, Print_Area.
2. In VBE, select menus Debug > Compile Project and fix problems.
3. Do not use same name routines in multiple objects. While you technically can, it is confusing for me and likely you at some point.

You're right, it's messy. I'll work that out... The working code is in module 3. The file 'CL-5208PR-19.pdf' (and others alike) are from 3rd party companies and can't (shouldn't) be changed. I have overcome the setbacks I've mentioned in my previous thread with minour changes, like changing the destiny folder and changing orders of the files, although the 3rd party files should be on top...

Then you will need to drop an Acrobat solution. Use a 3rd party like pdfcreate, pdfSam, or such.