Close workbook if open and create new

Posted on 2011-09-27
Last Modified: 2012-05-12
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!
Question by:Shanan212
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

by:Kannan K
ID: 36714671

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

LVL 42

Expert Comment

ID: 36714716
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

LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 36714837
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.


SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

LVL 13

Author Comment

ID: 36716800

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!
LVL 13

Assisted Solution

Shanan212 earned 0 total points
ID: 36716952
Solved it by myself by assigning filepath to string
LVL 13

Author Closing Comment

ID: 36902175

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question