Solved: Workbooks(NewFN).Close SaveChanges:=False Index Issue

[mailman]

Hey all.

I'm opening a file using a dialogue box, which works ok, but for some reason I'm getting an "Index Out of Range" error trying to close the same file.

The exact error I'm getting is:
Run-time error '9':
Subscript out of range

Here's the code....

Dim RangeObj As Range
Dim ParseFile
Dim SaveChanges
    ParseFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select the Config report you exported."
 
    If ParseFile = False Then
        ' They pressed Cancel
        MsgBox "You haven't selected a file. Please try again."
        Exit Sub
    Else
        Workbooks.Open FileName:=ParseFile
    End If
 
    ' Search for unique text to see if it's the correct file.
    Set RangeObj = Cells.Find(What:="Unique text in file", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
 
    If RangeObj Is Nothing Then
        ' Close the file we opened.
        Workbooks(ParseFile).Close SaveChanges:=False  ' <-- OFFENDING LINE HERE
 
        MsgBox "The report you selected is either incorrect or has changed. Please ensure you ran the 'Config - Agent Skillset Properties' report from Symposium and try again. If the problem persists, contact the Report Analyst (VBA Knowledge required)"
 
        Exit Sub
    End If

I'm running this out of Excel 2003.

Anyone know why I'm having issues here? Thanks.

[Bob Phillips]

ParseFile is a full path not just a workbook name.

Capture the workbook object and close that

[vba]

Dim RangeObj As Range
Dim ParseFile
Dim SaveChanges
Dim wb As Workbook

ParseFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Please select the Config report you exported.")

If ParseFile = False Then
' They pressed Cancel
MsgBox "You haven't selected a file. Please try again."
Exit Sub
Else
Set wb = Workbooks.Open(Filename:=ParseFile)
End If

' Search for unique text to see if it's the correct file.
Set RangeObj = Cells.Find(What:="Unique text in file", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If RangeObj Is Nothing Then
' Close the file we opened.
wb.Close SaveChanges:=False

MsgBox "The report you selected is either incorrect or has changed. Please ensure you ran the 'Config - Agent Skillset Properties' report from Symposium and try again. If the problem persists, contact the Report Analyst (VBA Knowledge required)"

Exit Sub
End If
[/vba]

[Mavyak]

ParseFile contains the entire path of the file. You only need the workbook name. Try this:
[VBA] Dim ParseFile As String
Dim wb As Workbook
x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select the Config report you exported.")
Set wb = Workbooks.Open(ParseFile)

wb.Close SaveChanges:=False
Set wb = Nothing[/VBA]

Also, you don't need to Dim SaveChanges. It is a named argument for the Close method of the Workbook object.

[mailman]

Full path..... dang..

Yeah, that's it peeps.. sweetness.

Thanks for your help.