Solved

Close workbook if open and create new

Posted on 2011-09-27
6
156 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:Kannan253984
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
AutoFilter Delete not keeping Headers? 2 12
VBA in SharePoint 3 18
Excel 2016 Hiding Toolbars 7 22
Cells protection in Excel 7 18
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

18 Experts available now in Live!

Get 1:1 Help Now