Looping through columns to merge files into a PDF

[GMC111568]

I found this code which works great, but could someone please tell me how to modify it so that it will through cells to create the files?

For instance, first I want it to combine the files referenced in A1 and A2 and name/place it in the value that is in A3. Then, I want it to move onto Row B, and and use the values in B1, B2 and B3, then C1, C2 and C3, etc.

Thanks!

Sub Main() 
    Dim MyFiles As String, DestFile As String 
    With ActiveSheet 
        MyFiles = .Range("A1").Value & "," & .Range("A2").Value 
        DestFile = .Range("A3").Value 
    End With 
    Call MergePDFs01(MyFiles, DestFile) 
End Sub 
 
Sub MergePDFs01(MyFiles As String, DestFile As String) 
     ' ZVI:2016-12-10 
     ' Reference required: VBE - Tools - References - Acrobat
     
    Dim a As Variant, i As Long, n As Long, ni As Long 
    Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.AcroPDDoc 
     
    a = Split(MyFiles, ",") 
    ReDim PartDocs(0 To UBound(a)) 
     
    On Error GoTo exit_ 
    If Len(Dir(DestFile)) Then Kill DestFile 
    For i = 0 To UBound(a) 
         ' Check PDF file presence
        If Dir(Trim(a(i))) = "" Then 
            MsgBox "File not found" & vbLf & a(i), vbExclamation, "Canceled" 
            Exit For 
        End If 
         ' Open PDF document
        Set PartDocs(i) = New Acrobat.AcroPDDoc ' CreateObject("AcroExch.PDDoc")
        PartDocs(i).Open 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 & a(i), vbExclamation, "Canceled" 
            End If 
             ' Calc the amount of pages in the merged document
            n = n + ni 
             ' Release the memory
            PartDocs(i).Close 
            Set PartDocs(i) = Nothing 
        Else 
             ' Calc the amount 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, 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 is created:" & vbLf & 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 
     'DoEvents: DoEvents
    Set AcroApp = Nothing 
     
End Sub

[Bob Phillips]

Sub Main() 
    Dim MyFiles As String, DestFile As String 
    Dim lastcol As Long,  As Long

    With ActiveSheet 

        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For i = 1 to lastcol

            MyFiles = .Cells(1, i).Value & "," & .Cells(2, i).Value 
            DestFile = .Cells(3, i).Value 
            Call MergePDFs01(MyFiles, DestFile) 
        Next i
    End With 
End Sub

[GMC111568]

I’m getting a “Compile error: Syntax error” on “Dim lastcol As Long, As Long”

I removed the “, As Long” from the end, and it seems to work fine.

Thanks for your help.

[Kenneth Hobs]

I always recommend Option Explicit as first line in a Module. You can set that in your as Require Variable Declaration in VBE's Tools > Options.

It is always best to Compile in the Debug menu before a run if intellisense does not show the error while coding. I modify the VBE toolbar and put it next to Run.

[Bob Phillips]

I’m getting a “Compile error: Syntax error” on “Dim lastcol As Long, As Long”

I removed the “, As Long” from the end, and it seems to work fine.

Thanks for your help.

That should have been , i As Long, seems the I got lost somewhere.

Note what Kenneth said, I do the same, so I need to declare the variables.