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

--
Trefor

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)


"Trefor" wrote in message
...
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

--
Trefor

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 ;)

--
Trefor


"Bob Phillips" wrote:

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)


"Trefor" wrote in message
...
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

--
Trefor

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

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


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

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

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

Hope this helps


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=488625