How to open files with templates

[bentod]

I have various templates I've made. Currently, I have to open the file then open a new file using a template. Then I copy everything from the original file to the template. Then I close the original file. Depending on what I'm doing I use different templates.

Is there a way to open a file with a template; i.e. with the macros loaded for a particular purpose?

[SharpL]

Record yourself doing this via macro and then allocate to an excel button or keyboard prompt.

This way you can have a few button options to pick from, or keyboard prompts (eg. CTRL + SHIFT + Q)

[bentod]

Where is the macro going to be saved that I will have recorded? That is, once I open the file there won't be a macro to run. It is easier to right click the tab and select "Insert" then pick the template.

[HaHoBe]

Hi, bentod,

have you saved the templates in the template path of Excel (Application.TemplatesPath will deliver the result in the immediate window) in order to have them on hand when Excel will get opened? And Iīm afraid to ask but why donīt you use the templates like Book.xlst instead of using a standrad workbook and ycopaing over?

Ciao,
Holger

[bentod]

Yes, I did save it in my templates path.
What is this Book.xlst you speak of? I am unfamiliar with it.

[HaHoBe]

Hi, bentod,

What is this Book.xlst you speak of? I am unfamiliar with it.

Depending on the version of Excel you use you should have the result of Book.xlt or Book.xlst when you open up Excel normally: a fresh workbook with a given number of empty worksheets. The path to these templates might be Application.StartupPath (at least it was supposed to be in the older versions).

What I donīt get by now is why the data is collected in a standard workbook and needs to be transferred into one or more templates thereafter - due to external programs? Will it only be just copy and paste or to different locations in each template as well?

Ciao,
Holger

[bentod]

We get various reports from our MRP system as well as from customer's systems. One report is a list of all jobs that I am narrowing down to a specific list of certain jobs. One report is a large bill of materials that I am extracting some fine detailed information. Etc.

Each report needs to have a different macro.

[HaHoBe]

Hi, bentod,

upon your last post maybe you should think about creating an addin which would launch a userform holding all the templates (maybe a ComboBox) and the possibility to open files (maybe via GetOpenFilename). All macros would reside inside the addin and would be triggered from the selection of the templates you choose. Problem for me is to design anything free hand without knowing what you need to be copied from a workbook and placed where in which template or how many options there should be or how to make sure that the opened excel file meets the template...

Ciao,
Holger

[bentod]

Thanks for the information. I use other programs that let me choose a template when opening a file. I was hoping for an option or something that would pop up the templates dialog at the opening of a file. The file would then open with the templates macros, formatting, etc. active.

[HaHoBe]

Hi, bentod,

I would go the other way around and open the template, import the data when it suits the needs.

If you want to go the way you think of you would have a file open with the data, open up the template and would need to direct the template to get the data from the file.l TZhe way I laid down would cut one elemnt down and maybe make it possible to restrict the files available for loading as well.

Ciao,
Holger

[bentod]

Yeah, but the data is automatically generated every morning. It's OK. Thanks for your effort.