Saving filename exists

[sbbmaster09]

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.

[GTO]

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]

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.

[GTO]

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]

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]

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]

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