Automatic installer or what for distributing a template?

D

dk_

Is there an installer program available, or recommendations on how to
get people be able to put a template file in the appropriate Office
Template folder, (for a small distribution of people)?

....For many people this seems to an impossible or a very intimidating
task, simple as it should be.

Thanks.

-Dennis
 
D

dk_

Ron,

Your installer code is a thing of beauty!

But there is good news, and there is bad news, (for me)....

Is there away that I can delete the install code after my template is
installed. Or, did I do the wrong thing? ...see just below.


The good news...

I double clicked your templates with Excel97--(Windows 98se),
Excel97--(Windows XP), and Excel 98--(Macintosh, OS 9.1).

Your very slick file was installed in the proper Templates folder on all
three machines. Very, very nice!!!

I then copied and pasted your code into one of my templates, in the
'ThisWorkbook' object window.

I changed your filename, referenced in the procedure, to my current
filename and saved my file.

When I double clicked my template file with your code installed into it,
the file was indeed installed in the Template folder. ...Perfect so
far!!!

When I start a new workbook using the File-->New menu in Excel, click on
my template, a new workbook is created. I save the new workbook and
close that new workbook. ...Perfect again, so far.


Now the bad news, (for me, anyway)...

When I go to open the newly saved .xls workbook, that workbook runs the
installer procedure again, which is stored in it's 'ThisWorkbook' object
window.

....My new .xls file is not usable, (it closes), and a template is once
again stored in the templates folder. So it goes in a circle.


Would you coach me what to do from here?

I see according to the way that I read your code, that the original file
that I installed from, is supposed to be deleted, (I think), but it's
still there also.


Thank you for showing me, that it can be done! I like the way that you
did your user messages also.

-Dennis

-------------------------------
 
R

Ron de Bruin

Hi dk

I install a sheet template

The template sheet is in workbook(xls) that you download
The code in the workbook open event copy the sheet to a new workbook and save that as template in the template folder.
Then it delete the install workbook

Do you want to install a sheet template ????
 
D

dk_

Ron de Bruin said:
Hi dk

I install a sheet template

The template sheet is in workbook(xls) that you download
The code in the workbook open event copy the sheet to a new workbook and save
that as template in the template folder.
Then it delete the install workbook
Click to expand...

Ron,

Yes, I see how your template does what you wrote above. Nicely done.

Do you want to install a sheet template ????
Click to expand...
*****

No, I wish to be able to install a complete .xlt workbook,
(not a sheet only). ********

I could not figure out how to change your sheet-template-installer
routine, to make it a workbook-installer-routine.

Would you coach me how to change your code for this?
I have spent time trying to alter the code.

I can imagine that using some bits of your code, that I could copy parts
of the code into a seperate workbook that would install my main template
and in that way I could get around the newly created file self-deleting
itself.


Thanks for your attention.

-Dennis
 
R

Ron de Bruin

Hi DK

Not tested but try this

Create a new workbook (named install.xls) and copy this code in the thisworkbook module

Private Sub Workbook_Open()
If Dir(ThisWorkbook.Path & "\your.xlt") <> "" Then

Name ThisWorkbook.Path & "\your.xlt" As Application.TemplatesPath & "\your.xlt"

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

End If
End Sub

Now give the user this workbook and the template your.xlt and say to run the workbook to install the template
Both files must be in the same folder.

It move the xlt to the template folder and kill the install xls
 
D

dk_

Ron,

Looking extremely good!

Your code works as is.

I then modified your code, adding "& Application.PathSeparator" in two
places, (see just below), so that it would also work on a Macintosh, and
it does, with one caveat, ... the installer workbook must be on the same
hard drive as the Excel program.

Now, would you add ONE MORE feature to this code??? :)

I'd like the installer workbook to be able to replace and 'existing'
template with the same name, (as does your original email-sheet
installer).

This way the code can be used for updating to a 'latest version' of the
template.

Currently this installer workbook, (with the code below), will halt when
opened if there is already a file in the Templates folder with the same
name, and will display the following message:

"Run-time error '58': File already exists.


' This code below was tested and works on:
' Excel97 Windows 98se, and XP, and on
' Excel98 Macintosh OS 9.1

Private Sub Workbook_Open()
If Dir(ThisWorkbook.Path & Application.PathSeparator &
"testerfile.xlt") <> "" Then

Name ThisWorkbook.Path & Application.PathSeparator &
"testerfile.xlt" As Application.TemplatesPath & "testerfile.xlt"

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

End If
End Sub


Thank you RON!!!

I wonder why a utility like this is not part of Miscosoft's distribution.

Cheers.

-Dennis
 
D

dk_

Ron,

Solution accomplished for installing and also for replacing existing an
excel template...

I changed your code from "Name" to "FileCopy" and then adjusted the
destination location to copy to.

Ron, Thank you very much for your help, inspiration and your time. This
has been fun.

Thank you again.

-Dennis
 
D

Dave Peterson

You may want to look at Kill in VBA's help (well, if you want to delete the
original file).
 
D

dk_

Dave,

I guess after all, it is a good idea to delete, (Kill), the installer
files. Less confusion for the user. Ron's original install codes do use
the Kill statement.

....Or, how could I modify the code below, to install from a CD, rather
than from a folder on the where Excel resides?


Sub Install_test()

If Dir(ThisWorkbook.Path & Application.PathSeparator &
"testerfile.xlt") <> "" Then

FileCopy ThisWorkbook.Path & Application.PathSeparator &
"testerfile.xlt" As Application.TemplatesPath & "testerfile.xlt"

End Sub


Thanks.

-Dennis
 
D

Dave Peterson

If ThisWorkbook is also on the same CD, then it doesn't look like anything would
change. But unless you're running some sort of DLA(?) with your CDRW, the file
will be readonly--so you won't have to delete it.
 
D

dk_

Dave,

I think I tried a similar routine from a CD and I believe that the
"Application.TemplatesPath" statement requires that TemplatesPath is on
the same drive as the file with the installer routine in it.

I understand the readonly deal.

Thanks.

-Dennis
 
D

Dave Peterson

It would be pretty unusual for the Templates path to point at a CD drive--how
would you ever ensure that the correct disk is in that CD.

I went to the immediate window of the VBE and did this:

?Application.TemplatesPath
and got this back
C:\Documents and Settings\David Peterson\Application Data\Microsoft\Templates\

I can't imagine anyone fiddling with this setting to point at a removable disk
drive.
 
D

dk_

Dave,

You are so right! Sorry! I just burned a CD, (also tried a Flash
Drive), and both worked perfectly. Thank you.

What is the Immediate Window? I did what you described below and got the
same result. Would you explain it a bit?

Thats one more time.

-Dennis
 
D

Dave Peterson

Inside the VBE, hit ctrl-g to see that immediate window.

You can put commands that you want executed directly in that window. The ? is a
shortcut for Print in this command:

?Application.TemplatesPath

While you're debugging your code, you could have a bunch of msgboxes to indicate
where you are. But then you'll have to dismiss those before continuing.

An alternative would be to put:

debug.print "successfully in function abc"

You could sprinkle these kinds of lines throughout your code and then examine
that debug window to see what when wrong, er, worked ok.
 
D

dk_

Dave,

Thanks for the lesson!

My CD installer is working just right. A totally satisfying experience
developing this project.

-Dennis
 
D

Dave Peterson

Congrats!

dk_ said:
Dave,

Thanks for the lesson!

My CD installer is working just right. A totally satisfying experience
developing this project.

-Dennis
Click to expand...
 
Top