M
Matthew Wieder
Thanks for the research!
So there may be no winning solution here to have the replacement happen
"behind the scenes"...
So there may be no winning solution here to have the replacement happen
"behind the scenes"...
M
Matthew Wieder
I guess that would do it. I wish there was some cleaner way, but there
isn't if the workbook is holding references to the fast it was a
compiled xll before and now it's a vba add-in. Thanks for your help.
-Matthew
isn't if the workbook is holding references to the fast it was a
compiled xll before and now it's a vba add-in. Thanks for your help.
-Matthew
K
keepITcool
you could still include my "monitor" solution
in the new addin.
but the scanning mechanism will be a lot harder
as you cannot simply check the "links"
on EVERY opened file:
it would look for XLA links that dont go to the currently
installed XLA (different path or something) for selfrepair
the install location. (system32 NOT recommended for xla)
it would need:
to recalc the workbook
then check for Error values
then check these error values are related to the missing XLL
and finally it would be simple: .formula=.formula
...but the bulletproof selection
of the cells to apply that line to..
THAT's gonna be a pain
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
in the new addin.
but the scanning mechanism will be a lot harder
as you cannot simply check the "links"
on EVERY opened file:
it would look for XLA links that dont go to the currently
installed XLA (different path or something) for selfrepair
the install location. (system32 NOT recommended for xla)
it would need:
to recalc the workbook
then check for Error values
then check these error values are related to the missing XLL
and finally it would be simple: .formula=.formula
...but the bulletproof selection
of the cells to apply that line to..
THAT's gonna be a pain
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
M
Myrna Larson
The following simplified code worked for me without an error:
Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula
That should run MUCH faster than a loop.
As for not running this every time a workbook opens, your addin has a
worksheet, right?
You could keep a list on that worksheet that gives the names of workbooks that
have been "fixed" and (maybe) the date that the code executed.
Then modify the routine below to first check the list for the name of the
active workbook. If it's on the list, exit the sub. If it's not, run the rest
of the code, then add the name to the list.
Or you could write a flag to the workbook itself, in some out-of-the-way
place, check it, and update it when the code has been run.
Or instead of a flag or list, you can select cells containing errors. If there
are any, make the assumption that it's due to the current problem and replace
the formulas
Set Rng = ThisWorkbook.Worksheets(1).UsedRange. _
SpecialCells(xlCellTypeFormulas)
Rng.Formula = Rng.Formula
That should run MUCH faster than a loop.
As for not running this every time a workbook opens, your addin has a
worksheet, right?
You could keep a list on that worksheet that gives the names of workbooks that
have been "fixed" and (maybe) the date that the code executed.
Then modify the routine below to first check the list for the name of the
active workbook. If it's on the list, exit the sub. If it's not, run the rest
of the code, then add the name to the list.
Or you could write a flag to the workbook itself, in some out-of-the-way
place, check it, and update it when the code has been run.
Or instead of a flag or list, you can select cells containing errors. If there
are any, make the assumption that it's due to the current problem and replace
the formulas
M
Myrna Larson
RE the code below: It ran without an error, and Rng.Formula does return a
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas were
TOTALLY screwed up! It won't work, I guess.
variant array with the formula for each cell, but after presumably just
replacing them, I had #NA! errors all over the place. i.e. the formulas were
TOTALLY screwed up! It won't work, I guess.
M
Matthew Wieder
thanks for trying. Could you give more detail about the solution you
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?
suggested of "develop an installer routine that removes the XLL, and
installs the XLA" - how would that work?
M
Myrna Larson
No, I can't, because I've never done it. I'm just a lone, retired user,
working on stuff on my own local PC, not on a network.
But I'm sure there are folks here that can help with that.
working on stuff on my own local PC, not on a network.
But I'm sure there are folks here that can help with that.
M
Matthew Wieder
I don't need help coding the install routine, I just don't understand
what your suggestion is...
what your suggestion is...
M
Myrna Larson
It would be a separate program -- the sort of thing that runs when you install
some new software. It would copy your XLA to the correct directory, make any
changes to the registry that are needed, maybe open Excel behind the scenes
and remove the old item from the add-ins list and add the new one, etc. Folks
often buy software that write the installer program for them.
some new software. It would copy your XLA to the correct directory, make any
changes to the registry that are needed, maybe open Excel behind the scenes
and remove the old item from the add-ins list and add the new one, etc. Folks
often buy software that write the installer program for them.