Excel is the death of me!

December 19, 2011 6:41 PM

I have about 200 separate Excel spreadsheets that I need to change one field in. There is a date in G4 that needs to be the same in each sheet. Is there way way to do this without opening, editing, and saving each sheet one at a time?
posted by keep it tight to Computers & Internet (10 answers total)

To automate this process, you'll need to use VBA. But before anyone can try to help you, you'll need to provide some more information. What version of Excel are you using? Are all the spreadsheets that need to be updated stored in one folder? Do all of the spreadsheets have the same structure (e.g., is the date that needs to be updated in cell G4 always on Sheet1 of the workbook)?
posted by Jasper Friendly Bear at 7:05 PM on December 19, 2011

opening, editing, and saving each sheet one at a time?

If you can't find a more automated way, you can open one, start recording a macro, select the cell, change the date, click in a blank cell, save and close the file. Stop recording and assign a shortcut (Ctrl+W, for example). Verify that the cell being changed in the code is always G4. Then you can open them in chunks of fifty or so. Ctrl+W through them and it shouldn't take too long. I do this when I have 10-12 files to format and it takes less than a minute.
posted by soelo at 7:13 PM on December 19, 2011 [1 favorite]

VBA is definitely the way. Here's an old post that contains VBA snippets to get you started:

Option 1: You can for instance open all the workbooks (just select all in explorer and drag and drop into Excel) and then loop through them. (Recording a macro can help you find the right code for the cell update.)

Option 2: Or you can get a list of files from a directory and then automate the opening of the workbooks, the update and save.

I don't believe there is a way to update a cell without opening either manually or under automation. You can read a single cell without opening, although probably doesn't work in Excel 2007 and greater. You might be able to do an UPDATE using SQL to access Excel--I suspect not though and it wouldn't be worth the hassle to find out when it's so easy to do it using VBA and iterating through the files.
posted by NailsTheCat at 7:42 PM on December 19, 2011

If all the spreadsheets are in one workbook, you can highlight all the sheets (scroll to the first, select, then scroll to the last, shift select), select G4, then change the field.
posted by yb2006shasta at 8:04 PM on December 19, 2011

VBA. you should start googling around for a few hours, and if you still can't solve your problem, then come back.
posted by cupcake1337 at 8:26 PM on December 19, 2011

If the Excel spreadsheets are saved in an XML format, you can also use an XML DOM parser to manipulate the save file. What's your level of programming experience?
posted by DetriusXii at 8:33 PM on December 19, 2011

I wouldn't bother editing the raw XML. Excel files are compressed and the worksheets are stored in a nested directory, so it's a bit of an ordeal just getting to the XML file to tweak it. Then you'd have to hope that you're not messing up the file format making your changes. It'd be much easier to do it with VBA since it's only 200 files.
posted by zixyer at 11:45 PM on December 19, 2011

If these files are tiny, you can probably open them all at once. The bigger they get, the fewer you'll want to open at one time. If you have a bunch of huge files open, Excel may slow to a crawl and it may even slow down your whole system for a while.

If you end up writing some code that opens, updates and closes each file, you may find this page interesting. It outlines a pretty simple way of getting and list of file names out of a folder and into Excel by using your web browser to display the folder contents and copy/pasting it into Excel.
posted by soelo at 8:40 AM on December 20, 2011

I'm guessing your level of experience is small otherwise you wouldn't have asked the question (in the way you did).

So, if this is a one-off then getting a strong cup of coffee and just sitting down and editing one after another by hand should take you about 90 minutes (possibly in two sessions). Select all, open all in excel, then repeat: change the topmost, close that window, until finished.

The alternative, doing VBA research, entering the code, testing it, debugging and running the batch job will probably take you longer but you will acquire knowledge you can use later.

Whichever way you choose, make a backup of the folder before making changes.
posted by epo at 10:07 AM on December 20, 2011

doing VBA research, entering the code, testing it, debugging and running the batch job will probably take you longer

This exactly why you would record a macro, so you don't need to write code, test and debug it. I recorded this one and it seems to do the job just fine:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range("G4").Select
ActiveCell.FormulaR1C1 = "1/1/2010"
Range("G5").Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

All you need to do is change the date entered, save it into a file, open the rest of your files (in small chunks if they are large files) and Ctrl+w each one. Make sure you keep the file with the macro in it open.
posted by soelo at 12:27 PM on December 20, 2011