Inserting custom worksheets

[Guest]

Hello,
I have an application that uses customized VBA Worksheets. I originally
stored the templates for those in the default location for such, but due to
need to make it compatible with different versions of Office will put in the
application's folder.

But does anyone have any ideas or suggestions on how to be able to insert
such custom sheets into a workbook? I would prefer to be able to right click
and use the insert function, but those templates are in the default folder I
understand. Any ideas how to access mine via the right click function?
Thanks, God bless
Van

 

[Peter T]

Hi Van

How about creating a shortcut to your template, and put the shortcut in the
default location for your templates. Worked for me.

Regards,
Peter T

 

[Guest]

Hi Peter,
Thanks for your reply.
Because I am needing to make my application compatible with multiple Office
platforms and users starting with Office 97, and since the location of the
default templates folder has changed and from what I can gather, there
doesn't seem to be an easy way to determine where the default folder is (it
would have to be determined and implemented by my installer application) (and
someone else pointed out that someone could have changed their default folder
location), I decided to try to just place my custom templates in my
application's own folder. So basically, I was trying to simplify, and avoid
having to determine where the default folder is. If there were a simple way
of determining that (from what one guy told me even the registry won't
necessarily reflect that) I would just install my templates in that folder.
That is the predicament, but any further thoughts or suggestions are also
appreciated.
Thanks again, and God bless
Van

 

[Peter T]

Hi Van,

If I understand, all would work if your installer app knows the templates
path.

Sub test()
Dim sXLTpath As String, sSep As String
sXLTpath = Application.TemplatesPath
sSep = Application.PathSeparator
If Right(sXLTpath, 1) <> Application.PathSeparator Then
sXLTpath = sXLTpath & sSep
End If

MsgBox sXLTpath
End Sub

Guess your installer would first need to set a reference to user's Excel.

Regards,
Peter T

 

[Guest]

Peter,
Application.TemplatesPath is what I used before-which worked until I tried
to make it work with Office 97 for which it gave the incorrect path-or at
least different than later Office versions: It gives as path:
C:\Program Files\Microsoft Office\Templates.
My Wise Installer puts them in:
C:\Windows\Application Data\Microsoft\Templates
That is why I was trying to simplify and put in the same folder regardless,
my application folder. But that brings up the problem how to insert a custom
worksheet if it's not in the default folder.
But your response makes me wonder if the Wise installer people have a bug
and are not installing in the right place.
Thanks again,
Van

 

[Peter T]

Afraid I don't know what else to suggest. If you know what versions your
respective users have, why not take a chance and install into the default
template paths for each version. Most people don't change these, but if
anyone has you'll get a complaint soon enough.

FWIW I got a bit side tracked and tried:

Set oXLapp = CreateObject("Excel.Application.8") ' .9, .10, .11
Debug.Print oXLapp.Parent.TemplatesPath

I have both XL97 & XL2k installed. But - even with the .8 qualifier,
CreateObject only references the latest of any multiple version of XL.
Returning of course only details of that latest version. Seems those
dot-version extensions have no significance. I'm surprised. Maybe your
installer similarly fails to find lower versions, assuming it's even trying
to check for different versions.

Regards,
Peter T

 

[Guest]

Thanks anyway Peter.
I am trying to get input from Wise support and later will check the
knowledgebase for help if there is an easy way to determine the MS Office
templates path for various versions.
God bless
Van