Workbook_BeforeSave()
 

Hi,
How can I trap the file save as /save event and get the filename entered in
the dialogbox?
I tried to get the file name in "Workbook_BeforeSave() " event, but it
displays the dialog twice FileSave As dialog twice.
How can i avoid that dialog appearing twice

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
filesavename = filesavename
End If
Application.EnableEvents = True
Cancel = True
MsgBox filesavename
End Sub

Workbook_BeforeSave()
 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
Thisworkbook.SaveAs FileName:=filesavename
End If
Application.EnableEvents = True
Cancel = True
End Sub

--
Regards,
Tom Ogilvy


"bmm" <newsgroup.com wrote in message
...
Hi,
How can I trap the file save as /save event and get the filename entered
in
the dialogbox?
I tried to get the file name in "Workbook_BeforeSave() " event, but it
displays the dialog twice FileSave As dialog twice.
How can i avoid that dialog appearing twice

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
filesavename = filesavename
End If
Application.EnableEvents = True
Cancel = True
MsgBox filesavename
End Sub

Workbook_BeforeSave()
 

Thanks Tom,
this code works but when i try to save the file with the the existing file
name,
it asks if i want to replace the existing file, if i say No/ Cancel then it
gives me an Run time error "1004"
which says
"Method SaveAs of object '_Workbook' failed "
Can you please explain me why is it so?

Thanks once again


"Tom Ogilvy" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
Thisworkbook.SaveAs FileName:=filesavename
End If
Application.EnableEvents = True
Cancel = True
End Sub

--
Regards,
Tom Ogilvy


"bmm" <newsgroup.com wrote in message
...
Hi,
How can I trap the file save as /save event and get the filename entered
in
the dialogbox?
I tried to get the file name in "Workbook_BeforeSave() " event, but it
displays the dialog twice FileSave As dialog twice.
How can i avoid that dialog appearing twice

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
filesavename = filesavename
End If
Application.EnableEvents = True
Cancel = True
MsgBox filesavename
End Sub

Workbook_BeforeSave()
 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
On Error Resume Next
Application.DisplayAlerts = False
Thisworkbook.SaveAs FileName:=filesavename
Application.DisplayAlerts = True
On Error goto 0
End If
Application.EnableEvents = True
Cancel = True
End Sub

or

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
if lcase(thisworkbook.FullName) = lcase(filesavename) then
ThisWorkbook.Save
else
Application.DisplayAlerts = False
Thisworkbook.SaveAs FileName:=filesavename
Application.DisplayAlerts = True
End if
End If
Application.EnableEvents = True
Cancel = True
End Sub

Should avoid the error.

--
Regards,
Tom Ogilvy


"bmm" <newsgroup.com wrote in message
...
Thanks Tom,
this code works but when i try to save the file with the the existing file
name,
it asks if i want to replace the existing file, if i say No/ Cancel then
it
gives me an Run time error "1004"
which says
"Method SaveAs of object '_Workbook' failed "
Can you please explain me why is it so?

Thanks once again


"Tom Ogilvy" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, -
Cancel As Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
Thisworkbook.SaveAs FileName:=filesavename
End If
Application.EnableEvents = True
Cancel = True
End Sub

--
Regards,
Tom Ogilvy


"bmm" <newsgroup.com wrote in message
...
Hi,
How can I trap the file save as /save event and get the filename
entered
in
the dialogbox?
I tried to get the file name in "Workbook_BeforeSave() " event, but it
displays the dialog twice FileSave As dialog twice.
How can i avoid that dialog appearing twice

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If filesavename < False Then
filesavename = filesavename
End If
Application.EnableEvents = True
Cancel = True
MsgBox filesavename
End Sub