I work in Excel 2007, but a number of my coworkers have Excel 2003. I need to send out a daily Excel file to them. For about three months now I have had a file that I originally made in compatibility mode. I have no idea what happened, but the file no longer saves ANY formatting. The formatting that is lost includes the following: highlighting, bold, underlining, date format, dollar format and percentages (possibly other items that I can't pinpoint currently). My Excel file still 'works' in that the information is still there and my pivots still update, but I can't send it out to everyone. Not to mention since dates are an important part of this file, it is eating up my time each day to work in it, because to start with I have to go in and format the dates and dollars. I'm not good enough to read dates as five digit numbers yetI made a test file where all I do is random formatting and save, and it works A-OK. So I started just recreating my base file, thinking it was an issue with the file. Well I save as I go, and I can get to a certain point with all formatting in place, but I hit a point where I save and all formatting is lost. I've attempted recreating in different orders, and it doesn't lose formatting on a certain bit of information, so I'm not sure if it's a size issue with the file or what. I have pivots in my old file, but I haven't gotten to the point of creating them in the new file, so I don't think pivot tables are the issue. I don't know how to even troubleshoot at this point.
I found here http://www.pcreview.co.uk/forums/for...-t3681860.html
that it could be due to the number of styles of formatting. Now my question is how do I turn those off?
Last edited by worldbook6; 11-03-2011 at 01:41 PM. Reason: Added 'due'
I do have so many styles. I can only seem to delete one at a time. This is a work computer, so I don't have download rights for any of the add-ins that people have made to delete all of the unused styles. Any ideas on how to delete more than one style at a time? Thanks
To delete all styles in your workbook, you could use something like the code shown below. You could also limit it to specific styles by referencing the style's Name property and checking to see if it's in a list you want deleted. (Note: you may get an error when running this, but it does appear to delete the styles except the standard ones like Normal, Note, Output, Percent, Title, Total and Warning Text - at least on my PC.)
Sub delStyles() Dim sty As Style For Each sty In ThisWorkbook.Styles sty.Delete Next sty End Sub
I'm getting a debug error at sty.Delete
I found this macro http://tjshome.com/blog/238/guide/excel/accounting
but it's not doing anything for me. I don't even get an error.
Are your macros disabled? Was 'EnableEvents' turned off at any point and not turned back on? It should do something, at least error..
Try closing and re-opening the workbook then trying to run a macro. If that doesn't work, open the VB Editor and paste the following code into a new module (Insert > Module) and then run it.
Afterward, try running another macro again to see if it runs.Sub turnOn() Application.EnableEvents = True End Sub
As I mentioned in an earlier post, my macro may generate an error, however in my workbooks it did delete all styles that aren't built into Excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks