BeforeSave traps in Excel

[baronvont]

This is doing my head in!! I am simply trying to redirect the place that the user is able to save the sheet ie in my documents as the file is originally opened from a read-only copy on the server .. it should be simple but...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)

Dim fileSaveName As String

fileSaveName = Application.GetSaveAsFilename("my documents\Timesheet""Excel Files (*.xls), *.xls")

Application.ActiveWorkbook.SaveAs fileSaveName

What keeps happening is that when the user presses Cancel on the save as dialog, the file still saves but as a file called "False.xls". any ideas why??

Many thanks
Georg

 

[SkipVought]

Did you read the GetSaveAsFilename Method Example in VB Help?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[baronvont]

Skip,

I have read it but it dosn't seem to work as expected. The example allows you to get a savename/location and going on, I would expect to use the "saveas" command to save the file with a "cancel = true" at the end of the routine to stop the default save routine from running. However as it hits the saveas, it re-runs the same beforesave event I am already in (popping up a 2nd saveas prompt)! sort of understandable I suppose, but no use at all. How do I tell it I am already in the event?

 

[SkipVought]

Have you tried putting a break in your code and YOU hit cancel and observe what happens?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Loomah]

Hi
Adding to what Skip has already told you, have a look at the EnableEvents property.

As for your original post, the answer is in the help file. You need to test the value of fileSaveName

ie
if fileSaveName <>false then
save the file
etc.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Ahhhhh, Loomah.

I was leading Georg via The Socratic Method.

But, OK. ;-)

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[baronvont]

Skip,

Thanks for the input - I have in fact done my homework on this (as always b4 posting!!). I've tried everything offered by the help and various posts here but still hitting the brick wall!!

Anyway back to the code. Replacing the msgbox bit with soemthing useful after setting the default location & filename only results in the same bit of code being re-run at the saveas line so you are prompted twice to save!

MyInitialPathAndFilename = "C:\test\testfile.xls"

fileSaveName = Application.GetSaveAsFilename(MyInitialPathAndFilename,"Excel (*.xls), *.xls")
If fileSaveName <> False Then
SaveAs fileSaveName
End If

 

[SkipVought]

Did you look at the EnableEvents property of the Application object?

Also check out the DisplayAlerts property of the afore mentioned object.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[baronvont]

Been there, done that - the trouble with getting in a muddle is that eventually u can't see the forest for the trees.
I suspect some of my problem is that the initial file is read-only. Mabe this interferes with the event trigger? Anyway, now even with this code (which I had tried b4), I still get asked twice to save. Even after creating a new sheet.

Application.DisplayAlerts = False

MyInitialPathAndFilename = "C:\test\testfile2.xls"
Application.EnableEvents = False
fileSaveName = Application.GetSaveAsFilename(MyInitialPathAndFilename, "Excel (*.xls), *.xls")
If fileSaveName <> False Then
SaveAs fileSaveName
End If

Application.EnableEvents = True

 

[combo]

With the logic of your code, you need to cancel BeforeSave event saving interface:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
SaveAsUI=False
Cancel=False
Application.EnableEvents=False

' proceed with your own saving rules

Application.EnableEvents=True
End Sub

combo

 

[baronvont]

Thanks Combo - but I still get the double save request!!?? It's only when I chose File > Save As. When I do a straight File > Save it works OK

SaveAsUI = False
Cancel = False
Application.EnableEvents = False
Application.DisplayAlerts = False

MyInitialPathAndFilename = "C:\test\testfile2.xls"

fileSaveName = Application.GetSaveAsFilename(MyInitialPathAndFilename, "Excel (*.xls), *.xls")
If fileSaveName <> False Then
SaveAs fileSaveName
End If

Application.EnableEvents = True

 

[rmikesmith]

Georg,

Cancel = [b]True[/b]

Regards,
Mike

 

[combo]

Thanks Mike, I was writing too fast!

combo

 

[baronvont]

Thanks everyone - I missed that as well!!