Excel - Close/Save using full path name

Question

Is there a way of inserting the full pathname instead of the filename when closing and saving a workbook

E.g below

Workbooks("BOOK1.XLS").Close SaveChanges:=true
Workbooks("C:\user\docs\BOOK1.XLS").Close SaveChanges:=true

Reason why I ask is that I've already named all my full pathnames in strings

Workbooks(i).Close SaveChanges:=true

So for each i in array etc

Answer 1

The Workbook object can be used like in your first statement

Workbooks("BOOK1.XLS").Close

because it uses the .Name property (without full path)

The Workbooks.Open method on the other hand, takes as parameter the full path and file name:

Workbooks.Open "C:\user\docs\BOOK1.XLS"

It can take the file name without a path as well, but it will search for it in the default folder

.

There are 2 options I use to solve similar problems, where all strings include full path and file name:

1. set a reference to the workbook when you open it:

   Set wb = Workbooks.Open("C:\user\docs\BOOK1.XLS")

   • then you can close it like this

     wb.Close SaveChanges:=True

2. Extract the file name from the string containing both, path and file name:

   wbName = Mid(fullName, InStrRev(fullName, "\") + 1)

   • then you can close it as in your first line:

     Workbooks(wbName).Close SaveChanges:=true