Force save as

[JamRivera]

Hello I am trying to force users to save a file to a specific location on our network when they open a file from our intranet. I don't want the user to save the file back onto the intranet. Below is my code works great except a user can cancel the save as window and unintentionally save the altered file back onto the intranet.
BTW did i mention I'm new to this and appriacte any assistance



Sub Autpen()
Dim Workbook_Name As Variant

MsgBox "Save to desktop prior to working", vbInformation + vbOKOnly, "Hello Ops Team"

If Dir("R:\Provider Ops", vbDirectory) = "" Then
MsgBox "No folder"
Exit Sub
End If
If ThisWorkbook.name = "Triaging for Vendors.xlsm" Then Exit Sub
Workbook_Name = Application.GetSaveAsFilename("R:Provider Ops\Triaging for Vendors")
If Workbook_Name <> False Then

ActiveWorkbook.SaveAs _
FileName:=Workbook_Name & ".xlsm", _
FileFormat:=52
End If
End Sub

 

[Joe4]

If you want to prevent them from saving back to a certain place, take a look at using the "Before Save" event:
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.beforesave

You can also force a save to a specified location, by running some "On Close" event code:
https://www.automateexcel.com/vba/force-workbook-save/
Just use "SaveAs" instead of "Save" to specify the location and name that you want.

 

[JamRivera]

Hi Joe - this is great but it doesn't stop the user from closing the save as window.
I have the path in my macro it takes the user to a folder on the z drive on our network.
All the users folders are stored by their name.
They would select their folder and type in a new name then save the file.

The problem I have is the user/s are no saving the file when the save as window comes up.
ultimately what I would like is to keep the save as window open till the filename is typed in and the file is actually saved.

Or if there is a code to stop the user from saving to a specific folder that would work as well.

 

[JamRivera]

Hello I'm looking to some how add the macro listed below into my macro listed above in my original post. this is exactly what I need so when. A user opens file and the save as window comes up the user can not cancel or x out of the save as window forcing the user to pick their folder and type in a file name. Any assistance would be deeply appreciated.



Sub PreventCancel()

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)
Dim result As Variant
With fPth
.InitialFileName = CTAPath
.InitialFileName = CTAName & "_CAP DATA"
.Title = "Save with your CTA file"
.InitialView = msoFileDialogViewList
.FilterIndex = 2
Do
result = .Show
Loop While result <> True
.Execute
End With
end sub

 

[Joe4]

Or if there is a code to stop the user from saving to a specific folder that would work as well.

Note what I said here:

If you want to prevent them from saving back to a certain place, take a look at using the "Before Save" event:
https://docs.microsoft.com/en-us/off...ook.beforesave

The "Before Save" event runs whenever they try to save the file, so you can validate where they are trying to save it to, and if it is in the directory you do not want them to save to, you can cancel the save and tell them they need to save it to a different location.

You can also add code in the "Before Close" that will force a save, if they have not already done so. Here are the steps I would follow to do that (assuming that you know the directory and/or path you want them to save to):
1. Get the current path the file is in (using "ActiveWorkbook.Path")
2. If the workbook has already been saved to the correct place, you are fine and can allow the file to close
3. If the workbook is still in the path you DON'T want them to save to, have an Input Box pop-up asking them what they would like to name the file
4. Save the workbook to the path you want with the name they just provided
5. Close the workbook
. If you know the path you want them to save to, you can have an InputBox ask