Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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


Hi,

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!
0
Shanan212
Asked:
Shanan212
  • 3
  • 2
2 Solutions
 
Kannan KManager - EngineeringCommented:
Hi,

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

KK,
0
 
dlmilleCommented:
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
else
   'your workbook is open, nothing to do
end if
on error goto 0

Dave
0
 
dlmilleCommented:
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.

Enjoy!

Dave
createNew-r1.xls
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Shanan212Author Commented:
Thanks  

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!
0
 
Shanan212Author Commented:
Solved it by myself by assigning filepath to string
0
 
Shanan212Author Commented:
solved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now