Spreadsheet Cleanout

January 15, 2007 9:03 PM

Help me remove unwanted spreadsheet info - I want to archive a massive buch of data from years of Excel spreadsheets but, I don't want to save all the extra gumph associated with macros and links in spreadsheets.

The Spreadsheets are financial models built from templates which rely on Macros and Links to other sheets to grind the results out.

I just want the numbers in the sheets, no calculations, no macros - these numbers are locked in stone.

Right now the Spreadsheets are about 3 meg each and I don't want to load them in to a repository like this when they could be broken down to a couple of hundred kB.

Is there a magic tool available to do this en masse to several hundred sheets in a folder structure?

posted by moocheen to computers & internet (10 comments total)

Well, you probably want something like CSV (comma-separated values). This is essentially a spreadsheet in text file format, which had the bonuses of being 1) small, and 2) no matter what Microsoft does with the Office suite, you will always be able to open it.

You'll want something along the lines of this found-on-google program to automate the conversion. If push comes to shove, you can do it by hand from Excel.
posted by niles at 9:41 PM on January 15, 2007

Save As... Comma Separated Values (CSV). Done.
posted by potch at 9:48 PM on January 15, 2007

On preview...
Ah Crap, what he said.
posted by potch at 9:49 PM on January 15, 2007

Indeed, a great solution but, perhaps I was a bit glib in wanting to strip "everything" out.

I would prefer to keep these as spreadsheets (.XLS) to keep formatting and a couple of other bits and pieces.

(I still need to be able to open them to graph with later and managing CSVs might be a bit too much)

Can it be done without me changing the file format?

Thanks.
posted by moocheen at 10:06 PM on January 15, 2007

Well, no. The extra "gumph" includes the formatting.

Have you tried collecting the files into a compressed archive (Send To > Compressed)? If you select the "root" directory and compress everything in it, then the folder structure will be preserved. You may the resultant file considerably smaller then the original set of files.
posted by SPrintF at 10:11 PM on January 15, 2007

I see.

I had assumed that the formatting was pretty small in size (borders, colouring etc) but maybe not.

I want to put them into a storage repository that allows for indexing and searching. Unfortunately, the indexing doesn't work with compressed files.

Maybe I'll just bite the bullet and throw the files in now even though they are huge.

I really just want a system to 'finalise' these spreadsheets to remove Macros and External Links.
posted by moocheen at 10:16 PM on January 15, 2007

I'm a big fan of the ASAP utilities add in for excel. It includes tools to 'remove all macros' and 'remove all links to external files'.

It's not going to do what you want en masse but if you have to resort to opening up each file, this will make life easier. You could customise the ASAP toolbar to include just the tools you want.
posted by Ness at 2:43 AM on January 16, 2007

One possible lead you might want to pursue would be the "Paste Special -> Values" function. When you copy a range of cells that is formatted, has formulas, etc., this will paste only the actual cell values.

The way I'd envision this working would be a VBA macro (probably sitting in an Excel file of its own) that loops through all files in a directory, and executes the following pseudocode:

For each file in the directory:
... Open the file
... Count the number of sheets in the file
... Create a new file
... Add or delete sheets so the new file has the same number of sheets as the old file
... Rename each sheet in the new empty file so the sheets have the same names as the old file
... For each sheet in the existing file
...... Select the entire sheet (A1:IV65536)
...... Copy it to the clipboard
...... Activate the new workbook
...... Navigate to cell A1 in the appropriate sheet
...... Paste Special -> Values
... Next sheet
... Save the new workbook in a different location than the old workbook
... Close the new workbook
... Close the old workbook
Next File

You're probably looking at about 50 lines of code, tops, and nothing difficult for someone familiar with VBA in Excel.
posted by Doofus Magoo at 3:10 AM on January 16, 2007

What Doofus said. Or along those lines. I can do it for you if you drop me a line. But perhaps you're better off having a stab at it yourself. It's not tooo difficult once you get up that learning curve.
posted by NailsTheCat at 7:50 AM on January 16, 2007

NailsTheCat: It's not tooo difficult once you get up that learning curve.

Agreed, and with Excel's "Record Macro" feature, even the learning curve wouldn't be that bad, since you could then get your hands on some quick-and-dirty code that gives you the core syntax to do what you want to do.
posted by Doofus Magoo at 8:08 AM on January 16, 2007