View Full Version : [SOLVED:] Saving filename exists
sbbmaster09
06-08-2016, 03:36 AM
Hi,
I have a code that could save the file, how can i do to save the file, if it already exists will save the file as myfilename (2). Here is my code:
Sub savedata()
Sheet2.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx"
Columns("A:A").Select
Selection.Delete Shift:=xlLeft
Range("A1").Select
lastrow = Selection.End(xlDown).Offset(1).Row
Rows(lastrow & ":1048576").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close False
End Sub
Thanks.
Try:
Option Explicit
Sub example()
Dim n As Long
Sheet2.Copy
If Dir(ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx") = vbNullString Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx"
ElseIf Dir(ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & "(1).xlsx") = vbNullString Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & "(1).xlsx"
Else
n = 1
Do While Not Dir(ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & "(" & n & ").xlsx") = vbNullString
n = n + 1
Loop
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheet2.Name & " " & Format(Date, "yyyymmdd") & "(" & n & ").xlsx"
End If
'etc
End Sub
sbbmaster09
06-09-2016, 02:46 AM
Great! This is awesome, it works very fine with me. :)
How can I add the folder name from the path location below which is in the D9. Need to get the name 'Completed' so that would know the name of the source folder.
C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed
The file will be saved as 'Completed Sheet2.Name YYYYMMDD'
Thanks.
Hi,
I am signing out, but someone may be able to help. I will try and check back tonight. I want to ask for clarification.
Let us presume that Sheet2.Name = "Peacock"
Am I correct in understanding that ThisWorkbook.Path "C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed\" ?
If so, then would I be correct in understanding that the desired FullName would be: "C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed\Completed Peacock 20160609.xlsx" ?
(Today's date being 06 JUN 2016)
Mark
sbbmaster09
06-09-2016, 08:34 AM
Hi,
I am signing out, but someone may be able to help. I will try and check back tonight. I want to ask for clarification.
Let us presume that Sheet2.Name = "Peacock"
Am I correct in understanding that ThisWorkbook.Path "C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed\" ?
If so, then would I be correct in understanding that the desired FullName would be: "C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed\Completed Peacock 20160609.xlsx" ?
(Today's date being 06 JUN 2016)
Mark
Yes. The output file will be named as the Foldername + SheetName + Date just to identify to where folder it was.
thanks a lot for the help.
sbbmaster09
06-13-2016, 05:23 AM
How can I get the Folder Name (Completed) using VBA Code in the path name in a cell:
C:\Users\sarah.b.s.borja\Documents\Project VM\Consolidation\Completed
sbbmaster09
06-13-2016, 06:04 AM
This was already solved, sharing the answer for future reference:
strFolder = Right(Range("D9").Value, Len(Range("D9").Value) - InStrRev(Range("D9").Value, "\"))
Complete Code:
Dim n As LongDim strFile, strFolder As String
strFolder = Right(Range("D10").Value, Len(Range("D10").Value) - InStrRev(Range("D10").Value, "\")) & " - "
Sheet2.Copy
If Dir(ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx") = vbNullString Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx"
strFile = strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & ".xlsx"
ElseIf Dir(ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & " (1).xlsx") = vbNullString Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & " (1).xlsx"
strFile = strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd") & " (1).xlsx"
Else
n = 1
Do While Not Dir(ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd" & " ") & "(" & n & ").xlsx") = vbNullString
n = n + 1
Loop
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd" & " ") & "(" & n & ").xlsx"
strFile = strFolder & Sheet2.Name & " " & Format(Date, "yyyymmdd" & " ") & "(" & n & ").xlsx"
End If
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.