We help IT Professionals succeed at work.

Check if workbook is open

Shanan212
Shanan212 asked
on
Medium Priority
338 Views
Last Modified: 2012-05-12
    wbkNew.SaveAs Filename:= _
    "C:\Documents and Settings\All Users\Desktop\" & Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx", CreateBackup:=False
    ActiveWorkbook.Close False

Open in new window


Hi, I have the above function saving a workbook with today's name, etc.

But I also have the coding below which checks if the above workbook exists and closes (if opened)

However, I could not define the name as it is to 'set the workbook' in different function. How would I assign the workbook name to the variable 'wkb'?

 
 On Error Resume Next
    Set wkb = Workbooks("what do I put here")

    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

Thanks for the help!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You put there the exact file name that would appear in the windows explorer if you weren't hiding extentions ie:

set wkb = Workbooks("example.xlsm")

Open in new window

CERTIFIED EXPERT

Commented:
Ahh I think this might be what you want to plug in there:

set wkb = Workbooks("Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx")

Open in new window

CERTIFIED EXPERT
Commented:
Whupps u want it like this.
set wkb = Workbooks(Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx")

Open in new window

Author

Commented:
Set wkb = Workbooks("Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx")

Tried that. It gives Expected: list sperator or )

when I put double quotations around date, it gives the same error on the italisized section
As Script Addict says, so if you have one routine to check if the file is open and close if so:
 
Sub closeWbIfOpen(ByVal strWb As String)
    On Error Resume Next
    Set wkb = Workbooks(strWb)

    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
End Sub

Open in new window


Then you could use this wherever required e.g. in your example you could simply set the filename e.g.
 
Dim fName As String

fName = Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx"

Open in new window


and amend to:
 
closeWbIfOpen fName

wbkNew.SaveAs Filename:=fName, CreateBackup:=False
ActiveWorkbook.Close False

Open in new window


And that will do the job both here and gives you a piece of re-usable code.
Regards

Author

Commented:
Thanks that worked!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.