Rename/Relocate ThisWorkbook while open. Can't get to work with AddIns

I'm trying to work out how to relocate an installed Add-In.

I have worked out how to relocate ThisWorkbook on-the-fly (see code below - it's messy but it works) but only when it is not an Add-In.

When I alter this code for an Add-In, it only creates a COPY of the add-in at the new location.

It errors on the

Please Login or Register  to view this content.

(because Debug.Print ThisWorkbook.FullName shows that ThisWorkbook is still set to the ORIGINAL location not the NEW location)

Any ideas on how this can be achieved?

I'm open to alternative solutions (e.g. load & install the copied add-in and then uninstall/unload/file kill the original add-in)

Please Login or Register  to view this content.

Change ThisWorkbook to ActiveWorkbook

Originally Posted by AlphaFrog

Change ThisWorkbook to ActiveWorkbook

Use ActiveWorkbook instead? I want to alter this code to work on an Add-In.


Tested and it doesn't work.

When I run the code on a Workbook, the .SaveAs causes ThisWorkbook to MOVE to the new location (While leaving a copy at the original location - which can then be deleted with Kill)

When I alter the code so it can be run on an Add-In, the .SaveAs causes the ThisWorkbook to COPY to the new location. ThisWorkbook is still set to the original file - which causes an error message on the Kill line.


The difference is subtle but critical.

The easiest way to explain/prove this difference is to add this code just after the SaveAs line

Please Login or Register  to view this content.

The Workbook will return the new location/filepath. The Add-In will return the original location/filepath.

I misunderstood what you were doing. Sorry. I understand now I think, but I don't have a solution.

You want an add-in to move itself while loaded? I'm not going to ask why.

Have you tried setting its Isaddin property to false?

Originally Posted by romperstomper

You want an add-in to move itself while loaded?

You got it

Originally Posted by romperstomper

I'm not going to ask why.

Heh.

It's no secret so I'll tell you. I like to see add-ins loaded from the Application.UserLibraryPath. I can't prevent users from installing them from another location. So what I want to do is have the Add-In check it's TWB path on open. If the path is different, it will background move itself to the UserLibraryPath. Ideally I want to achieve this with no interruption to the end user (i.e. the add-in should continue running, the user doesn't need to close & reopen Excel etc.)

Originally Posted by romperstomper

Have you tried setting its Isaddin property to false?

No I hadn't. That sounds promising. I will try it and let you know.

@Rory: Tried IsAddin = False. I thought it would work but it didn't.

I'm puzzled as to why the XLSM version never has this problem? Unless it has something to do with the XLAM being installed?
(I don't suppose there is any way possible to continue running code in a XLAM after Install=False?)

FYI I have my AddIn version of the code below. To Anyone testing this - this will error on the Kill line so you will need to call the Disable_SpeedHack_Full after you have finished testing.

Please Login or Register  to view this content.

It looks like you reset objWorkbook.IsAddin = True before killing. Try killing while it's set to False. Then set it to True after.

I'm curious to see if you can get this to work.

Originally Posted by AlphaFrog

It looks like you reset objWorkbook.IsAddin = True before killing. Try killing while it's set to False. Then set it to True after.

Interesting. I didn't think it would matter when I wrote this. Because after the saveas either the new location or the old location is set as ThisWorkbook. If the new location then it won't make any difference. If the old location then we still have the same problem.

I tried it and it didn't work. Errored on Kill. Debug.Print ThisWorkbook.FullName shows that the open XLAM is still set in the old location.

Originally Posted by AlphaFrog

I'm curious to see if you can get this to work.

Thanks Maybe I shouldn't be so restrictive on where the add-ins are installed. But I'm curious as to why the XLSM version will work and the XLAM doesn't.

I'm now trying to install the copy and then delete the original file. But I can't get the copy to install. I run into a 1004 error on the Installed line.

Please Login or Register  to view this content.

With an add-in using SaveAs creates a copy of the workbook but leaves the original one open and running.

I didn't have a problem installing the copy and then deleting and uninstalling the current version - you have to use the current workbook's ChangeFileAccess method before you can delete it though.

Originally Posted by romperstomper

I didn't have a problem installing the copy and then deleting and uninstalling the current version

You didn't?! Which code were you using?! In my latest version (post #10) I've been getting 1004 error on the installed line.

Originally Posted by romperstomper

you have to use the current workbook's ChangeFileAccess method before you can delete it though.

Never used it before. I'll look into it and let you know.

I was using post #10. Perhaps you need to post the add-in you were testing with.

Originally Posted by romperstomper

I was using post #10. Perhaps you need to post the add-in you were testing with.

Finally got round to testing this (I'm distracted by the WMA & C++ projects that I'm slowly sinking in) and I get an error message on the Add of the copy?

I'm testing this by opening a workbook, opening add-ins manager and then using browse to install the xlam. (The AddIn install event calls the post10 code).

You're missing a path in the Add line:

Please Login or Register  to view this content.

also your ChangeFileAccess is wrong:

Please Login or Register  to view this content.

You need the open copy to be read only so you can delete the original.

Originally Posted by romperstomper

You need the open copy to be read only so you can delete the original.

But the workbook you are setting as readonly is the original file, not the copy?!

I tried the code and it does work (albeit with some unusual side effects). I couldn't have got this far without your help. Thanks Rory +1

Side effects:

1. The original XLAM still appears as an open WB in VBA. (Even though the file no longer exists in Windows!)
2. The original XLAM still appears as installed in AddIns manager. (Generally the AddIns Manager doesn't refresh until you completely close & reopen Excel and you would get an error message that the AddIn no longer exists)
3. The copied AddIn appears as installed and the WB is open (which is correct). However I haven't tested that the copied addin has triggered install/wb open events (which would be needed to load controls). Based on other addin experiments I'm doing, I doubt that it does. (see thread link further down this post)

For this experiment to have practical use in the real world, these side effects need to be solved.

I'm focused on other projects at the moment but below is my plan for solving this. I think that all of below can be done but most of the code would need to be called from the copied file:

1. to solve how to trigger an install/WB open event in the copied add-in (see link to thread at bottom of this post)
2. from the event in the copy - remove original controls (by calling removecontrols sub in the original file)
3. from the event in the copy - close original WB
4. from the event in the copy - delete original file
5. from the event in the copy - remove original from appearing in Addin Manager (I have code for this. But it requires the XLAM to be deleted first)

Wishful thinking: Ideally (but I doubt this will ever be possible)

1. the copied XLAM shouldn't need to change it's filename (I need the filename to remain the same to maintain compatibility with another addin project - version auto updater - which works based on the addins filename) and
2. the original XLAM should only be uninstalled & removed from AddIn Manager (not file deleted)

I don't think 2 will ever happen. IIRC AddIns Manager refuses to allow the install of two add-ins with the same filename. And besides my "force refresh AddIns Manager" requires the XLAM to be deleted first - unless I copy the XLAM to another location, force AIM refresh and then copy back?



Link to thread testing install of controls on copied addin: UPDATE-This is now solved. http://www.excelforum.com/excel-prog...sworkbook.html