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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
sFile is a string....
and PLEASE declare all your variables before using them.
 
Upvote 0
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
 
Upvote 0
sfile includes the path, you don't need that to refer to an open workbook.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top