CODEWITHSUNDEEP
excelvba
CptGoodar
CptGoodar

Reputation: 303

Set Multiple Workbook, when opening more than one

I have a samll issue, I would like to be able to set workbooks as variables like so Set MainWorkBook = ActiveWorkBook, which is easy and great, however I now have to be able to open multiple unique workbook ranging from 2 to 50 or so depending on what I'm using the code for.

Ideally I would like to havethe first file selected as 1Book then the second file selected as 2Book etc. So they can be easily reference later on in the code

Using this idea I would need to use a for loop, something like 

For i = 1 To NUMBEROFOPENEDWORKBOOKS

    WorkBookName = i & "Book"


    Set WorkBookName = ActiveWorkbook

Next i

Where, NUMBERIFOPENEDWORKBOOKS is sudo code for a variable that defines how many workbooks i've selected, and then placed witin the For Each section of the code below.

I open the workbooks using the code below. 

Set FileChoice = Application.FileDialog(msoFileDialogFilePicker) ' Opens a File Exploer dialog box '

With FileChoice ' This is used to set what is being displayed '

    .ButtonName = "Select"
    .AllowMultiSelect = True  ' Enables the abilty to select more than 1 file
    .InitialView = msoFileDialogViewDetails ' Sets the start location '
    .Show ' Allows it to be seen '

    For Each oFD In .SelectedItems ' This is used to create a file path used to open the file

        FilePath = oFD
        Workbooks.Open (FilePath)

    Next oFD

End With

I tried to add a for loop within the code using .selectedItems but that only confirmed that it would fail

I hope all of this made sense. Thank you for any help you can give.

EDIT 1
I have taken the advide form the comment and trie to use an Arraty function to solve this, however when I run the code below, I get a Object Required error.

Have I made a totaly clearing error that I can't see? 

Public WorkBookArray() As Excel.Workbook
Sub Channel_1()

Set FileChoice = Application.FileDialog(msoFileDialogFilePicker) ' Opens a File Exploer dialog box '

    With FileChoice ' This is used to set what is being displayed '

        .ButtonName = "Select"
        .AllowMultiSelect = True  ' Disables the abilty to select more than 1 file
        .InitialView = msoFileDialogViewDetails ' Sets the start location '
        .Show ' Allows it to be seen '

        ReDim WorkBookArray(.SelectedItems.Count, 1)

        For Each oFD In .SelectedItems ' This is used to create a file path used to open the file

            FilePath = oFD
            counter = counter + 1
            Set WorkBookArray(counter,1) = Workbook.Open(FilePath)

        Next oFD

End With

End Sub

Upvotes: 0

Views: 157

Answers (2)

DisplayName
DisplayName

Reputation: 13386

There’s a 

Workbook

that must be:

Workbooks

And you don’t need a 2D array

So your code can be as follows:

Public WorkBookArray() As Excel.Workbook
Sub Channel_1()
     With Application.FileDialog(msoFileDialogFilePicker) ' Opens a File Exploer dialog box '
        .ButtonName = "Select"
        .AllowMultiSelect = True  ' Disables the abilty to select more than 1 file
        .InitialView = msoFileDialogViewDetails ' Sets the start location '
        .Show ' Allows it to be seen '

        ReDim WorkBookArray(1 To .SelectedItems.Count)

        For Each oFD In .SelectedItems ' This is used to create a file path used to open the file
            counter = counter + 1
            Set WorkBookArray(counter,1) = Workbooks.Open(oFD)
        Next 
     End With
End Sub

Of course, as soon as any of those workbook gets clised, the corresponding item in WorkBookArray() becomes a null reference

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

Not using the filedialog, but a similar approach, this is how I would tackle your issue

Public arrWorkbooks() As Excel.Workbook

Sub open_workbooks()

Dim strWorkbookPaths(2) As String
Dim intCounter As Integer

strWorkbookPaths(0) = "C:\Workspace\Dummy Data\test1.xlsx"
strWorkbookPaths(1) = "C:\Workspace\Dummy Data\test2.xlsx"
strWorkbookPaths(2) = "C:\Workspace\Dummy Data\test3.xlsx"

ReDim arrWorkbooks(UBound(strWorkbookPaths))

For intCounter = 0 To UBound(strWorkbookPaths)

    Set arrWorkbooks(intCounter) = Workbooks.Open(strWorkbookPaths(intCounter))

Next intCounter

End Sub

Upvotes: 1

Related Questions