Proper VBA code to open Excel template
 

Does anybody have a good example of some VBA
code for loading or opening an Excel template file???

Proper VBA code to open Excel template
 

Sub Open_Template()

yourTemplate = "file.xlt" ' amend this to your template file name
yourPath = "c:\some folder\" ' amend this to the path containing
your template file

lOpen = True
On Error Goto NotOpen
Workbooks.Open Filename:= yourPath+yourTemplate, Editable:=True
On Error Goto 0
if lOpen=False then
n = MsgBox("Specified file could not be opened",vbOkOnly)
endif
exit sub

NotOpen:
lOpen = False
Resume Next

End Sub

The error trap will handle occassions where the filename or path are not
valid.

"Robert Crandal" wrote:

Does anybody have a good example of some VBA
code for loading or opening an Excel template file???
.

Proper VBA code to open Excel template
 

You code below worked great. However, I would prefer something that
does not load the template in a new workbook window. I have
a spreadsheet with a listbox that allows the user to choose between
4 different layouts (ie. template files). Once a user chooses a template
option, I would like the current workbook to transform into the chosen
template. Is this even possible??

Thank you!


"Paul" wrote in message
...
Sub Open_Template()

yourTemplate = "file.xlt" ' amend this to your template file name
yourPath = "c:\some folder\" ' amend this to the path containing
your template file

lOpen = True
On Error Goto NotOpen
Workbooks.Open Filename:= yourPath+yourTemplate, Editable:=True
On Error Goto 0
if lOpen=False then
n = MsgBox("Specified file could not be opened",vbOkOnly)
endif
exit sub

NotOpen:
lOpen = False
Resume Next

End Sub

Proper VBA code to open Excel template
 

Do you mean overwriting the cells formats (colors, fonts, etc.) ?

If so, and if the data is in the same cells in each case :

myTemplateFile = "Template.xls" ' The name of the file containing
the required formats
myNewFile = application.activeworkbook ' The name of your file to be
amended

workbooks.open filename:=myTemplateFile

cells.copy
windows(myNewFile).activate
Range("A1").select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

workbooks(myTemplateFile).close (False)


"Robert Crandal" wrote:

You code below worked great. However, I would prefer something that
does not load the template in a new workbook window. I have
a spreadsheet with a listbox that allows the user to choose between
4 different layouts (ie. template files). Once a user chooses a template
option, I would like the current workbook to transform into the chosen
template. Is this even possible??

Thank you!


"Paul" wrote in message
...
Sub Open_Template()

yourTemplate = "file.xlt" ' amend this to your template file name
yourPath = "c:\some folder\" ' amend this to the path containing
your template file

lOpen = True
On Error Goto NotOpen
Workbooks.Open Filename:= yourPath+yourTemplate, Editable:=True
On Error Goto 0
if lOpen=False then
n = MsgBox("Specified file could not be opened",vbOkOnly)
endif
exit sub

NotOpen:
lOpen = False
Resume Next

End Sub


.

Proper VBA code to open Excel template
 

Hmmm, I pasted your code into a subroutine, ran it, and then I
got the following error message:

"Run-time Error '438'
Object doesn't support this property of method"

BTW, yes, I am trying to immediately transform the entire spreadsheet's
cell formats (colors, fonts, size, text values, etc). I want my users
to be able to select 1 of 4 different layouts, so their current
spreadsheet will be customizable, kinda like changing the
"skins" of a WinAmp music player or something. So, the
design of the current spreedsheet will change, but the data in some of
the cells will not change.

I could code in ALL of the properties for ALL of my template designs,
but it seems like a lot of work. I am just wondering if there is an
easier way to do this and I thought templates would be a solution, but
I'm not sure?


"Paul" wrote in message
...
Do you mean overwriting the cells formats (colors, fonts, etc.) ?

If so, and if the data is in the same cells in each case :

myTemplateFile = "Template.xls" ' The name of the file containing
the required formats
myNewFile = application.activeworkbook ' The name of your file to be
amended

workbooks.open filename:=myTemplateFile

cells.copy
windows(myNewFile).activate
Range("A1").select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

workbooks(myTemplateFile).close (False)

Proper VBA code to open Excel template
 

My apologies

Please add .Name as follows:

myNewFile = application.activeworkbook.NAME ' The name of your file to
be
amended

The code should then do exactly what you require.
Coding formats for individual cells is a monster task, and would be
incredibly difficult to maintain.


"Robert Crandal" wrote:

Hmmm, I pasted your code into a subroutine, ran it, and then I
got the following error message:

"Run-time Error '438'
Object doesn't support this property of method"

BTW, yes, I am trying to immediately transform the entire spreadsheet's
cell formats (colors, fonts, size, text values, etc). I want my users
to be able to select 1 of 4 different layouts, so their current
spreadsheet will be customizable, kinda like changing the
"skins" of a WinAmp music player or something. So, the
design of the current spreedsheet will change, but the data in some of
the cells will not change.

I could code in ALL of the properties for ALL of my template designs,
but it seems like a lot of work. I am just wondering if there is an
easier way to do this and I thought templates would be a solution, but
I'm not sure?


"Paul" wrote in message
...
Do you mean overwriting the cells formats (colors, fonts, etc.) ?

If so, and if the data is in the same cells in each case :

myTemplateFile = "Template.xls" ' The name of the file containing
the required formats
myNewFile = application.activeworkbook ' The name of your file to be
amended

workbooks.open filename:=myTemplateFile

cells.copy
windows(myNewFile).activate
Range("A1").select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

workbooks(myTemplateFile).close (False)


.