Excel Tools (Copy & Unused Format Remover)

Moving away from Dynamics GP stuff and into Excel, two unique issues arise at my workplace. One, due to the way an internal piece of software integrates with Excel, it opens a new Excel Application instance every time you open a workbook. Due to this, each workbook/worksheet essentially loses viability of each other. The most annoying feature lost due to this is the ability to copy worksheets between workbooks. The first feature of my tool is it scans all active Excel instances and lists all worksheets currently open allowing you to select any worksheets and the destination workbook to copy to. Because I allow the source to be from different workbooks, I perform the copy one by one by saving it to your temp folder and opening it within the destination Excel application. The only downside to this is if you have cell references between worksheets, they will not move over correctly (they will be pointed to the original workbook).

The second unique issue we encounter a lot for unknown reasons is the “Too many different cell formats” error
Excel Cell Formats

I have used the VBA solution to this that deletes all unused formats however we have protected worksheets in our workbooks which the VBA solution can’t solve. The only way (short of removing the protected worksheets) is to manipulate the .xls file’s XML by converting the file to a zip, unzipping it, editing the styles.xml file, re-zipping and converting it back to an Excel file.

I have compacted both of these into one tool for convenience. I can provide source code upon request but it’s not the cleanest.

Link to tool: Excel Tools (Version 1.0.0.0)

Enjoy,

~James

Disclaimer: I am very much an amateur programmer. I have never taken an official Microsoft course. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set. Users accept all the risk of using anything I provide and are encouraged to thoroughly test in a dedicated test environment before moving to production. Files I provide are free for use and are not to be resold. By using my files you agree to not hold me liable for any damages caused by said files.

Advertisements