Closing workbook - subscript out of range

[InptTrdr]

Hello,

I cannot figure this out for the life of me....

I'm creating a macro to open a file, format it, print it, and close it. Everything goes will until the closing part. See code below. When it gets to the line to close the file it bugs out. I would appreciate any assistance.

Sub run_macro()
Dim sfile As String
Dim tfile As Variant
Dim vFile As Variant
 
sfile = Dir("C:\temp\reports\Report " & Format((Date - 1), "mm dd yyyy") & ".xls")
tfile = "Report " & Format((Date - 1), "mm dd yyyy") & ".xls"
If sfile <> "" Then
    Workbooks.Open Filename:="C:\temp\reports\Report " & Format((Date - 1), "mm dd yyyy") & ".xls"
Else:
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Where is " & tfile & "?", "Open", False)
    If vFile = False Then
        MsgBox (tfile & " not printed")
        Exit Sub
    End If
    Workbooks(vFile).Open
End If
 
[simple formating code here]
 
Workbooks(vFile).Close SaveChanges:=False

End Sub

 

[VoG]

Try

ActiveWorkbook.Close SaveChanges:=False

 

[baitmaster]

did vfile actually open?

Only part of your macro allows this, i.e. the ELSE part of your IF statement. Have you opened the file referenced in the first part?

 

[Andrew Poulsom]

The variable vFile includes the full path, while the Close method expects only the name of the workbook. When you open a workbook it becomes the ActiveWorkbook, so depending on what your formatting code does you may be able to use:

ActiveWorkbook.Close SaveChanges:=False

Otherwise you can assign the workbook to an object variable and use that:

    Dim wb As Workbook
    Set wb = Workbooks.Open(vFile)
End If
 
'[simple formating code here]
 
wb.Close SaveChanges:=False

Note that your statement:

Workbooks(vFile).Open

is syntactically incorrect. A workbook is not a member of the Workbooks collection until it is opened.

 

[InptTrdr]

I think I made this more complicated then it need to be...

VoG / Andrew:
ActiveWorkbook.Close SaveChanges:=False seems to be the best solution, because the workbook name could potentially vary.

baitmaster:
No vfile would not open, but in the process of debuging I changed the code from "Workbooks.Open vFile" which does work and forgot to change it back before I posted this.

Andrew:
Thank you for the explaination of why it wasn't working. That makes a lot more sense now why it wasn't working.