CODEWITHSUNDEEP
vbaexcelexcel-2010
Elixir
Elixir

Reputation: 303

Excel - Close/Save using full path name

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

Upvotes: 5

Views: 32538

Answers (1)

paul bica
paul bica

Reputation: 10715

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

Upvotes: 11

Related Questions