Check if workbook is open
wbkNew.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Desktop\" & Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx", CreateBackup:=False
ActiveWorkbook.Close False
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!
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")
Ahh I think this might be what you want to plug in there:
set wkb = Workbooks("Format(Now(), "ddmmmyyyy") & " - Trip Data.xlsx")
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
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:
Then you could use this wherever required e.g. in your example you could simply set the filename e.g.
and amend to:
And that will do the job both here and gives you a piece of re-usable code.
Regards
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
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"
and amend to:
closeWbIfOpen fName
wbkNew.SaveAs Filename:=fName, CreateBackup:=False
ActiveWorkbook.Close False
And that will do the job both here and gives you a piece of re-usable code.
Regards