Cannot close open workbook object

Question

I have a macro that opens an Excel file with test data, performs some simple formatting to the data, and then saves the data as a new file (keeping the file that was opened originally unchanged). I got everything to work up until the very last line, where I try to close the workbook I originally opened. There is some code just prior to attempting to close the original workbook, that sets a different workbook variable as the first one, then opens and closes it? I am a bit confused as to what the code is doing, but it closes the new file that the user just saved, and leaves the original data file that was opened at the beginning open. Here is my code below; can someone explain a little better what is happening when it saves and closes the workbook?

Sub Main()
'
'

'
'Define variables
Dim wBook As Workbook
Dim sBook As String
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String

'Get workbook filepath
sBook = Application.GetOpenFilename()

If sBook = "False" Then
    End
End If

'Open Workbook
Set wBook = Workbooks.Open(sBook)

'Unrelated formatting occurs

'Save workbook as new file
Application.ScreenUpdating = False    ' Prevents screen refreshing.

CurrentFile = wBook.FullName

NewFileType = "Excel Files 2007 (*.xlsx), *.xlsx,"

NewFile = Application.GetSaveAsFilename( _
    InitialFileName:=NewFileName, _
    fileFilter:=NewFileType)

If NewFile <> "" And NewFile <> "False" Then
    wBook.SaveAs Filename:=NewFile, _
        FileFormat:=xlOpenXMLWorkbook, _
        Password:="", _
        WriteResPassword:="", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False

    Set ActBook = wBook
    Workbooks.Open CurrentFile
    ActBook.Close
End If

Application.ScreenUpdating = True

wBook.Close

End Sub

Answer 1

When you .SaveAs the wBook object becomes the "new file." You can just close that and be fine. If you wanted to create a new object leaving the original object unchanged you would use Workbook.SaveAsCopy instead.

So, what is happening in your code:

wBook.SaveAs 

causes wBook to be the "new file" and the "old file is automatically closed"

Set ActBook = wBook 

is basically creating two "new file" objects. The old file is then opened using

Workbooks.Open CurrentFile

And then,

ActBook.Close 

closes both wBook AND ActBook.

wBook.Close 

tries to close an already closed workbook and throws an error. (Because it was the same as ActBook.)

I believe your intention is the following:

Sub Main()
'
'

'
'Define variables
Dim wBook As Workbook
Dim sBook As String
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String

'Get workbook filepath
sBook = Application.GetOpenFilename()

If sBook = "False" Then
    End
End If

'Open Workbook
Set wBook = Workbooks.Open(sBook)

'Unrelated formatting occurs

'Save workbook as new file
Application.ScreenUpdating = False    ' Prevents screen refreshing.

CurrentFile = wBook.FullName

NewFileType = "Excel Files 2007 (*.xlsx), *.xlsx,"

NewFile = Application.GetSaveAsFilename( _
    InitialFileName:=NewFileName, _
    fileFilter:=NewFileType)

If NewFile <> "" And NewFile <> "False" Then
    wBook.SaveAs Filename:=NewFile, _
        FileFormat:=xlOpenXMLWorkbook, _
        Password:="", _
        WriteResPassword:="", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ' wBook is now the "new file" at this point and the "old file" has been closed. 
End If

Application.ScreenUpdating = True

wBook.Close ' Close the new file.

End Sub