How to sort formulas from a sheet?

[FrenchCAD]

Greetings,

I'm currently working into a workbook which contains many sheets. I imported 2 of them from another workbook. No problem till here, but I use formulas to lin some cells into those sheets. The 2 ones I imported were so linked to their original workbook. I found many formulas I change to link them to the new workbook, but it looks like I missed some. I was wondering if it is possible to write a macro that would read the sheet to find formulas and sort them anywhere else like a new sheet, or at least scan the formulas for dependencies or scan for a certain string. I hope to be clear enough

Help would be greatly appreaciated.

Regards,

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

 

[joerd]

You can try using Replace (Ctrl-H) and then replace all occurrences of for example [C:\dir\oldsheet.xls] with nothing, so the formula will reference its own workbook.
You can also try Edit - Links... and then Change Source.
There are also some add-ins available that will replace formulas with external references with their values.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[EGT01]

As Joerd suggests and from other discussions in this forum, I found the Microsoft add-in program dellinks.exe to be very useful in finding and eliminating links.

See thread770-47158

This add-in program can be downloaded free of charge from

http://support.microsoft.com/

search the knowledgebase for dellinks.exe and follow the instructions.

When run, it will keep your original file unchanged and make changes to a copy of the file as well as create a listing of the changes it made. This includes a listing of the links.

 

[FrenchCAD]

Many thanks for the help guys

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

 

[FrenchCAD]

Well, I have a problem with Dellinks.exe It seems like the program isn't compatible with my French version of Excel97 (damn compatibility problems >:[ ). I foolowed the procedure written in the tewt file given, and when I launch Excel, it says "Routine Location Error Code 1 : Please record this Error Code". Then, I add the add-in using Tools>Add-ins and it doesn't appear in my Tools>Wizards menu as it should do. I also got "Routine Location Error Code 2 : Please record this Error Code" when closing Excel.

Dellinks.exe can be downloaded at the following address btw :

http://support.microsoft.com/?kbid=188449

Also, how can I remove add-in listed in the Tools>Add-ins box?

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

 

[EGT01]

Sorry to hear you had so much problem with dellinks, I found it to be very useful, maybe you can find something similar that would be compatible.

I'm afraid I can't offer much in the way of additional help, besides, I guess I wasn't very helpful to begin with.

I'm running Excel 2000 in the US, but look to see if you have these options.

Go to Tools/Add-ins. In the Add-ins available window, make sure the box for Delete Links Wizard is not checked. This should unload dellinks from memory and prevent it from being loaded when you start Excel again. This, however, does not remove it from your computer or from the Add-ins available window.

I have not removed an Add-in from my computer before but I did a search in Excel help for "remove add-in" and did find instructions on how to do it. For reference, I've pasted the info I found below, but obviously, you should look to see what your version suggests. Good luck in resetting your system.

Install or remove individual features of Microsoft Office or Excel
In most cases, when you first attempt to use a feature that is not installed, Microsoft Excel installs the feature automatically. For example, if you click Run Database Query on the Data menu, Microsoft Query is installed. Use this procedure if the feature you want is not installed automatically.

Click Print on the Help toolbar to print this topic. You might also want to click the link at the end of this procedure and print that topic as well.

Quit all programs.

On the Windows Start menu, point to Settings, and then click Control Panel.

Double-click the Add/Remove Programs icon, and then do one of the following:
If you installed Excel with Office, click Microsoft Office 2000 on the Install/Uninstall tab, and then click Add/Remove.

If you installed only Excel, click Microsoft Excel 2000 on the Install/Uninstall tab, and then click Add/Remove.

Follow the instructions on the screen.
See the location of features in the installation program.

Notes

If you installed Office or Excel from a CD-ROM and later mapped your CD-ROM drive to a new drive letter, run the installer again from the CD-ROM. If you are running any Office or Excel files from the CD-ROM, you must uninstall the program and then reinstall Office or Excel from the CD-ROM.

If you originally installed Office or Excel from a network file server or shared folder, run that copy of the installer.

 

[mkoijn]

The addinns dialogue box doesn't have a delete button, however you can delete the addin using the following procedure:

uncheck the addin, rename the XLA file and then recheck the addin. a meesage will ask if excel should remove from the list, click yes.

best regards

finnigan

 

[mashiz]

To supplement Joerd's solution, I would try this

1) Hit "Ctrl-`" key simultaneously with cursor in the worksheet. (NB: the ` key is just next to 1 in the numeral row above the alpha keys)

- this will show up all the formulas in the worksheet

2) Hit Alt, E and E to activate the Replace function, replace the old workbook link with nothing ie leave it blank.

It works for me every time.

 

[FrenchCAD]

Thanks for the answers. Alas, I have no access to remove Office components, I need to ask my computer service.

Best regards,

Cyril Guichard
Mechanical Engineer