Excel Templates 1

[EliseFreedman]

Hi There

Is it possible to write code to build a file based on a template when the user selects File and Open from the file menu. At the moment if this is done, the actual template file (xlt) file opens. Obviously I dont want users to make changes to this file. I will tell them to select File and New but I suspect that they may forget and revert to using File and Open. I had tried to add the code below in the workbook open method but it just started an endless loop

any help would be appreciated

Workbooks.Add(Template:= _
        "M:\depts\HS&ES\Safety\Physio Stats\PhysiotherapyTreatmentRecord.xlt" _
        ).RunAutoMacros Which:=xlAutoOpen

 

[PHV]

You may try this:
If ActiveWorkbook.Fullname Like "*PhysiotherapyTreatmentRecord.xlt" Then
Workbooks.Add(Template:= _
"M:\depts\HS&ES\Safety\Physio Stats\PhysiotherapyTreatmentRecord.xlt" _
).RunAutoMacros Which:=xlAutoOpen
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EliseFreedman]

I tried the code you suggested but I got the error message below

Cant open a copy whilst the template PhysiotherapyTreatmentRecord.xlt is open

 

[PHV]

And this ?
If ActiveWorkbook.Fullname Like "*PhysiotherapyTreatmentRecord.xlt" Then
ActiveWorkbook.Close
Workbooks.Add(Template:= _
"M:\depts\HS&ES\Safety\Physio Stats\PhysiotherapyTreatmentRecord.xlt" _
).RunAutoMacros Which:=xlAutoOpen
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886