Make Array with Full Paths to PDF Files in Parent and Subfolders

Code:

---

'Similar to: NateO's code, http://www.mrexcel.com/forum/showpost.php?p=1228168&postcount=2
Function aFiles(strDir As String, searchTerm As String, _
  Optional SubFolders As Boolean = True)
  Dim fso As Object
  Dim strName As String
  Dim i As Long
  ReDim strArr(1 To Rows.Count)
 
  'strDir must not have a trailing \ for subFolders=True
  If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
 
  'Exit if strDir does not exist
  If Dir(strDir, vbDirectory) = "" Then Exit Function
   
  Let strName = Dir$(strDir & searchTerm)
  Do While strName <> vbNullString
      Let i = i + 1
      Let strArr(i) = strDir & strName
      Let strName = Dir$()
  Loop
  Set fso = CreateObject("Scripting.FileSystemObject")
  'Strip trailing \ if subFolders=False
  If SubFolders = False Then strDir = Left(strDir, Len(strDir) - 1)
    Call recurseSubFolders(fso.GetFolder(strDir), strArr, i, searchTerm)
    Set fso = Nothing
    If i = 0 Then i = 1 'Returns one empty array element in strArr
    ReDim Preserve strArr(1 To i)
    aFiles = strArr
End Function


Private Sub recurseSubFolders(ByRef Folder As Object, _
    ByRef strArr, _
    ByRef i As Long, _
    ByRef searchTerm As String)
    Dim SubFolder As Object
    Dim strName As String
    For Each SubFolder In Folder.SubFolders
        Let strName = Dir$(SubFolder.Path & "\" & searchTerm)
        Do While strName <> vbNullString
            Let i = i + 1
            Let strArr(i) = SubFolder.Path & "\" & strName
            Let strName = Dir$()
        Loop
        recurseSubFolders SubFolder, strArr, i, searchTerm
    Next
End Sub

---

Quote:

---

Originally Posted by branston

Type mismatch here in 2nd sub routine

Code:

---

      Set pdf = CreateObject("AcroExch.PDDoc").Open(sn(j))

---

---

Why don't you give meaningful feedback ?
What is the value of sn(j) ?

SNB does concise code but it may be hard for some to see how it works.

After a run, Debug.Print's output can be viewed in VBE's Immediate Window. Change the parts in red to suit and view the results after a run.

The Replace() routine to make c00 for "it" needs a tweak as a colon is not a legal filename.

Code:

---

Sub M_snb()   
    sn = Split(CreateObject("wscript.shell").Exec("cmd /c dir ""C:\Users\lenovo1\Dropbox\_Excel\pdf\Acrobat\*.pdf"" /b/s").StdOut.ReadAll, vbCrLf)
   
    For j = 1 To UBound(sn) - 1
      c00 = Left(sn(j), Len(sn(j)) - Len(Dir(sn(j))))
      If InStr(c01, vbLf & c00) = 0 Then c01 = c01 & vbLf & c00
    Next
   
    If c01 <> "" Then
      sp = Split(Mid(c01, 2), vbLf)
      For Each it In sp
          M_MergePDF1 Filter(sn, it), "C:\Users\lenovo1\Dropbox\_Excel\pdf\Acrobat\Merged\Run2\" & Replace(it, "\", "_") & "pdf"
      Next
    End If
End Sub


Sub M_MergePDF1(sn, c00)
  Debug.Print 0, sn(0)
    For j = 1 To UBound(sn)
      Debug.Print j, sn(j)
    Next
    Debug.Print "c00", c00
End Sub

---

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.

Code:

---

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

---

Thanks Ken - and point taken.

will try the code but my point was actually about why the merged files are still being merged (into a new file with the parent folder.pdf as it’s name) once they are in the mergedPDFs folder. It’s an unnecessary file but I couldn’t work out where in the code this final step of merging the merged files was happening.

anyway will try the suggestions in your post.