Copy a file and rename it with the date

[jevi]

Hi all,

I tried to combine a macro from the different help that I got on this website but I am not doing well.

I have this file Dati.csv and I need to do a copy of it (as it is a daily file) and save it as the condition below.

Don't know what I am doing wrong.

Thank you,

Sub Workbook()

    Dim WorkbookName
    Windows("Dati.csv").Activate
    ActiveWorkbook.SaveAs
    If Weekday(Date) = vbMonday Then
    ActiveWorkbook("Dati.csv (2)").Name = Format(Date - 3, "dd.mm.yyyy")
    Else
    ActiveWorkbook("Dati.csv (2)").Name = Format(Date - 1, "dd.mm.yyyy")
    End If
    Windows("Dati.csv ").Activate
    ActiveWindow.Close

End Sub

 

[Joe4]

First off, NEVER used reserved words (names of existing functions, objects, methods, etc) like "Workbook" as the name of your procedures, functions, or variables.
Doing so is only inviting trouble, and you could get errors or unexpected results.

Please tell us more about what you want to do.
Is the CSV already open?
Where is the VBA code to reside (you cannot have VBA code in a CSV file)?
After you rename, should either the original or copy of the CSV file be left one, or should any of them be closed?

 

[jevi]

Hi Joe4,

Sorry the late reply but busy period. Thank you for your suggestion, I will keep it in mind. The file is closed and it is CSV and the macro is in another file.xlsm ( I know that )), always in the same folder. The original should stay as it is with the name: file.csv and the copy should be renamed file 28.06.2022.csv

If Weekday(Date) = vbMonday Then
ActiveWorkbook("Dati.csv (2)").Name = Format(Date - 3, "dd.mm.yyyy")
Else
ActiveWorkbook("Dati.csv (2)").Name = Format(Date - 1, "dd.mm.yyyy")
End If

Dim oFSO As Object
 
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Call oFSO.CopyFile("O:Dati.csv", "O:Dati xx.06.2022.csv")

Thank you

 

[Joe4]

I cannot tell from your last post, are you OK now, or is there still an issue?

 

[jevi]

There is still the issue as I try to rename the file with the VBA but I can't achieve the rule that if it is monday the date of the file should be today -3 and if other day today -1.

thnx

 

[Joe4]

The WEEKDAY function returns a numeric value (see: Weekday function (Visual Basic for Applications))

So, you could do something like this:

Dim fname as string

If Weekday(Date) = 2 Then
    fname = Format(Date - 3, "dd.mm.yyyy") & ".csv"
Else
    fname = Format(Date - 1, "dd.mm.yyyy") & ".csv"
End If

to build the name of the file you want to save.

Note, however, I don't think you can rename a workbook using "Activeworkbook.Name"
I think you will need to use "SaveAs" or "FileCopy" (or "CopyFile", using your "FSO" objects).
And you might need to also capture/include the file path (which you can do with "ActiveWorkbook.Path")

 

[jevi]

Hi Joe4,
Sorry for the late reply but moving to a new home. I am not sure how to do..I did try something but it didn't work out.

I don't have to use FSO object if there is another way. Thank you

Dim oFSO As Object
    Call oFSO.Copyfile("path.csv", "path/file name10.08.2022")
    
    Dim fname As String
    If Weekday(Date) = 2 Then
    fname = Format(Date - 3, "dd.mm.yyyy") & ".csv"
    Else
    fname = Format(Date - 1, "dd.mm.yyyy") & ".csv"
    End If