Saving .XLSM but then resaving when name exists

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
So this code I put jnder is saved in as a template master. We have new work numbers every time we get a job so we copy template in ever job...

So when I run this after filling out form it automatically saves in the job file number... Once we reopen in job folder everything is the same, we make changes and press that same macro same but now that it is the active same name it does not save

So macro saves in right directory, but when I try to change something once saved it gives me an error because same name exists

——————
VBA Code:
Sub SavingXLform()



FormDir = Range("FormPath") & "\" & IncomingPath & ((Range("Order") \ 10000) * 10000) & "\" & IncomingPath1 & ((Range("Order") \ 1000) * 1000) & "-" & ((Range("Order") \ 1000) * 1000) + 999 & "\" & Range("Order") & "\" & Range("FormDir4")

FormName = Range("Filename")

Response = MsgBox("Save As " & FormDir & "\" & FormName, vbOKCancel)



If Response = vbOK Then

If Dir(FormDir, vbDirectory) = "" Then

On Error GoTo DirErr

MkDir (FormDir)

End If



ChDir (FormDir & FormSubDir)

On Error GoTo SaveErr

ActiveWorkbook.SaveAs FileName:=FormDir & "\" & FormName _

, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

Response = MsgBox("File " & FormName & " was saved successfuly.", vbInformation)

Exit Sub

Else

MsgBox ("Operation Canceled.")

Exit Sub

End If



SaveErr:

Response = MsgBox("File save ERROR !", vbCritical)

Exit Sub



DirErr:

Response = MsgBox("Directory " & FormDir & " could not be created !", vbCritical)

MsgBox ErrorCode



MsgBox ("Form was not saved.")



End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
So this code I put jnder is saved in as a template master. We have new work numbers every time we get a job so we copy template in ever job...

So when I run this after filling out form it automatically saves in the job file number... Once we reopen in job folder everything is the same, we make changes and press that same macro same but now that it is the active same name it does not save

So macro saves in right directory, but when I try to change something once saved it gives me an error because same name exists

——————
VBA Code:
Sub SavingXLform()



FormDir = Range("FormPath") & "\" & IncomingPath & ((Range("Order") \ 10000) * 10000) & "\" & IncomingPath1 & ((Range("Order") \ 1000) * 1000) & "-" & ((Range("Order") \ 1000) * 1000) + 999 & "\" & Range("Order") & "\" & Range("FormDir4")

FormName = Range("Filename")

Response = MsgBox("Save As " & FormDir & "\" & FormName, vbOKCancel)



If Response = vbOK Then

If Dir(FormDir, vbDirectory) = "" Then

On Error GoTo DirErr

MkDir (FormDir)

End If



ChDir (FormDir & FormSubDir)

On Error GoTo SaveErr

ActiveWorkbook.SaveAs FileName:=FormDir & "\" & FormName _

, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

Response = MsgBox("File " & FormName & " was saved successfuly.", vbInformation)

Exit Sub

Else

MsgBox ("Operation Canceled.")

Exit Sub

End If



SaveErr:

[U][I][B]Response = MsgBox("File save ERROR !", vbCritical)[/B][/I][/U]

Exit Sub



DirErr:

Response = MsgBox("Directory " & FormDir & " could not be created !", vbCritical)

MsgBox ErrorCode



MsgBox ("Form was not saved.")



End Sub


The error I get is File save ERROR !

the funny thing is in original I save in other folder while renaming it... if I leave it open under the new name as it saved. It copied evrything, I can make changes, press the macro button and it saves, askinf it already exist do you want to overwrite and it works fine. As soon as I close it and reopen it wont work that macro and I verified and link is still the same, macro did not change at all so no idea what could be causing this
 

Theglyde

New Member
Joined
May 29, 2020
Messages
18
Office Version
  1. 2013
Finally figured it out on my own, the issue was we has a J: Drive to our server but when someone would open it from the Y drive then pressed save, it would act like someone else was in the file so added Err1 to code so it would save on its own copy if not from J drive

thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,290
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top