Solved

Close workbook if open and create new

Posted on 2011-09-27
6
158 Views
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


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
Comment
Question by:Shanan212
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:Kannan K
ID: 36714671
Hi,

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

KK,
0
 
LVL 41

Expert Comment

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

Dave
0
 
LVL 41

Accepted Solution

by:
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.

Enjoy!

Dave
createNew-r1.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Author Comment

by:Shanan212
ID: 36716800
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
 
LVL 13

Assisted Solution

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

Author Closing Comment

by:Shanan212
ID: 36902175
solved
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

930 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now