VBA 另存为当前文件名 +01

Question

我正在寻找编写一个宏来保存我当前版本的文件名 +1 版本的实例。对于每个新的一天，版本将重置为 v01 .前任。当前 = DailySheet_20150221v01；另存为 = DailySheet_20150221v02；第二天 = DailySheet_20150222v01

在增加版本号的同时，我希望版本不必包含 v0一次v10 + 已达到。

我能够锻炼如何使用今天的日期保存文件:

Sub CopyDailySheet()

Dim datestr As String

datestr = Format(Now, "yyyymmdd")

ActiveWorkbook.SaveAs "D:\Projects\Daily Sheet\DailySheet_" & datestr & ".xlsx"

End Sub

但在查找版本添加时需要额外的帮助。我可以设置 SaveAs到一个字符串，然后通过 For/If 运行它 - 然后设置？

Answer 1

把这个告诉我的几个 friend ，下面是他们的解决方案:

Sub Copy_DailySheet()

Dim datestr As String, f As String, CurrentFileDate As String, _
    CurrentVersion As String, SaveAsDate As String, SaveAsVersion As String


    f = ThisWorkbook.FullName
    SaveAsDate = Format(Now, "yyyymmdd")
    ary = Split(f, "_")
    bry = Split(ary(UBound(ary)), "v")
    cry = Split(bry(UBound(bry)), ".")
    CurrentFileDate = bry(0)
    CurrentVersion = cry(0)
    SaveAsDate = Format(Now, "yyyymmdd")

    If SaveAsDate = CurrentFileDate Then
        SaveAsVersion = CurrentVersion + 1
    Else
        SaveAsVersion = 1
    End If

    If SaveAsVersion < 10 Then
        ThisWorkbook.SaveAs "D:\Projects\Daily Sheet\DailySheet_" & SaveAsDate & "v0" & SaveAsVersion & ".xlsm"
    Else
        ThisWorkbook.SaveAs "D:\Projects\Daily Sheet\Daily Sheet_" & SaveAsDate & "v" & SaveAsVersion & ".xlsm"
    End If

End Sub

感谢所有做出贡献的人。