Trying to close a file but get "error 9"

[Cummins]

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

 

[kpark91]

sFile is a string....
and PLEASE declare all your variables before using them.

 

[Leith Ross]

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.

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]

sfile includes the path, you don't need that to refer to an open workbook.

 

[Cummins]

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]

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.