combine the PDF files in a folder

[megatronixs]

Hi all,

I added to my database a button to ouput the data to PDF. It works as it should. We do print out the letters in paper and put a copy in the folder from the client (connected with the ID nr). From time to time we need to attach a PDF document to the letter and combine it. My question is now if it is possible to have some code to the button that when it finish the output to PDF it will combine all the PDF files that are in the folder and put it in a second folder.
After that, I need to put the combined PDF in another folder.

I hope some one can help out.

Greetings.

 

[pr2-eugin]

Not sure if you will be able to do this with simple Acrobat. You might need the complete Pro version to do stuff like this. I may be wrong !

 

[megatronixs]

Hi, I have Adobe Standard 7.0 (without it, I would not be able to do the printing to PDF, I guess). I managed to do the combining in Excel VBA, but I guess that I can´t use the Excel code just like that in Access as other thing I tried before did not work and needed to start from zero.

Maybe I could have a copy of the excel file in each folder and once I press a button, it will open the excel file and the excel macro will run. (in the excel version, the macro starts running as soon as the excel file opens and once it is done, it will close the excel file).
Is there a code that will look for a specific excel file in the folder an run it?


Greetings.

 

[redalert]

How about setting up a Word Mail Merge document for the letter and as the second page have data inserted into Word tables. I am assuming that you want the data in tabular format.

You will need to create a data source for the mail merge, run the merge from Access, save the new document and then run through a routine to insert the data etc.

Try this sample database for code ideas.

http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=22&Ite

 

[redalert]

I forgot to add that you can then save the Word document as a pdf file where you like and call it what you like.

 

[megatronixs]

Hi Redalert,

Thanks for the tips. I have already working the print to PDF and it really works great. The only thing I need is to combine all the PDF files that will be in the folder. I will post the code that I have in Excel to combine the PDF files and hope some one will be able to convert it to Access VBA

Greetings.

P.S. what about the code to start an excel file from a button? or mix it with another button so it will run at the end?

 

[megatronixs]

Hi all,

This is the macro in excel code (I hope some one can convert it to access vba):

Sub ListOnlyAllFiles()
Dim fs As FileSearch, ws As Worksheet, i As Long
Set fs = Application.FileSearch
With fs
.SearchSubFolders = False ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = ActiveWorkbook.Path

If .Execute > 0 Then
Set ws = Worksheets.Add
ActiveSheet.Name = "Folder Files"


For i = 1 To .FoundFiles.Count
ws.Cells(i, 1) = Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
Next
Else
MsgBox "No files found"
End If
End With
Columns.EntireColumn.AutoFit
Columns.EntireColumn.AutoFit
Range("A1:A300").Sort Key1:=Range("A1"), Order1:=xlDescending
Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
MergePDF 'calling the "main" function

End Sub
Sub MergePDF()
Dim Path1 As String, Path2 As String, Path3 As String, Path4 As String
Dim File_Path As String, Target_File_Path As String
File_Path = ActiveWorkbook.Path
newFilename = Range("A1")
Dim gPDDoc1 As AcroPDDoc
Dim gPDDoc2 As AcroPDDoc
'Dim gPDDoc3 As AcroPDDoc
'Dim gPDDoc4 As AcroPDDoc
Set gPDDoc1 = CreateObject("AcroExch.PDDoc")
x = newFilename
firstFilename = File_Path & "\" & x
'Debug.Print "ffn:", firstFilename
chk1 = gPDDoc1.Open(firstFilename)
'Debug.Print "chk1:", chk1
i = 0
'for all file names from column A
For Each x In Sheets("Folder Files").Range("A2:A20") '"PDFs_to_load")
len1 = Len(x.Value) - 3
'Debug.Print "X:", x
x = Trim(x)
'Debug.Print "X:", x
If x <> "" Then
Debug.Print "isPDF? s", Mid(x, len1, 4), "s"
If ".pdf" = Mid(x, len1, 4) Then
Set gPDDoc2 = CreateObject("AcroExch.PDDoc")
chk2 = gPDDoc2.Open(File_Path & "\\" & x)
'Debug.Print "C2:", chk2
'mergeFile = gPDDoc1.InsertPages(i, gPDDoc2, 0, 1, 0)
numpages2 = gPDDoc2.GetNumPages()
'Debug.Print "nump2:", numpages2
mergeFile = gPDDoc1.InsertPages(gPDDoc1.GetNumPages() - 1, gPDDoc2, 0, numpages2, 0)
'Debug.Print "mergeF", mergeFile
'Debug.Print "nextNumPages:", gPDDoc1.GetNumPages
i = i + 1
gPDDoc2.Close
End If
End If
Next

Target_File_Path = "C:\Letters\TO BE ADDED"
finalname = Target_File_Path & "\" & newFilename
'finalname = File_Path & "\new_" & newFilename
'Debug.Print "fn:", finalname
savemergefile = gPDDoc1.Save(1, finalname)
'Debug.Print "numpages:", gPDDoc1.GetNumPages
'Debug.Print "smf:", savemergefile
CloseNoSave 'runs the macro CloseNoSave to quit the excel file
End Sub
Sub CloseNoSave()
'Close the workbook without saving it
ThisWorkbook.Close savechanges:=False
End Sub