Adding more utilities

XLStylesCleaner turned out to be very popular. I really appreciate all the positive comments from the application users in the previous post. The utility has been out for about a year and seems to work without any issues. I only had to help people out with installing .Net3.5 framework when they couldn’t run the application. There were a couple asks:
1. The top ask was for a bulk file processing utility. I went ahead and wrote a console application that can process multiple files: XLBulkCleaner. Just start the utility, copy your files onto a monitored share or just open and close each file on the share once, watch the progress in the console window and when it is done your files are clean. The cleaning algorithm is the same as in the XLStylesTool, so you have all the same options that you were able to set in the WinForms app.
2. The second ask was to see who keeps on corrupting files. I wrote another console utility MonitorFileAccess that can detect who opens what file and when for edit. Now you can catch people that keep on re-introducing bad styles and named ranges into your cleaned up files (apparently it is a common problem as well). Or if you ask someone to edit your Word document or PowerPoint presentation you can see when that someone actually got to your request and how long did he/she have the file open for.

Once again please leave me a comment if you use these utilities and let me know what you think of them. My family, friends and now user feedback is what inspires me to write these applications in my spare time and keep on helping resolve common problems that affect thousands of people.

UPDATE [8/25/11]: added one more version of XLBulkCleaner with subfolder support based on the user ask from an earlier post. Get XLBulkCleanerSF from the link above if you need to: a) process really large volumes of files (I increased buffer size in this version) and/or b) that are placed in multiple folders and you want to keep this folder structure intact.

This entry was posted in Computers and Internet. Bookmark the permalink.

