Assign workbook to variables – VBA

August 2, 2024

I have the below piece of code which save as a workbook to a new location and assign the new file to a new variable. However, the second part of the code, the part which assign the new workbook to the new variable gives me an error of Out of subscription.

I have use Dir function to check if the file excist and the file excist.

Any ideas?

Declare workbooks
Dim wb As Workbook
Dim newwb As Workbook

'Save as new workbook
    wb.SaveAs Filename:="C:\Users\XXXX\Desktop\Workspace\XXXX Tool\XXXX Workbook " & strYear & ".xlsm", FileFormat:=52`
       
'Set the new workbook
    Set newwb = Workbooks("C:\Users\XXXX\Desktop\Workspace\XXXX Tool\XXXX Workbook " & strYear & ".xlsm")

>Solution :

When you save or open a Workbook, you need to specify the full path. However, when accessing a Workbook from the Workbooks-Collection by name, you need to use the file name without the path.

Const Path = "C:\Users\XXXX\Desktop\Workspace\XXXX Tool\"
Dim filename As String
filename = "XXXX Workbook " & strYear & ".xlsm"

Dim newwb As Workbook
wb.SaveAs filename:=Path & filename, FileFormat:=52
Set newwb = Workbooks(filename)