branston, please see this FAQ about PMs. http://www.vbaexpress.com/forum/faq....aq_req_help_pm
Anything you have asked in PMs can be done in the thread. Then, anyone can answer it or maybe they had the same question(s).
Regarding the merged files slow to show up after the MsgBox(), that is due to the delay in processing code versus actual work being done in the background/hidden shell calls. I found that some 3rd party applications take longer to do their thing than others. PDFill was fast. Likely, Acrobat will be too but I don't have it on this computer to test. There are Shell and Wait routines one can use rather than Shell() if waiting for the shell process to end is really needed.
3rd party apps have some neat little options that one can play with. Acrobat can usually do those and sometimes more but sometimes not. The extra command line parameter options for the apps are easily used. To do the same in Acrobat takes some additional coding and sometimes extensive coding.
Here is an example that marry's both my methods and snb's for Acrobat. Like the other examples using other 3rd party applications, it is easily modified to call them rather than Acrobat as I demonstrated in this thread.
I left this one in test mode. Simply comment out the line and uncomment the mAcrobat call's line to do the actual merge in Acrobat.
Sub MergeToAcrobat()
Dim a, v, f, i As Long, p As String
Dim p2 As String, r As String, fso As Object
Dim s As String, s2 As String, k As String
'Parent folder
'p = ThisWorkbook.Path & "\"
p = "C:\Users\lenovo1\Dropbox\_Excel\pdf\Acrobat\"
'Folder to copy merged pdfs in subfolders to, p2 initially, and r actual.
p2 = p & "MergedPDFs"
If Dir(p2, vbDirectory) = "" Then MkDir p2
'Make a new folder in p2 to store this run's merged pdf files.
Do
i = i + 1
r = p2 & "\Run" & i & "\"
Loop Until Dir(r, vbDirectory) = ""
MkDir r
Set fso = CreateObject("Scripting.FileSystemObject")
'All PDF files array
f = Split(CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
"""" & p & "*.pdf" & """" & " /b/s").StdOut.ReadAll, vbCrLf)
'Build string with folder names delimited by vbLF
For i = 0 To UBound(f) - 1
s = fso.GetParentFolderName(f(i)) & "\"
If InStr(s2, vbLf & s) = 0 Then s2 = s2 & vbLf & s
Next i
If s2 <> "" Then
a = Split(Mid(s2, 2), vbLf) 'Make array from s2 string but just elements with folder names.
For Each v In a
If InStr(v, p2) = 0 Then 'Process if folder v is not in p2, merged folder's path.
k = fso.GetFolder(v).Name & ".pdf"
mAcrobat1 Filter(f, v), r & k 'Test with output to VBE Immediate Window
'mAcrobat Filter(f, v), r & k 'Perform the merge in Acrobat
End If
Next v
End If
Set fso = Nothing
End Sub
Sub mAcrobat(sn, c00)
Dim j As Long, pdf As Object
With CreateObject("AcroExch.PDDoc").Open(sn(0))
For j = 1 To UBound(sn)
Set pdf = CreateObject("AcroExch.PDDoc").Open(sn(j))
.InsertPages .GetNumPages - 1, pdf, 0, pdf.GetNumPages, 0
pdf.Close
Next j
.Save 1, c00
.Close
End With
Set pdf = Nothing
End Sub
Sub mAcrobat1(sn, c00)
Dim j As Long
Debug.Print 0, sn(0)
For j = 1 To UBound(sn)
Debug.Print j, sn(j)
Next
Debug.Print "c00", c00
End Sub
Reply With Quote
Originally Posted by branston
