Template Problem

Excelnoobster

New Member
Joined
Sep 4, 2007
Messages
18
Hi Gang,

I have a template called template.xtl. When I open this directly from say my documents it will open as template1.xtl and after i have made changes i click the save button and the save as will appear with .xls preselcted and i can go ahead and save it wherever.

My problem is that I have a marco set up which opens template.xtl and pastes some information in. However when the macro opens it it doesn't open it as as template1.xtl, only without the 1, then when i hit save it has template preselcted rather than workbook.

This is a bit of a pain as other people use it and if they're not concerntrating and forget to change to xls will overwrite the the original.

Can anyone shed any light on how to get this working as it should.

Thanks in advance!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
To open a copy of the template manually you choose File|New rather than File|Open. To do this in VBA use Workbooks.Add instead of Workbooks.Open, passing the name of the template as an argument. Example:

Code:
Workbooks.Add Template:= _
    "C:\Program Files\Microsoft Office\Templates\1033\Balance Sheet.xlt"
 
Upvote 0
Hi Andrew,

Thanks for the reply. Gave that a go but I'm getting compile error: named argumant not found and has the word editable highlighted. code below.


Workbooks.Add Template:= _
"C:\mydocuments\invoice.xlt", _
Editable:=True
 
Upvote 0
Workbooks.Add Template:= _
"C:\mydocuments\invoice.xlt", _
Editable:=True


Greetings,

Just to add a little as you mentioned opening thru My Documents. The reason its not screwing up there is you are probably double-clicking to open while in My Documents/Explorer. If you right-click on the file you will see that New is the default action.

As to opening via code, not sure where you came up w/the second arg (new for 2007?), but if you drop it, it should work. You can set a reference to the new wb as the same time if desired...
Code:
Sub ex()
Dim wbTmplt As Workbook
 
    Set wbTmplt = Workbooks.Add(Template:="C:\mydocuments\invoice.xlt")
End Sub

Hope this helps,

Mark
 
Upvote 0
Hi Andrew,

Thanks for the reply. Gave that a go but I'm getting compile error: named argumant not found and has the word editable highlighted. code below.


Workbooks.Add Template:= _
"C:\mydocuments\invoice.xlt", _
Editable:=True

Indeed Editable is not an argument and wasn't in the sample code I posted. Try:

Code:
Workbooks.Add Template:="C:\mydocuments\invoice.xlt
 
Upvote 0
thanks for you help on this guys.

Andrews code sorted it.

If anyone else has these issues thought I'd add I had some problems with the macro selecting the template window after it opened but sorted that by changing all instances of template.xls to template1, and knocking off the .xlt in the VB code.

Thanks again for your help!
 
Upvote 0
Indeed Editable is not an argument and wasn't in the sample code I posted. Try:

Code:
Workbooks.Add Template:="C:\mydocuments\invoice.xlt

@Andrew Poulsom:

Hi Andrew,

Greetings from sunny Arizona (today was actually 'postcard perfect' here), and thank-you much for the clarification as to second arg. (I figured not, but was wondering...)

Have a great day,

Mark
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top