VBA, Array range error, subscript out of range

Question

Hi I am trying to make my array more dynamic to include all file paths in column G. However I have trouble doing that as I am getting subscript out of range whenever I try something like arrayFilePaths = Range("G4:G5") or any solution from here, Put entire column (each value in column) in an array?

I get type mismatch or subscript out of range on the OK = primaryDoc.Open(arrayFilePaths(0)) line.

My code:

Sub main()

        Dim arrayFilePaths() As Variant
        Set app = CreateObject("Acroexch.app")

        arrayFilePaths = Array(Range("G4"), Range("G5"))

        Set primaryDoc = CreateObject("AcroExch.PDDoc")
        OK = primaryDoc.Open(arrayFilePaths(0))
        Debug.Print "PRIMARY DOC OPENED & PDDOC SET: " & OK

        For arrayIndex = 1 To UBound(arrayFilePaths)
            numPages = primaryDoc.GetNumPages() - 1

            Set sourceDoc = CreateObject("AcroExch.PDDoc")
            OK = sourceDoc.Open(arrayFilePaths(arrayIndex))
            Debug.Print "SOURCE DOC OPENED & PDDOC SET: " & OK

            numberOfPagesToInsert = sourceDoc.GetNumPages

            OK = primaryDoc.InsertPages(numPages, sourceDoc, 0, numberOfPagesToInsert, False)
            Debug.Print "PAGES INSERTED SUCCESSFULLY: " & OK

            OK = primaryDoc.Save(PDSaveFull, arrayFilePaths(0))
            Debug.Print "PRIMARYDOC SAVED PROPERLY: " & OK

            Set sourceDoc = Nothing
        Next arrayIndex

        Set primaryDoc = Nothing
        app.Exit
        Set app = Nothing
        MsgBox "DONE"
    End Sub

Add .Value so vba does not try to put the range itself, but the values in the array. — Scott Craner
– Scott Craner, Commented Aug 14, 2018 at 13:41
@ScottCraner , still getting same error, this is my new line arrayFilePaths = Range("G4:G5").Value — excelguy
– excelguy, Commented Aug 14, 2018 at 13:43
@ScottCraner without a Set keyword, it's always the class' default property if no member is specified :) — Mathieu Guindon
– Mathieu Guindon, Commented Aug 14, 2018 at 13:46
with setting the array with arrayFilePaths = Range("G4:G5").Value the first location is probably 1 not 0 — Scott Craner
– Scott Craner, Commented Aug 14, 2018 at 13:47
@ScottCraner but arrayFilePaths = Range("G4:G5").Value produces a 2-dimensional array so the first element is actually arrayFilePaths(1, 1) or you need to transpose it. — Pᴇʜ
– Pᴇʜ, Commented Aug 14, 2018 at 13:50

learnAsWeGo · Accepted Answer · 2018-08-14 15:01:13Z

1

Rather than use an array I am now using a collection to make things a bit easier. Also I moved the save file outside of our loop so that we aren't saving the file every time that we insert into it.

The pathway to your primary document, aka the one that you will insert into, has been set at index 1. The for each cell in range loop will add pathways to the files that you want to insert into that primary document starting at index 2.

Also I modified the error messaging a bit (tell you what index it is failing at)

Sub main()

    Set app = CreateObject("Acroexch.app")

    Dim FilePaths As Collection
    Set FilePaths = New Collection

    FilePaths.Add "PRIMARY DOC PATHWAY HERE"

    Dim cell As Range
    For Each cell In Range("G4:G5")
        FilePaths.Add cell.Value
    Next cell

    Set primaryDoc = CreateObject("AcroExch.PDDoc")
    OK = primaryDoc.Open(FilePaths(1))
    Debug.Print "PRIMARY DOC OPENED & PDDOC SET: " & OK

    For colIndex = 2 To FilePaths.Count
        numPages = primaryDoc.GetNumPages() - 1

        Set sourceDoc = CreateObject("AcroExch.PDDoc")
        OK = sourceDoc.Open(FilePaths(colIndex))
        Debug.Print "(" & colIndex & ") SOURCE DOC OPENED & PDDOC SET: " & OK

        numberOfPagesToInsert = sourceDoc.GetNumPages

        OK = primaryDoc.InsertPages(numPages, sourceDoc, 0, numberOfPagesToInsert, False)
        Debug.Print "(" & colIndex & ") PAGES INSERTED SUCCESSFULLY: " & OK

        Set sourceDoc = Nothing
    Next colIndex

    OK = primaryDoc.Save(PDSaveFull, FilePaths(1))
    Debug.Print "PRIMARYDOC SAVED PROPERLY: " & OK

    Set primaryDoc = Nothing
    app.Exit
    Set app = Nothing
    MsgBox "DONE"
End Sub

edited Aug 14, 2018 at 15:01

answered Aug 14, 2018 at 14:25

learnAsWeGo

2,2823 gold badges15 silver badges21 bronze badges

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

5 Comments

excelguy Over a year ago

thanks 1) but how do I save it now? As it doesnt open, maybe a saveas would work? 2) Can I just add paths to column G and it will pick them up? Of course I would have to manually extend G4:GX .

learnAsWeGo Over a year ago

script is still saving outside of the loop, look towards bottom of the code. you can do a lastRow search to create a dynamic range.

excelguy Over a year ago

Yes but the pdf is no longer saving, its just doing the combining but cant see the results.

learnAsWeGo Over a year ago

it works for me. make sure that your replace FilePaths.Add "PRIMARY DOC PATHWAY HERE" with your actual primary doc file pathway. or make it first in your range that adds to filePaths collection and delete that line

excelguy Over a year ago

Great it works. The nice part about it I can perhaps put a new /blank pdf in 'PRIMARY DOC PATHWAY HERE' , that way the original pdfs are not over ridden.

Collectives™ on Stack Overflow

VBA, Array range error, subscript out of range

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related