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]

I just populated the array with the letters A to F (Chr returns the character associated with the specified ASCII character code).

What is dFile(j) when you get the error (hover your mouse over it in the VBE)? You only need the workbook's name, not the full path.

 

[JackDanIce]

Hi Andrew,

Bingo! I'm trying to make the array return the file using the full pathname, rather than the array name. I've made some other simple code to confirm this so now need to modify my main code to take this into account.

Thank you very much for all your help and suggestions!
Jack

 

[JackDanIce]

The joys of arrays...

Carrying on from below, I've now have a macro that asks the user if the multiple files they've opened are correct and if not, cycles through each one, allowing the user to leave open or close the file that is not needed (aim is to allow them to replace this with another file).

Relevant VBA code is:

For element = LBound(array) to UBound(array)
' some code for MsgBox with vbQuestion + vbYesNo and output
If output = vbYes Then
tempfilename = array(element).name
Workbooks(tempfilename).Activate
Workbooks(tempfilename).Close
End If
Next element

Problem is if I run this macro again, it stops as soon as it encounters an element that refers to a file that has been closed, since it can't activate or close a file that is already closed!

Is there any simple code that can test if an array element is referring to a file that is closed or not?

Thanks,
Jack

 

[Andrew Poulsom]

There's a function you can use here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[JackDanIce]

Thanks Andrew