Printing excel sheet to pdf

[Paulo Zoio]

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.

______________________________


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

[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.


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

[Paulo Zoio]

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.


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</code>

[Kenneth Hobs]

Try comparing your code to what was posted and discussed in: http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-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.

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

[Paulo Zoio]

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?

[Kenneth Hobs]

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

[Paulo Zoio]

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.

[Kenneth Hobs]

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.

[Paulo Zoio]

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'.

[Kenneth Hobs]

Concatenating the .pdf is likely needed as I explained.

[Kenneth Hobs]

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.


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

[Paulo Zoio]

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)...

[Kenneth Hobs]

Attach your file if you want me to troubleshoot it.

[Paulo Zoio]

Hi, The file is available in the following dropbox link: https://www.dropbox.com/s/cjzmxuvh9jgmhq2/2019.02.27-Registo%20DMM.vVBA.xlsm?dl=0

Thanks

[Kenneth Hobs]

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.


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...

[Paulo Zoio]

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

[Kenneth Hobs]

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...

[Paulo Zoio]

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.

[Kenneth Hobs]

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.

[Paulo Zoio]

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...

[Kenneth Hobs]

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