PDA

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



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

Bob Phillips
07-30-2008, 10:40 AM
ParseFile is a full path not just a workbook name.

Capture the workbook object and close that



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

Mavyak
07-30-2008, 10:55 AM
ParseFile contains the entire path of the file. You only need the workbook name. Try this:
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

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

mailman
07-30-2008, 11:37 AM
Full path..... dang..

Yeah, that's it peeps.. sweetness.

Thanks for your help.