Assign Workbooks to Array when opening Multiple Workbooks by ParamArray

Question

I open multiple files using ParamArray.

How do I assign the workbooks to an array to access them individually? For example, to get the last row of each workbook.

The error is

subscript out of range

Sub main()
 Call COMBINED_FILES("wk1.csv","wk2.xlsx","wk3.xls","wk4.csv")
End Sub

Sub COMBINED_FILES(ParamArray SOURCE_FILE_NAME_WITH_EXTENTION() As Variant) 

 Dim MASTER_WORK_BOOK_PATH As String
 MASTER_WORK_BOOK_PATH = ActiveWorkbook.Path & "\"

 Dim MASTER_WORK_BOOK As Workbook
 Set MASTER_WORK_BOOK = ActiveWorkbook

 Dim I As Integer

 For I = 1 To UBound(SOURCE_FILE_NAME_WITH_EXTENTION)
     Dim Resultworkbook() As Workbook
     Set Resultworkbook(I) = Workbooks.Open(MASTER_WORK_BOOK_PATH & SOURCE_FILE_NAME_WITH_EXTENTION(I))
     Debug.Print SOURCE_FILE_NAME_WITH_EXTENTION(I)
 Next I
End sub

Resultworkbook is not dimentionned. Use Redim to set it size. — Vincent G, Dec 16 '19 at 16:10
And put the declaration and resizing out of the For loop, since the final size is known before. — Vincent G, Dec 16 '19 at 16:12
As Vincent says, and you can set it to be the same size as the array you're passing. — SJR, Dec 16 '19 at 16:12
paramarray is zero-based, so you're missing the first element. — Tim Williams, Dec 16 '19 at 16:13
@VincentG I got it to work! and since I don't know how many files would be put into so I put it within the loop. Thank you. — Oliver Bird, Dec 16 '19 at 16:25

score 3 · Accepted Answer · answered Dec 16 '19 at 16:19

The parenthesis of the arguments in COMBINED_FILES are not needed
Dim MASTER_WORK_BOOK As Workbook is not used
Avoid ActiveWorkbook - How to avoid using Select in Excel VBA
Avoid Integer - Why Use Integer Instead of Long?
There is a convention in VBA, to use Caps lock only for constants
If you loop on an array, try For i = LBound(array) to Ubound(array)
ReDim ResultWorkbook(UBound(sourceFileNameWithExtension)) As Workbook is used to both initialize and dimensionize the ResultWorkbook(). If it is not dimensionzed, it cannot be accessed.

This code works:

Sub Main()
    CombinedFiles "wk1.xlsx", "wk2.xlsx", "wk3.xlsx", "wk4.xlsx"
End Sub

Sub CombinedFiles(ParamArray sourceFileNameWithExtension() As Variant)

    Dim masterWorkbookPath As String
    masterWorkbookPath = ThisWorkbook.Path & "\"

    Dim i As Long
    ReDim ResultWorkbook(UBound(sourceFileNameWithExtension)) As Workbook
    For i = LBound(sourceFileNameWithExtension) To UBound(sourceFileNameWithExtension)
        Set ResultWorkbook(i) = Workbooks.Open(masterWorkbookPath & sourceFileNameWithExtension(i))
        Debug.Print sourceFileNameWithExtension(i)
    Next i
End Sub

I got it to work and being able to grab the last row numbers for each workbook. Thanks for all the suggestions. I'll look through these. — Oliver Bird, Dec 16 '19 at 16:33
"use Caps lock only for constants" - meh. Keep `SCREAMCASE` for SQL, constants are fine in `PascalCase` like everything else =) — Mathieu Guindon, Dec 16 '19 at 16:35
@MathieuGuindon - idk, when I see caps lock I feel being screamed upon. But for SQL it is one of the two standards, indeed. — Vityata, Dec 16 '19 at 16:35

Assign Workbooks to Array when opening Multiple Workbooks by ParamArray

1 Answers1