Gingertrees
Well-known Member
- Joined
- Sep 21, 2009
- Messages
- 697
Looking for a way to automatically add a check digit (or whatever) to the end of a file name, if the filename intended already exists in the target directory. Here's my code, which works brilliantly if the filename isn't taken:
Code:
Sub FinalProcessing()
'////Sub 8//////'////Sub 8//////'////Sub 8//////
Set ProjectNm = Activesheet.Range("F1")
'//this next section copies the processed orange report into a new file
'//New file can be emailed or uploaded to Sharepoint
Dim SourceWb As Workbook
Dim DestinationWb As Workbook
Dim Datestring As String
Set SourceWb = ThisWorkbook
Datestring = Format(Now, "mm-dd-yy")
SourceWb.ActiveSheet.Copy
Set DestinationWb = ActiveWorkbook
With DestinationWb
.SaveAs SourceWb.Path & "\Orange " & ProjectNm & " " & Datestring & " USER.xlsx"
'/-----------------
'/add code here to handle if filename
'/already exists, giving this a title of
'/e.g. "Orange projectname 09-08-15 USER1.xlsx"
'/-----------------
.Close
End With
'//just the orange sheet has now been saved
'//to the same directory location as this workbk.
'//It's called "Orange projectname today's date-time.xslx"
'//
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If MsgBox("And there was much rejoicing.", vbOKOnly) = vbOK Then Exit Sub
End Sub
Last edited:
