CODEWITHSUNDEEP
excelvba
Oliver Wu
Oliver Wu

Reputation: 145

Assign Workbooks to Array when opening Multiple Workbooks by ParamArray

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

Upvotes: 1

Views: 151

Answers (1)

Vityata
Vityata

Reputation: 43585

  • 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

Upvotes: 3

Related Questions