Close workbooks saved as elements in an array

[JackDanIce]

Hi all,

I have an array of data type Variant, who's elements are workbooks opened by a user.

The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close

Code I have that doesn't work is:

Sub Close_Workbooks_In_An_Array ()

Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer

' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6

j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Workbook.(dFile(j)).Close
Next j

End Sub

Any help on what the code in red should be?

Thanks in advance,
Jack

 

[Andrew Poulsom]

Welcome to the Board.

It's Workbooks.

 

[JackDanIce]

Hi Andrew, thanks for being the first to reply!
Unfortunately, that was a typo on my part. When I run the below (with Workbooks!) I receive following error message:

Run-time error '9':
Subscript out of range

Any thoughts?
Thanks,
Jack

 

[Andrew Poulsom]

Try:

For j = LBound(dFile) To UBound(dFile)

 

[JackDanIce]

Error again:

Run-time error '13':
Type mismatch

j is an integer, dFile is the array, but I thought LBound and UBound should both be integer's themselves?

 

[Andrew Poulsom]

Let's see your code that populates the array.

 

[JackDanIce]

Morning, relevent code is:

Public dFile(1 To 6) As Variant
' Array, public so I can later call it from other macros,
' should this be Global instead?

Sub Open_Selected_Files()

Dim Path1 As String
Path1 = Range("Path1").Value
' This relates to a named range (Path1) on spreadsheet with macro
' which in turn is a cell with a directory path on it

Dim i As Integer
i = 1
Dim j As Integer
j = 1
' Variables to loop with

For i = 1 to UBound(dFile)
' Other code here that opens files and places them into dFile(i)
Next i

For j = LBound(dFile) To UBound(dFile)
MsgBox ("Closing: " & vbNewLine & vbNewLine & "(" & j & ") " & dFile(j))
Workbooks(File(j)).Close
Next j

MsgBox ("Operation complete")
End Sub


Thanks, Jack

 

[JackDanIce]

Morning, code is:

Public dFile(1 To 6) As Variant
' Array, public so I can later call it from other macros,
' should this be Global instead?

Sub Open_Selected_Files()

Dim i As Integer
i = 1
Dim j As Integer
j = 1
' Variables to loop with

For i = 1 to UBound(dFile)
' Other code here that opens files and places them into dFile(i)
Next i

For j = LBound(dFile) To UBound(dFile)
MsgBox ("Closing: " & vbNewLine & vbNewLine & "(" & j & ") " & dFile(j))
Workbooks(dFile(j)).Close
Next j

MsgBox ("Operation complete")
End Sub


Thanks, Jack

 

[Andrew Poulsom]

This simplified example worked for me:

Public dFile(1 To 6) As Variant
Sub Test()
    Dim i As Integer
    Dim j As Integer
    For i = 1 To UBound(dFile)
        dFile(i) = Chr(64 + i)
    Next i
    For j = LBound(dFile) To UBound(dFile)
        MsgBox ("Closing: " & vbNewLine & vbNewLine & "(" & j & ") " & dFile(j))
    Next j
End Sub

On which line do you get the error?

 

[JackDanIce]

In my example after the line, I have the closing line which your code doesn't appear to:

MsgBox ("Closing: " & vbNewLine & vbNewLine & "(" & j & ") " & dFile(j))
Workbooks(dFile(j)).Close

That's the line where it's failing!

Also, what does Chr(64 + i) mean? Is that the equivalent to populating the array with at element i with a file that I've defined as a Variant in the Public statement?