4

I have a series of Microsoft Office 2010 templates (Excel, PowerPoint and Word) spread through a pre-defined folder hierarchy.

I need to make sure that when a new document is created from one of those templates, it defaults to saving it in the same folder as the template it was created from.

I was able to accomplish the task for Word using a macro such as

Sub AutoNew()
   ChangeFileOpenDirectory ActiveDocument.AttachedTemplate.Path
End Sub

but I had no success in replicating this behavior in Excel nor PowerPoint.

1 Answer 1

1

Templates are quite different in Excel and Word from what I can see, so I'm not sure it's possible the way you're asking.

A possible workaround, however, would be to create a normal Excel file (.xls/.xlsx) in the various folders that is essentially the same 'template' document. Then mark the file as Read Only in the operating system (right-click, properties, read-only).

When Excel opens a read-only file it will not let you save it over the original, so when you attempt to save it, you will be prompted for a new name and Excel will default to the folder you opened the document from.

If you want to get fancier, you could have a Workbook.Open event triggered that checks if ActiveWorkbook.ReadOnly is True, then prompt for a file name and save the file.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .