VBA trying to close a workbook if it happens to be open excel-add-in

excel 2010.

I have an excel add-in..
in a module I'm trying to close a workbook if it happens to be open.
??


vba code to close a workbook if it is open ?

Sub CloseWorkbook()
Dim excelwrk As Excel.Application
Set excelwrk = Excel.Application

excelwrk.Workbooks("NewInput.xlsx").Close SaveChanges:=True  '  SaveChanges:=True


End Sub

Open in new window



This is not working ?

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Will this work?
Sub CloseWorkbook()
Dim excelwrk As Excel.Application
Dim wb As Workbook
Set excelwrk = Excel.Application
For Each wb In excelwrk.Workbooks
    If wb.Name = "NewInput.xlsx" Then
        excelwrk.Workbooks("NewInput.xlsx").Close SaveChanges:=True  '  SaveChanges:=True
    End If
Next wb
End Sub

Open in new window

0
Patrick MatthewsCommented:
I take it that "if it happens to be open" may mean that it may not be open.  In that case I would make it very simple:

Sub CloseWorkbook()

    On Error Resume Next
    Application.Workbooks("NewInput.xlsx").Close SaveChanges:=True  '  SaveChanges:=True
    On Error GoTo 0

End Sub

Open in new window


Note that looping through the workbooks collection can be risky:
1) A statement like If wb.Name = "NewInput.xlsx" Then will be case sensitive unless you set a module declaration of Option Compare Text.  Better to use StrComp, or force the workbook name to all upper or lower case first
2) If the workbook you are trying to close is itself an add-in, it will not appear in an enumeration of the workbooks collection
1
Roy CoxGroup Finance ManagerCommented:
Here's a Function that checks if a specific workbook is open and test code to close it if the function returns True.

Place the Function in a module within the addin.  Use the code to test it in the appropriate code that needs to close the workbook.

Option Explicit
Public Function wbOpen(wbName As String) As Boolean
' returns TRUE if the workbook is open
    wbOpen = False
    On Error GoTo wbNotOpen
    If Len(Application.Workbooks(wbName).Name) > 0 Then
        wbOpen = True
        Exit Function
    End If
wbNotOpen:
End Function


Sub TestOpen()
'///will not save workbook
End Sub
If wbOpen("NewInput.xlsx") Then Workbooks("NewInput.xlsx").Close
End If

Sub TestOpen()
'///will save workbook
End Sub
If wbOpen("NewInput.xlsx") Then Workbooks("NewInput.xlsx").Close True
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
THANKS ALL !!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.