Problems copying XLA file

[Guest]

In MAIN.XLS I check to see if the MACRO.XLA file on a network drive is more
up to date than the file in Application.UserLibraryPath. If it is more up to
date, I want to update / copy over the old file in user lib.

If I attempt to copy the XLA and it is currently open, I get an Error = 70
(Permission denied) on the source of the copy. So I added a macro to the XLA
(Force_Close_XLA) which simply closes the XLA. BUT when I do this the sub
routine in MAIN.XLS ends following the execution of Force_Close_XLA.

Is it possible to copy an open file or retain contol in MAIN.XLS?

MACRO.XLA:

Sub Force_Close_XLA()
ThisWorkbook.Close SaveChanges:=False
End Sub


MAIN.XLS:

Sub Update()
If (SourceDate <> TargetDate) Or (SourceSize <> TargetSize) Then
' MsgBox ">" & SourceDate & "<>" & TargetDate & "<" & Chr(10) _
' & ">" & SourceSize & "<>" & TargetSize & "<"
' On Error Resume Next
' Run "MACRO.XLA!Force_Close_XLA"
' On Error GoTo 0
FileCopy MainPath & "\MACRO.XLA", Application.UserLibraryPath &
"MACRO.XLA" ' Copy source to target.
LastError = Err
On Error GoTo 0
If LastError = 70 Then
MsgBox "Permission denied Error " & LastError & " updating file:
" & Application.UserLibraryPath & "MACRO.XLA"
ElseIf LastError <> 0 Then
MsgBox "Error " & LastError & " updating file: " &
Application.UserLibraryPath & "MACRO.XLA"
End If
End If

 

[Bob Phillips]

Trefor,

Why don't you just point at the network XLA file?

Other than that, set the installed property to false

APplication.Addins("myAddin.xla").Installed = False

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

1. I would love to just point to the network, but I am happy all sorts of
problems.

Check out my questions in Excel Programming (where I meant to put this
thread):
- Location of Add-in's
- Problems using Add-in
- Compile Error: Can't find project or library (missing Add-in)

2. Your alternative suggestion does not seem to do the trick either, please
see my troubles above.

Wow, I seem to be digging a deeper and deeper hole here

 

[tony h]

I have done this in various ways but the technique I use is to do th
checking and copying in a seperate spreadsheet. So I have Main.xls an
Main_partB.xls Main checks the xla's and does the copying then launche
Main_partB.xls and closing itself down.

Usually I set the startup routine in Main_partB.xls to indicate that i
can't be run from here.


Sometimes it is better to do call them Main.xls and Maintenance.xls. I
this case you always run main.xls as before. In this Main.xls checks t
see whether copying is required. If it is then it starts u
Maintenance.xls and closes itself down. Maintenance.xls then does th
copying and can relaunch main.xls.

I find the first to be best when the copying is required most times an
the second to be best when only occaisionally. Also try and keep the no
launchable part in a subfolder so that it is not quite so tempting for
user.

I use the same technique for keeping the spreadsheets themselves up t
date.

Hope this help