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 anmes 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
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 anmes 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