Removing Excel styles in Excel 2003

[Ldegeus]

In our office some of us use Excel 2003 and some Excel 2007. Sometimes when we copy data form one excel sheet to another we get a warning that there are to many styles and we can't past. Now I created a simple program that removes all the styles but then all formats are removed. Now I want to modify the program so only styles which are not used will be removed, or when two styles are identical they are combined. Can somebody give me some tips how to do this.

Many thanks in advance

 

[combo]

1. Create list of used styles.
You have to go through each cell in UsedRange of each worksheet. Probably the Dictionary object (Microsoft Scripting Runtime, scrrun.dll) will be easiest to apply.
2. Delete unused styles.
If a style is not in the list of used styles (1) and is not built-in, can be deleted.
3. Unify duplicates.
Identify built-in styles (not to remove). Compare styles property by property. If duplicates found, then:
- decide which style to keep,
- find cells with style to delete,
- replace styles in cells,
- delete style.
However, this may take quite a long time.


combo

 

[jflaurin]

i tought this could be done using the MID function, can't it ?

 

[SkipVought]

Hi,

This article has info about deleting Custom Styles.

http://office.microsoft.com/en-us/excel/HA011101991033.aspx?pid=CL100570551033.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ldegeus]

@jflaurin:
As far as I know the mid function is only for selecting a part of a string. So I don't think it's applicable here

@Skipvought
I know this article. And I know how to delete styles, manualy or with VBA. But I don't know how to determine which styles are used, double or what so ever and which can be deleted so to get less then around 4000 styles which figure generates the error.

http://www.costengineering.eu

http://nl.linkedin.com/in/leendertdegeus