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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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"
 

Excelnoobster

New Member
Joined
Sep 4, 2007
Messages
18
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
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
 

Excelnoobster

New Member
Joined
Sep 4, 2007
Messages
18
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!
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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
Top