Trying to close a file but get "error 9"

Cummins

Board Regular
Joined
Jul 26, 2011
Messages
58
sfile is defined prior to reaching the close statement. The file is open.
Assuming I figure out why, will it error out if the file is not open?


Public Sub Print_Batch_RS()

Application.ScreenUpdating = False

'****Establish the file BatchRecords to put the data into
sfile = MyPath + "\BatchRecords.xls"
If Dir(sfile) <> "" Then 'Checks to see if the file exists
Workbooks(sfile).Close SaveChanges:=True 'Closes file and saves it if it is open
Kill (sfile) 'delete BatchRecords

' ActiveWorkbook.Close True
' closes the active workbook and saves any changes

' Workbooks.Open sfile 'Opens file if it is there.
Else
Set Newbook = Workbooks.Add 'If it is not found, this creates and opens the file.
With Newbook
.Title = "Summary Data for AIM."
.Subject = "Data converted from raw AIM output files."
.Author = "Clark Cummins"
.SaveAs FileName:=sfile
' .Worksheets(1).???
End With

End If
Workbooks.Open sfile 'Opens the new file
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
sFile is a string....
and PLEASE declare all your variables before using them.
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello Cummins,

The subscript out of range error (error 9) happens after you create the new workbook and try to reopen it. Once you create a new workbook, it becomes the active workbook. Excel will allow you to reopen a workbook that is already open. Here is the amended code.
Code:
Public Sub Print_Batch_RS()

       Application.ScreenUpdating = False
    
        '****Establish the file BatchRecords to put the data into
     sfile = MyPath + "\BatchRecords.xls"

     If Dir(sfile) <> "" Then        'Checks to see if the file exists
           Workbooks(sfile).Close SaveChanges:=True       'Closes file and saves it if it is open
           Kill (sfile)                'delete BatchRecords

   '      ActiveWorkbook.Close True
   ' closes the active workbook and saves any changes
      
       ' Workbooks.Open sfile         'Opens file if it is there.
       Else

           Set Newbook = Workbooks.Add    'If it is not found, this creates and opens the file.
              With Newbook
                  .Title = "Summary Data for AIM."
                  .Subject = "Data converted from raw AIM output files."
                  .Author = "Clark Cummins"
                  .SaveAs FileName:=sfile
     '           .Worksheets(1).???
              End With
         
     End If
Sincerely,
Leith Ross
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,813
Office Version
  1. 365
Platform
  1. Windows
sfile includes the path, you don't need that to refer to an open workbook.
 

Cummins

Board Regular
Joined
Jul 26, 2011
Messages
58
Still not getting there,
clarification, when the code reaches the Close statement, I get the error. I know that the file is currently open. Watching the code run, sfile is clearly defined as the correct name and path. Ideally, I would like to create a backup file, close this file and delete it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,813
Office Version
  1. 365
Platform
  1. Windows
So you are still using the path when referring to the workbook when you try to close it?

You do not use the path when referring to an open workbook, just the filename.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,678
Messages
5,524,241
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top