Subscript out of range when closing workbook

Question

I wrote a macro to loop through files in a folder, copy some data from 1 tab over to another workbook. I then want to close the looped workbook before opening the next, but I receive a Subscript out of range error when trying to close.

I am using a desktop version of Excel for Office 365.

In the code below, I get Run-time error 9: Subscript out of range error when trying to execute Workbooks(currentFile).Close

The currentFile variable works further up the code to open a file.

I have tried setting it up like:

Workbooks(currentFile).Activate
ActiveWorkbook.Close

But I get the same error

'setup FSO
Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

    'actions to be performed on each file

    currentFile = FSOFile

    'go to Full Raw tab
    Workbooks.Open (currentFile)
    Worksheets("Full Raw").Range("A1").Select

    'select everything & copy
    Selection.End(xlDown).Select
    ActiveCell.Resize(1, 15).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy

    'go to combining workbook
    Workbooks("" & txn_month & "_" & Year(Date) & "").Activate

    'go to bottom of data
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    'paste copied data
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    'close working file
    Workbooks(currentFile).Close

Next

I expect each FSOFile to open, do the stuff, then close before the next FSOFile is selected

Where am I going wrong?

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, Jul 29 '19 at 12:13
for closing working file, you can activate the currentFile and then use `ActiveWorkbook.Close` — Gangula, Jul 29 '19 at 12:13
@Gangula that would be bad practice. Referring to active ranges or objects is quite frankly asking for trouble — Tim Stack, Jul 29 '19 at 12:14
What if you declare `currentFile` as a Workbook and use `Workbooks.Open(FSOFile): Set currentFile = FSOFile` and `currentFile.Close` instead? — Tim Stack, Jul 29 '19 at 12:17
Be aware that passing parameters between parenthesis to functions/subs (where return value is not get) might not result in the expected behaviour. `Workbooks.Open (currentFile)` is not the same as `Workbooks.Open currentFile`. — Vincent G, Jul 29 '19 at 12:22

score 0 · Accepted Answer · answered Jul 29 '19 at 12:25

Following up with the good advices you got in the comments above, you better of using a Code that fully qualifies Workbooks, Worksheets and Ranges, and not rely on Selection.

Modified Code

Dim Wb As Workbook
Dim Sht As Worksheet
Dim currentFileName As String

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile
    ' get the file name
    currentFileName = FSOFile.Name

    ' set the workbook object
    Set Wb = Workbooks.Open(currentFileName)
    ' set the worksheet object
    Set Sht = Wb.Worksheets("Full Raw")

    ' to copy a range from a sheet (just an example)
    Sht.UsedRange.Copy  '<-- without Selecting

    ' rest of your code....

    'close working file
    Wb.Close
Next FSOFile

Subscript out of range when closing workbook

1 Answers1