Wb backup with a variable file name...

[GS7CLW]

IT"S FRIDAY!!!!!!!!!!!!!!!!!!!
Hope everyone has a great week end.

I am trying to backup a workbook daily. The problem is this user has to change the filename from month to month, year to year (i.e.):
583 Report JUL 12.xlsx
583 Report AUG 12.xlsx
583 Report SEP 12.xlsx
NEXT YEAR:
583 Report JAN 13.xlsx
583 Report FEB 13.xlsx
and so on.

My code:

Sub ReportBackup()
UserForm2.Show
UserForm2.Label1.Caption = "Creating historical copies...."
UserForm2.Repaint
   Dim BackupFileName
   Dim BUFileNameExt
   BUFileNameExt = Format(Date, "mm-dd-yy") & ".xlsm"
   ChDir "I:\S4DATA~1\584Backup\"
'-------------- BACKUP THE 584 Input Report --------------------
   BackupFileName = "I:\S4DATA~1\584Backup\584 INPUT * -" & BUFileNameExt
   If Not WorkbookOpen("584 INPUT *.xlsm") Then
   FileCopy "I:\S4DATA~1\584Backup\584 INPUT *.xlsm", BackupFileName
   Else
   Workbooks("584 INPUT *.xlsm").Close True
   FileCopy "I:\S4DATA~1\584Backup\584 INPUT *.xlsm", BackupFileName
   End If
UserForm2.Show
UserForm2.Label1.Caption = "Historical copy complete...."
UserForm2.Repaint
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

gives me a BAD FILE NAME err msg. I KNOW it is because of the wild card * I am try to use.

ANY and ALL help greatly appreciated.

 

[Jerry Sullivan]

Does the user do the renaming of the file, or does your code need to do that?

If the user renames the file each month, then your code just needs to read the path and name of the ActiveWorkbook and use that name in the FileCopy parameters.

Something like...

Sub ReportBackup()
   Dim sBUSwap As String, sFullName As String
   sBUSwap = "-" & Format(Date, "mm-dd-yy") & ".xls"
   With ActiveWorkbook
        sFullName = .FullName
        .Close SaveChanges:=True
        FileCopy sFullName, Replace(sFullName, ".xls", sBUSwap)
   End With
End Sub

This code and your Userform would need to be in a Workbook other than the ActiveWorkbook the user is backing up.

 

[GS7CLW]

Re: Wb backup with a variable file name...SOLVED

Took your idea and added some to it.
Got it working!
Thank you for your help.
It IS greatly appreciated.
Have a great week.