Close workbook if open and create new

Function CreateNew()
    Dim wkb As Workbook, wbkNew As Workbook
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   ' Set wbDest = ThisWorkbook
    'If WorkbookOpen("Payroll GL Summary.xls") Then
'        Workbooks("Payroll GL Summary.xls").Close
        'Workbooks.Close Filename:="C:\GL Summary\Payroll GL Summary.xls", savechanges:=False
   'End If
    Set wbkNew = Workbooks.Add
    wbkNew.SaveAs Filename:="Payroll GL Summary"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Function

Open in new window


I have been breaking my head with the above code for a while now.

I want to close a work book if its open 'Payroll GL Summary.xls'

Then overwrite the existing file (this portion is buggy at the bottom part of above code)

I am really confused about how to check if a workbook is open and then closing it!

Any help is much appreciated!
LVL 13
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

dlmilleConnect With a Mentor Commented:
Ok - I took this a bit further.  You'd like to check if "Payroll GL Summary.xls" is an open workbook.  If it is, then close it without saving changes.  Then, you'd like to create a new, blank workbook and save it as "Payroll GL Summary.xls", thus overwriting the "old" workbook.

Note, I assume the folder where Payroll GL Summary.xls is, is the same as the active workbook.  You'd need to alter fPath accordingly, so you overwrite to the correct folder!

Here's the complete code:
Function CreateNew()
    Dim wkb As Workbook, wbkNew As Workbook
    Dim fPath As String
    Application.ScreenUpdating = True
    fPath = ActiveWorkbook.Path 'path of activeworkbook, or whatever you need it to be
    On Error Resume Next
    Set wkb = Workbooks("Payroll GL Summary.xls")
    If Err.Number = 0 Then 'workbook is open
        Application.EnableEvents = False 'just in case we trigger a beforeClose event we don't want
        wkb.Close savechanges:=False 'so close it, without saving changes
        Application.EnableEvents = True
    End If
    On Error GoTo 0
    Set wbkNew = Workbooks.Add
    Application.DisplayAlerts = False 'to avoid being prompted on file overwrite
    wbkNew.SaveAs Filename:=fPath & "\Payroll GL Summary.xls"
    Application.DisplayAlerts = True
End Function

Open in new window

See attached.  Its tested, so it works the way I described.


Kannan KManager - EngineeringCommented:

You can use FileSystem Object to identify whether the file is open or not?

Assuming you're only running one instance of Excel, you only need to point to a workbook to see if it is open, or not.

See code, below:

dim wkb as workbook

on error resume next
set wkb = workbooks("Payroll GL Summary.xls")
if err.number <> 0 then 'workbook is not open
   'open your workbook
   'your workbook is open, nothing to do
end if
on error goto 0

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Shanan212Author Commented:

this line gives me

bkNew.SaveAs Filename:=fPath & "C:\GL Summary\Payroll GL Summary.xls"

run time error - file could not be accessed!

Much appreciate your help!
Shanan212Connect With a Mentor Author Commented:
Solved it by myself by assigning filepath to string
Shanan212Author Commented:
All Courses

From novice to tech pro — start learning today.