VBA Unable to open backup file containing dynamic name

Question

New to VBA, no previous coding experience.

I am creating a backup copy using SaveCopyAs to change the extension to a .xlsx due to issues with the macro running in the new file. This code can create the backup file correctly, but when I try to open this new file I receive a Run-time error '1004' Method 'Open' of object 'Workbooks' failed.

I have tried writing this several different ways with no luck. I assume the dynamic component of this is causing the issue. Please advise what needs to be done to open the new file.

Sub Refresh()

Dim currwbk As Workbook
Dim FilePath As String
Dim newFileName As String

FilePath = ThisWorkbook.Path
T = Format(Now, "mmm dd yyyy hh mm ss")

Set currwbk = Workbooks("467_Report_Active.xlsm")

Application.ScreenUpdating = False

newFileName = FilePath & " " & T & ".xlsx"

Application.DisplayAlerts = False
    currwbk.SaveCopyAs newFileName
Application.DisplayAlerts = True

Workbooks.Open (newFileName)
End Sub

Ah okay, it works now but is there a way to save the workbook so that the macro doesn't continue in the backup copy? — The1stRazgriz, Jun 08 '20 at 19:24
It appears the code can't locate the original workbook. I've rewritten this several different ways with different errors popping up. — The1stRazgriz, Jun 09 '20 at 11:53

Kalicharan.N · Accepted Answer · 2020-06-09T12:54:15.387

Instead of SaveCopyAs use SaveAs along with extension code. I tried this and it worked for me.

File format code:

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)

52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)

50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)

56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

Reference link:

CODE:

Sub Refresh()

Dim currwbk As Workbook
Dim FilePath As String
Dim newFileName As String

FilePath = ThisWorkbook.Path
T = Format(Now, "mmm dd yyyy hh mm ss")

Set currwbk = Workbooks("467_Report_Active.xlsm")

Application.ScreenUpdating = False

newFileName = FilePath & " " & T & ".xlsx"

Application.DisplayAlerts = False
    currwbk.SaveAs Filename:=newFileName, FileFormat:=51
Application.DisplayAlerts = True

Workbooks.Open (newFileName)
End Sub

Also, instead of using the actual filename in Set currwbk = Workbooks("467_Report_Active.xlsm"), you can simply assign thisworkbook object Set currwbk = Thisworkbook so that you don't need to update the filename, if there is a change in future.

EDIT: If you want your source workbook to be in open state even after saving and opening it with a new file name- check this stackoverflow discussion for the code.

Realizing my mistake here, the last line should be Workbooks.Open (currwbk). When I run this, I receive a Run-time error '438'. Object doesn't support this property or method. Something about using the currwbk causes this. The files are all saved to a local OneDrive folder. Could that be the issue? — The1stRazgriz, Jun 09 '20 at 14:11
when you use the above method, after the .saveAs, your current workbook will be saved as with a new name. Means, you cannot run/continue the macros in the original workbook, from where you triggered. If you still want your macro workbook to be opened, even after copying it into .xlsx file, please check the link that I posted under EDIT section. — Kalicharan.N, Jun 09 '20 at 16:28

VBA Unable to open backup file containing dynamic name

1 Answers1