2003 VBA in a 2007 environment

Hi all,

I'm getting a new error message when running some existing code on existing workbooks in Excel 2007.
Both the code and workbook were created in 2003 and run in 2003 with no problems.

The error message is:
"Runtime error -2147417848 (80010108)
Automation error
Object invoked has disconnected from it's clients"

The code being executed reads:
Selection.Replace What:="Template - Size Ratios", Replacement:="Size Ratios", LookAt:=xlPart _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

Then as soon as you stop code execution Excel crashes out

Any ideas anyone?
Cheers

 I see the word template and wonder if you are updating a range name in formulas that refer to an external workbook and Excel can't find it???

Not sure why it would suddenly break in 2007 though, except the extension of the template may fool it. (I'm making loads of guesses here as I don't know what it is actually doing!)

If you don't get any lead or ideas from this, i'll take a look at the workbook if you like (although fresh back from the US at present and jet lagged )

 Just another thought. have you tried saving as an xlsm (and xltm if there is a template involved)?

Hi Nick,

In this Range Planning workbook there are 2 main working sheets called a 'Line Detail' and a 'Size Ratio'. When the users pick up a clean template (in a truer sense) the size sheet is hidden and called 'Template - Size Ratio'.

Elements of the Line Detail refer to the 'Template - Size Ratio' and if sizing is not used it stays hidden and the references are unused.
However, if the users opt to use sizing in their range then I take a copy of 'Template - Size Ratio' sheet, call it 'Size Ratio' and redirect the links in 'Line Detail' to the new sheet.
Hence replacing the 'Template - Size Ratio' parts of any formulas with 'Size Ratio'

Looking back, there are better ways of doing this (my skills have improved loads) and our buying team has finally learnt not to delete/rename sheets,  but the connundrum is why it'll always stop at this line of code in 2007 when it runs perfectly in 2003 and has for several years.

I haven't tried changing the extension as I need to see if (when we move to 2007) we can open and run all existing workbooks without changing anything.

I'll post up anything else as and when I come across it. Will be looking very closely at this suite of workbooks!!

Gizmo

Most things work just fine in the change but I would look at setting calculation to manual or updating of links to manual while you do this operation or find a different way of handling it. I suspect it is the 'on-the fly' changing of this that is crashing the system and setting it all to manual while you make the  changes and then back to auto may get it in order.

Sometimes, unfortunately, there is no logical solution

Let us know when you get it working or need more help.

hi nick,

i got similiar problem with Gizmo. i created a simple program in excel vba 2003. my program is like i have a userform to be filled up by employees then i created a form in excel (sheet1) where after clicking the save button in the userform, all the details inputted in the userform will be transferred to the form i created in excel sheet. and this one can be printed. i hope i explained it clearly.

i used autoshape (rectangle) in the form i designed in excel sheet.

my problem is that when i upgraded it to office 2007 and tried running the program, i had this error:

"Unable to get the Characters property of the Rectangle class"

i dont know how to configure this because i had no problem running this in 2003. pls help.

appreciate your reply. thanks

Hi,

I had a similar issue with 2003 vs 2007. In our case it related to the VBA code using a capital letter at the start of the range name - when the range name was all lower case. It only showed up in Turkey, but it had us stumped for quite a while.

Try changing the name of the worksheet so that there are no spaces (use _ as a conjoin) and see if that helps - this has been useful in the past for me.