MicroDudeq
06-13-2016, 03:44 AM
Hi everyone,
[Edit: Current software: Excel 2013 (Microsoft Office 365 ProPlus)]
I'm trying to compile a VBA code that will check if the current name exists, and if it does change the name. Optionally I could also use date & time in the filename.
My code as of now:
Sub Save()
Application.ScreenUpdating = False
Worksheets("LO FORM").Copy
With ActiveWorkbook
.SaveAs Filename:=("**SERVERPATH**") & "\LOFORM.xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
Application.ScreenUpdating = True
EndWith 'Auto-email disregard this'
End Sub
If you want to test this code, create a new WorkBook with two different sheets and create a button (or just run the code) and connect the code to the button. And the selected Sheet should be saved in a new WorkBook to where you defined your path. However, if you try to save it once again, it will ask you to replace the current file or to abort the whole process (and no it does not give you an option to save as another name :crying:).
I've asked around and someone told me to use this:
Function CheckExists(fname As String) As BooleanDim TestStr As String
Dim fpath As String
fpath = Worksheets("Filepaths & Filenames").Range("D4").Value
TestStr = ""
On Error Resume Next
TestStr = Dir("**SERVERPATH**" & "\" & LOFORM & ".xlsm")
On Error GoTo 0
If TestStr = "" Then
'MsgBox "File doesn't exist"
CheckExists = False
Else
'MsgBox "File exist"
CheckExists = True
End If
End Function
But I cannot get it to work. I've tried calling the function, and in all desperation also tried GoTo.
To Further explain, you simply push a button that is connected to a macro which in turn runs a VBA code that saves the current sheet to a remote server and sends an email to the administrators that a new form is available. The reason I need it to change name is because I can expect up to 10 forms being saved daily, and as of now only one file can be saved at a time because they have the same name, and you do not get the option to save with new name.
All help is appreciated,
Thanks
[Edit: Current software: Excel 2013 (Microsoft Office 365 ProPlus)]
I'm trying to compile a VBA code that will check if the current name exists, and if it does change the name. Optionally I could also use date & time in the filename.
My code as of now:
Sub Save()
Application.ScreenUpdating = False
Worksheets("LO FORM").Copy
With ActiveWorkbook
.SaveAs Filename:=("**SERVERPATH**") & "\LOFORM.xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
Application.ScreenUpdating = True
EndWith 'Auto-email disregard this'
End Sub
If you want to test this code, create a new WorkBook with two different sheets and create a button (or just run the code) and connect the code to the button. And the selected Sheet should be saved in a new WorkBook to where you defined your path. However, if you try to save it once again, it will ask you to replace the current file or to abort the whole process (and no it does not give you an option to save as another name :crying:).
I've asked around and someone told me to use this:
Function CheckExists(fname As String) As BooleanDim TestStr As String
Dim fpath As String
fpath = Worksheets("Filepaths & Filenames").Range("D4").Value
TestStr = ""
On Error Resume Next
TestStr = Dir("**SERVERPATH**" & "\" & LOFORM & ".xlsm")
On Error GoTo 0
If TestStr = "" Then
'MsgBox "File doesn't exist"
CheckExists = False
Else
'MsgBox "File exist"
CheckExists = True
End If
End Function
But I cannot get it to work. I've tried calling the function, and in all desperation also tried GoTo.
To Further explain, you simply push a button that is connected to a macro which in turn runs a VBA code that saves the current sheet to a remote server and sends an email to the administrators that a new form is available. The reason I need it to change name is because I can expect up to 10 forms being saved daily, and as of now only one file can be saved at a time because they have the same name, and you do not get the option to save with new name.
All help is appreciated,
Thanks