Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.