Referencing workbook

[confusion123]

For some reason, the following fails with the error message: Object required

Sub Test

Dim Mywbs() As Variant 

Mywbs = Array("C:\Test\Apples.xlsm", "C:\Test\Oranges.xlsm")

Dim Counter As Integer

For Counter = 0 to 1

    Call GetWorkbook(Mywbs(Counter)) ' FAILS HERE

Next Integer

End Sub

Sub GetWorkbook(ByVal Mywbs As Workbook)

    Mywbs.Open

End Sub

Please explain what is wrong.

Thanks

 

[Norie]

Mybws is a array of strings, not workbooks.

Try this, it will open each workbook named in the array.

Sub Test()

Dim Mywbs() As Variant
Dim Counter As Integer

    Mywbs = Array("C:\Test\Apples.xlsm", "C:\Test\Oranges.xlsm")

    For Counter = 0 To 1

        Call GetWorkbook(Mywbs(Counter))    ' FAILS HERE

    Next Counter

End Sub

Sub GetWorkbook(ByVal Mywbs As String)

    Workbooks.Open Mywbs

End Sub

 

[confusion123]

Mybws is a array of strings, not workbooks.

Try this, it will open each workbook named in the array.

Sub Test()

Dim Mywbs() As Variant
Dim Counter As Integer

    Mywbs = Array("C:\Test\Apples.xlsm", "C:\Test\Oranges.xlsm")

    For Counter = 0 To 1

        Call GetWorkbook(Mywbs(Counter))    ' FAILS HERE

    Next Counter

End Sub

Sub GetWorkbook(ByVal Mywbs As String)

    Workbooks.Open Mywbs

End Sub

Thanks but I prefer Mywbs to be workbook rather than string because later on, I want to refer to the worksheets within the opened workbook.

Something like:

Dim wks As Worksheet

For Each Mywks in Mywbs.Worksheets

Next Mywks

How can that be done?

I've even tried:

Dim a As Workbook
Set a = Workbooks (Mywbs)

but it flags up subscript out of range.

 

[Norie]

You can't have Mywbs as an array of workbooks, the workbooks aren't opened yet.

I suppose you could use something like this.

Sub Test()

Dim MywbsNames() As Variant
Dim wbs() As Variant
Dim Counter As Integer

    MywbsNames = Array("C:\Test\Apples.xlsm", "C:\Test\Oranges.xlsm")

    For Counter = 0 To 1

        ReDim Preserve wbs(Counter)
        
        Set wbs(Counter) = GetWorkbook(MywbsNames(Counter))

    Next Counter

End Sub

Function GetWorkbook(ByVal Mywbs As String) As Workbook

    Set GetWorkbook = Workbooks.Open(Mywbs)

End Function

 

[confusion123]

This opens the workbook (which is what I want) but I can't do my For Each Loop later on.

Sub Test

Dim Mywbs() As Variant 

Mywbs = Array("C:\Test\Apples.xlsm", "C:\Test\Oranges.xlsm")

Dim Counter As Integer

For Counter = 0 to 1

    Call GetWorkbook(Mywbs(Counter))

Next Integer

End Sub


Sub GetWorkbook(ByVal Mywbs As String)

Workbook.Open Mywb ' NEW LINE ADDED

End Sub

ie

Dim wks As Worksheet

For Each Mywks in Mywbs.Worksheets ' FAILS

Next Mywks

 

[Norie]

That's because Mywbs is still only an array of strings.

Even if it was you an array of workbooks you couldn't use that For Each, you would need a loop for the array too.

 

[confusion123]

Sorted!

Dim wbname As String

wbname = Right(Mywb, Len(Mywb) - InStrRev(Mywb, "\", Len(Mywb)))

Workbooks.Open

Dim wb As Workbook

Set wb = Workbooks(wbname)

Dim MyWks As Worksheet

For Each MyWks In wb.Worksheets


Next MyWks