Close open workboos with script

**Richard Buttrey** · 01-20-2013, 01:30 PM

Try

 

    Dim stwb1 As String, wb2 As Workbook, stPathPlus As String
    stwb1 = ThisWorkbook.Name
    stPathPlus = Sheets("Sheet1").Range("C5") ' where "Sheet1" is the sheet TAB name and C5 contains a character string
    ActiveWorkbook.Save    
    ActiveWorkbook.SaveAs Filename:=stPathPlus
    Set wb2 = ThisWorkbook
    Workbooks.Open Filename:=stwb1
    wb2.Close

**kjsconv** · 01-20-2013, 08:27 PM

I copied & Pasted the code, it worked through saving the backup as I named it on sheet1 range c5.

However it then gives me a run-time error 1004

saying:
The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read only
Make sure the file name does not contain any of the following charcters: <>?[]:|ir*
Make sure the file/path name doesn't contain more than 218

I press the Debug key and it highlights the code line:

ActiveWorkbook.SaveAs Filename:=stPathPlus

It did save the file as I named it for a backup, the stops there.

**kjsconv** · 01-20-2013, 08:28 PM

I copied & Pasted the code, it worked through saving the backup as I named it on sheet1 range c5.

However it then gives me a run-time error 1004

saying:
The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read only
Make sure the file name does not contain any of the following charcters: <>?[]:|ir*
Make sure the file/path name doesn't contain more than 218

I press the Debug key and it highlights the code line:

[Select Code]

ActiveWorkbook.SaveAs Filename:=stPathPlus

It did save the file as I named it for a backup, the stops there.