P
Philip Roe
Suppose we want an Excel workbook (call it PhilipsMacro) to create another
Excel workbook and save it in a different folder.
The macro will presumably contain a line like: myName =
ThisWorkBook.Fullname. We cannot predict what myName will be at runtime
because we cannot predict where our friend the user is going to put our
workbook on his computer, but typically myName becomes
C\Documents\Work\newProject\workbooks\PhilipsMacro.xls.
At runtime Philip's macro takes this string and produces the string
C\Documents\Work\newProject. That's easy. We just knock letters off the end,
stopping when we have just knocked off the second "\". Call that string
newPath.
We want the new workbook (which Philip's macro created by instructions Dim
newWorkbook as Workbook: Set newWorkbook = Workbooks.Add) to be saved as
newPath\filename\bookname, where filename and bookname are strings generated
at runtime.
We all know how to do this with ChDir and SaveAs instructions. However two
necessary conditions for it to work are:
(a) newPath\filename exists, i.e. our friend's computer actually has a
folder called filename at C\Documents\Work\newProject and
(b) none of the files in this folder is called bookname.
The second condition is easy. It is routine to get a string array of names
of files in the current folder and check that none of them is bookname.
Alternatively we can treat saving a file with a name that already exists as
a trappable error.
The first condition is harder. Of course I can tell my friend to make sure
he has a folder called (say) "newBooks" at newPath and then my code can
have "newBooks" instead of fileName. But I would rather give him a drop-down
list of folder names that will work. We know that there is at least one,
namely that of the folder in which Philip's macro lives (in the example
"workbooks") but how do we get at the others?
To put it in family terms: we know how to list our workbook's brothers and
sisters but how do we list its uncles and aunts?
Philip Roe
Excel workbook and save it in a different folder.
The macro will presumably contain a line like: myName =
ThisWorkBook.Fullname. We cannot predict what myName will be at runtime
because we cannot predict where our friend the user is going to put our
workbook on his computer, but typically myName becomes
C\Documents\Work\newProject\workbooks\PhilipsMacro.xls.
At runtime Philip's macro takes this string and produces the string
C\Documents\Work\newProject. That's easy. We just knock letters off the end,
stopping when we have just knocked off the second "\". Call that string
newPath.
We want the new workbook (which Philip's macro created by instructions Dim
newWorkbook as Workbook: Set newWorkbook = Workbooks.Add) to be saved as
newPath\filename\bookname, where filename and bookname are strings generated
at runtime.
We all know how to do this with ChDir and SaveAs instructions. However two
necessary conditions for it to work are:
(a) newPath\filename exists, i.e. our friend's computer actually has a
folder called filename at C\Documents\Work\newProject and
(b) none of the files in this folder is called bookname.
The second condition is easy. It is routine to get a string array of names
of files in the current folder and check that none of them is bookname.
Alternatively we can treat saving a file with a name that already exists as
a trappable error.
The first condition is harder. Of course I can tell my friend to make sure
he has a folder called (say) "newBooks" at newPath and then my code can
have "newBooks" instead of fileName. But I would rather give him a drop-down
list of folder names that will work. We know that there is at least one,
namely that of the folder in which Philip's macro lives (in the example
"workbooks") but how do we get at the others?
To put it in family terms: we know how to list our workbook's brothers and
sisters but how do we list its uncles and aunts?
Philip Roe