mailman
07-30-2008, 09:51 AM
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. :wot
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. :wot