Why won't this VBA code loop through?

Question

I am trying to get it to print the files from folder in C2, which it does, and then go on to repeat for C3. It works fine the first time I run it, but when it tries for C3, it outputs the error sequence that it can't insert the pages, and then cannot save and there is no output file. My basic understand of VBA makes me think one of the integers or the array isn't 'resetting'.

What do you think? How can I fix so it'll loop through a few different folders to output.

    Sub MergePDFs()

    Dim a() As String, i As Long, n As Long, ni As Long, p As String, f As String
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc
    Dim DestFile As String '<-- change to suit
    Dim t As Integer
    Dim MyPath As String, MyFiles As String

     ' Choose the folder or just replace that part by: MyPath = Range("E3")
         '.InitialFileName = "C:\Temp\"
    For t = 0 To 1
    MyPath = Cells(t + 2, 3).Value
     DestFile = Cells(t + 2, 1).Value & ".pdf"

     ' Populate the array a() by PDF file names
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    ReDim a(1 To 2 ^ 14)
    f = Dir(MyPath & "*.pdf")
    While Len(f)
        If StrComp(f, DestFile, vbTextCompare) Then
            i = i + 1
            a(i) = f
        End If
        f = Dir()
    Wend

     ' Merge PDFs
    If i Then
        ReDim Preserve a(1 To i)
        MyFiles = Join(a, ",")
        Application.StatusBar = "Merging, please wait ..."
        Application.StatusBar = False


    If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
    a = Split(MyFiles, ",")
    ReDim PartDocs(0 To UBound(a))

    On Error GoTo exit_
    If Len(Dir(p & DestFile)) Then Kill p & DestFile
    For i = 0 To UBound(a)
         ' Check PDF file presence
        If Dir(p & Trim(a(i))) = "" Then
            MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
            Exit For
        End If
         ' Open PDF document
        Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open p & Trim(a(i))
        If i Then
             ' Merge PDF to PartDocs(0) document
            ni = PartDocs(i).GetNumPages()
            If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
                MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
            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
             ' Calc the number of pages in PartDocs(0) document
            n = PartDocs(0).GetNumPages()
        End If
    Next

    If i > UBound(a) Then
         ' Save the merged document to DestFile
        If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
            MsgBox "Cannot save the resulting document" & vbLf & p & 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 is created:" & 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
    Else
        MsgBox "No PDF files found in" & vbLf & MyPath, vbExclamation, "Canceled"
    End If
    Next t
End Sub

What line does the error occur on? If you take out the error handler, what error do you get? — BruceWayne
– BruceWayne, Commented Mar 7, 2017 at 20:42
Unless you use Static local variables or Global variables, there should be no issue with values "sticking" between calls. — Tim Williams
– Tim Williams, Commented Mar 7, 2017 at 20:43
What do you mean take out the error handler? Sorry I'm a novice. — Matt Cottrill
– Matt Cottrill, Commented Mar 7, 2017 at 20:46

YowE3K · Accepted Answer · 2017-03-07 21:22:09Z

2

After the start of your For t loop, reset the value of i:

For t = 0 To 1
    i = 0

If you currently have 5 files in the first directory, the files from the second directory will currently be placed in positions 6+ of your array, with the first five positions being set to blanks. That will undoubtedly cause problems later when you try to access those "blank" filenames.

By resetting your counter, the new filenames will be placed in positions 1+ of your array.

edited Mar 7, 2017 at 21:22

answered Mar 7, 2017 at 21:18

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Cottrill Over a year ago

You are my hero. That was successful. Thank you!

YowE3K Over a year ago

@MattCottrill If you accept the answer, and vote up, I will hit "200+ rep in one day" for the very first time and earn myself a new badge!

Matt Cottrill Over a year ago

Ah I already upvoted you the second I saw it worked :/ I'm a new user so it doesn't show up or something until I have 15 rep.

YowE3K Over a year ago

@MattCottrill - Woohoo - It tells me "You've earned the Mortarboard badge"!!! Many thanks. (And, interestingly, it shows me as having gained 210 rep yesterday, even though the max is 200 - not sure how that works.)

Shai Rado Over a year ago

@YowE3K here another +1 from me also :)

|

Collectives™ on Stack Overflow

Why won't this VBA code loop through?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related