30 Responses to Adding more utilities

  1. David says:

    AWE. SOME. Seriously, this is a huge help. Thanks so much for taking the time and uploading for all to use!

  2. Pingback: Sharing useful utilities | XLGeek's space

  3. Jason says:

    The XLStylesTool is a brilliant package! Install, run, and problem solved. Thank you for so generously providing this software.

  4. Joseph says:

    Sergie, this XLSytlesTool is WONDERFUL. Thank you SO much. After 5 hours of trying to figure this out, your tool solved our issue in seconds!
    Many thanks!
    Joseph (Toronto)

  5. Asit says:

    Hey DLGeek (I am guessing your name is Sergie)

    Thanks for helping us out.
    I cant access the skydrive. Can you please try the alternate approach about mirroring that you mentioned above?

    • XLGeek says:

      Hi Asit, I setup Silverlight 5 version on Windows Azure specifically for people that don’t have SkyDrive access that you can use right now (see this post and next post). You can even pin Silverlight app to your taskbar as a shortcut. You will need Silverlight 5 if you don’t have it yet and that’s it. I will try to get to the mirror in the near future. Also, signing up for Windows Account that will give you access to the SkyDrive is free and easy. Thank you for the feedback.

  6. Hasan says:

    can you please give me XLStylestool.exe download link ?

    • XLGeek says:

      It is already posted: first hyperlink in this post. It has both .Net3.5 and .Net4.0 versions in two separate clearly named zip files. If you can’t access it then you probably don’t have Windows Live or Hotmail account. It is free to setup. You can always use web browser version that doesn’t require Windows Live Hotmail account.

  7. Kimberly Ng says:

    It’s appear “too many different cell formats” then i use the XLStylestool.exe to run it & process the file it’s show ” File contains corrupted date”

    • XLGeek says:

      Your file is either password protected or you are trying to use a binary file with *.xls or *.xlsb extension. Remove a password or save file as xlsx or xlsm and everything will work.

  8. Paul says:

    Seriously!! Dude! This worked as promised – and you saved me 6 weeks of work + Thanks you!!!

  9. Connor says:

    Ok so I feel completely stupid that I don’t know how to do this, but how do you change the directory in the tool? Every time I enter the directory of the shared folder and the cleaning parameters and hit Enter the tool just closes. I feel like I should know how to do this.

    • XLGeek says:

      Hi Conner,

      Did you change the directory in the command window to where the tool is? Did you enclose the path of the monitored folder in double quotes? Try mapping UNC path to a letter drive if double-quotes don’t work. Also, make sure that the identity that launches the exe file has read-write rights to the monitored share. Reply back if you still have issues.

  10. Konami says:

    Hi XLGeek, nice to meet people like you. first I already download your XLCleaner and run it. I hope my xlsx file can be fix with this XLCleaner. but it nothing happen. it say : unrecoverable styles corruption. I open my xlsx file it still show me an error : Excel found unreadable content in ‘xxx.xlsx’ Do you want to recover the content of this workbook ? I answer yes, it give me a messagebox says
    Removed Records: Style from /xl/styles.xml part (Styles)
    Repaired Records: Format from /xl/styles.xml part (Styles)
    than i click error log


    error030600_01.xml
    Errors were detected in file ‘F:\Pak Andrew\xBiaya Baru File Bermasalah.xlsx’

    Removed Records: Style from /xl/styles.xml part (Styles)


    Repaired Records: Format from /xl/styles.xml part (Styles)

    and it open my worksheet, but border format all gone.
    can you help me ? please …

    • XLGeek says:

      Hi Konami,

      Unfortunately this error message means your file is in unrecoverable state when it comes to formatting. Either your unique format definitions count (more likely) or max id for the style definition stored in the file (less likely) went over the limit corrupting the formatting on last file save. Chances are all other content like data and formulas is still intact. If you have a recent backup you are in luck. Otherwise there is no other way, but to re-create the formatting. Make sure to clean up the source files if you do copy/paste from other files to restore the formatting. Copy/paste from the files that have excessive style count is what got you in this state most likely.

  11. Aksel says:

    Thanks a lot! StylesCleaner didn’t start under Windows 8, but fortunately the BulkCleaner did.

  12. Andy says:

    Thank you!

  13. dEron says:

    Hey XLGeek.
    Thanks for your sharing and contribution. How can I get rid of the open and close process when cleanup multiple excel file? It seems I can’t just send the command with file name in the cmd.
    I think this function is also useful that I can cleanup file via cmd without open and close.

    • XLGeek says:

      Any file action will trip the clean up process. Start monitoring empty folder, dump all your bad files there (subfolders ok in the latest version), and watch all of your files get cleaned up. I tried 100+ files per batch w/o any issues. I know that other people in large corporate environments went even higher.

  14. CKYL says:

    Dear Sergei,

    Thank you so much for this. Firstly you are smart in coming up with these (even Microsoft links to your tool from their website), on top of being smart, you are magnanimous – helping many people around the world with their troubles for free (in your own way), and I am one of them.

    Once again, thank you.

  15. Lyn Kae Go says:

    You, Sir, are a genius. I was able to reset unnecessary formatting and reduce the file size significantly. You should teach how you do these cool stuff.

  16. deepion says:

    Hi XLGeek,
    First of all, thanks a lot for the great job you did and for sharing it for free! It saved me quite the headache.
    Now for the issue I have with XLBulkCleanerSF. While monitoring a folder, I’m getting an error (in the console log) whenever a file is open: “Couldn’t open ‘…~$filename’ for write access”. All the rest works as intended: all the moved or copied files are processed correctly.
    As I see it, your tool tries to modify the temporary file that Excel creates at opening and it’s unable to do it as it’s protected. Since in your instructions you said opening the file should process it, maybe I’m doing it wrong.
    I would really apreciate your help as the “process while opening” function is exactly what I need in my environment.
    Thank you

    • XLGeek says:

      I can’t replicate your issue on any OSs that I tried: win 7, 8.1, and 10 (all 64 bit). I’ve tried *.xlsx and *.xlsm files. Do you have other extensions on your files? Do you want to share sample files and detailed steps?

  17. deepion says:

    Thanks for the quick reply.
    After some more testing it seems like it doesn’t work when I open the file in a subfolder though in the monitored folder itself the same file works as expected. I’ve tested it with an empty(newly created .xlsx) file to avoid any file related issue and it behaves the same. That’s what I get on the console window:
    Monitoring c:\New folder
    Processing options: ForceNormal, RemoveExternal, UnhideNames
    Starting c:\New folder\New folder\~$Book1.xlsx (by the way it starts at the same time I open the file and hangs for about 1 minute)
    Couldn’t open c:\New folder\New folder\~$Book1.xlsx for write access
    If I do the same on the monitored folder I get:
    Starting c:\New folder\Book1.xlsx (it begins after I close the file)
    Finished: c:\New folder\Book1.xlsx
    Sorry for the lot of text, wanted to give you all the details and don’t have any option to attach a screenshot. It’s a Win7 x64 system, by the way, with MSOffice 2010.
    Thanks again.

    • XLGeek says:

      Thanks for the details. I can repro this behavior in the subfolder version of the app by going to 1+ level deep as you describe. I added subfolder support for bulk cleaning console app based on the specific user request on this blog and it was meant for “grab a bunch of files in subfolders, clean up in bulk preserving folder structure, move back to production location”. It was intended to boost productivity (i.e., bulk cleaning one folder at a time in the original app). So technically based on this case subfolders were never meant or tested for active monitoring as opposed to the idea of setting up single level “cleansing share” for users in highly restrictive environments. That was the original approach. I don’t think this is really blocking your ability to use the tool and have clean files: you can always do periodic copy/paste. What is your exact scenario? Is it just you or you are collaborating with a bunch of other people and for some reason must have proactive cleansing while maintaining strict subfolder structure?

  18. deepion says:

    Ok, I see. I just wanted to make sure I wasn’t doing anything wrong that kept it from working properly. My environment is pretty complex as I have thousands of excel files in a complex folder tree. Since this styles corruption is here for years now and no one addressed the issue, with time half of the files became corrupted. Now, I already started to make some mass cleanup using a powershell script to mass convert .xls files and than another script that would move all excel files out and in again from a folder tree. Using your tool to monitor it in the same time, I can repair 2k+ files in about 5 minutes, so I can confirm it works amazingly well.
    About the proactive cleansing I was just hoping to monitor my folders in order to eradicate completely this issue as I have quite a few people working every day and though I asked them to pay attention they might still corrupt some already cleaned files.
    Anyway, as everyone said, your tool is awesome and making it available for free makes of you a great person.
    Thanks a lot!

    • XLGeek says:

      Thanks for describing your scenario. I’ll see what the effort is to get subfolders running in active mode. The problem with subfolders is that I need to rely on the .net native object, so I don’t know what the limits are without extensive testing. Really glad the apps work for you. Ask your users to install the client version of the app and let them do the cleaning work themselves. Once they see that the files open faster they will be motivated to do it on their own.

  19. ATO says:

    Awsome tool. Great job sir. I salute you. I have been trying to solve this issue since year 2000. Just found this, tried this, succeeded and I am crying with joy. Thanks a million for your generosity.
    ATO

Leave a Comment or Ask a Question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s