How to retreive path of Excel template file using VBA?? 1

[dannydanny]

Hi,

I have an Excel Template file. When I open it, Excel automatically changes the name of the file e.g. Filename.xlt to Filename1.xls. Because of this, I am having trouble using the ThisWorkBook.Path code in the Workbook_Open() event to retrieve the path that the template file resides in.

I would like to save the Filename1.xls in the same folder as the template file..... Any way of achieving this?

Any info much appreciated.
Danny

PS. Using Windows 2000.

 

[RobBroekhuis]

Interesting question... Not a ready answer. Why do you want to save in the same folder as the template? That's kind of defeating the general idea of templates residing in their own location. How are you opening the template? Through File - New... , through File - Open..., or through Win explorer or other external link?
Rob

 

[dannydanny]

Hi Rob,

I anticipate that people will use this file in different locations/folders, hence I can not hardcode the path into the macro. I also want people to save the file into the same folder as the template file to keep all associated files together.

I`m opening the file by browsing to the files through the My Computer icon.

As a background, the purpose of the file is to enter information about inventory. The user can select from a dropdown box a customer name. But sometimes they might want to add a new name, so I have a userform that users can use to enter the customer information. When they click on OK, I want to save this new customer information to the template file, as all customer information resides on sheet2 of that file. Since the file will reside on different folders for different users, I don`t know how to retrieve the path to the file....

Thanks for any info.
Danny

 

[RobBroekhuis]

I would propose you don't use a template file. Instead, make it a regular .xls file. In the workbook_open event of the file, put code to create and activate a new file (much like the template does automatically). Save the new file in the same folder as the original one. You can then close the original file.
Make sense?
Rob

 

[dannydanny]

Hi Rob,

Thanks for the suggestion. I`ll try it out and see how it goes.

How should I code the workbook_open event? I`m not sure how to open the "template" file (now a .xls file as you suggested), and give it a different name.... I would rather that the new name file not be saved initially in case the user decides to close the file without wanting to save. The ThisWorkbook object doesn`t seem to have a property in which I can change the filename...
So it seems I would have to save as a different filename. But as mentioned, if the user wanted to discard changes without saving, well..the file has already been created and saved...

Any help much appreciated,
Danny

 

[RobBroekhuis]

Good points. I would suggest maybe the following will work:

- have your main .xls file be read-only. This will protect it from being overwritten - never a bad thing.

- don't attempt to assign new name until it is time to save

- use a workbook_beforesave event handler. You can either put your own save routine in here, or merely make sure that the user selected "save as" rather than "save" before letting Excel handle the rest.

If you need to create a new workbook (without VBA code and other extraneous items that don't need to be saved with each book), then you could do that in the beforesave handler.
Rob

 

[dannydanny]

Hi Rob,

Thanks for all the suggestions. Much appreciated,

Danny