How Can I Replace Text in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I replace text in an Excel spreadsheet?

— PD

Hey, PD. We’ll talk about replacing text in an Excel spreadsheet in just a moment. But first, let’s address the question that’s on everyone’s mind: what is the Scripting Guy who writes this column going to do today as his Italian vacation continues? Well, to be honest, the term “everyone” actually includes the Scripting Guy who writes this column: he has no idea what he’s going to do today, either. (Ah, the joys of trying to get a group of family members to agree on something!)

It’s possible that everyone will board the train this morning and head for the city of Pompeii, the city that was literally wiped from the face of the earth by the cataclysmic eruption of Mt. Vesuvius back in August of 79 AD. As most of you know, the destruction of Pompeii ranks as one of the world’s great tragedies, alongside the Great Chinese Earthquake of 1556, the sinking of the Titanic (1912), and the release of Windows ME (1999).

At any rate, the Scripting Guy who writes this column doesn’t know what he’s going to do today, other than moderate a debate about what he and the rest of the crew are going to do today. On the off-chance that no one out there is interested in listening the Scripting Family discuss what they do or don’t want to do today, here’s a script that searches a worksheet in an Excel spreadsheet and (per PD’s request) replaces any instances of the file path C:\Test\Image.jpg with the path C:\Backup\Image.jpg. That should tide everyone over until the Scripting Family reaches a consensus.

Wait; scratch that: there’s a good chance that the Scripting Family will never reach a consensus. But here’s the Excel search-and-replace script anyway:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
objRange.Replace “C:\Test\Image.jpg”, “C:\Backup\Image.jpg”

You say you’d like to know how this script works? What a coincidence; so would we. Let’s see if we can go through it line-by-line and come up with the answer.

As you can see, the script starts out simple enough: all we do is create an instance of the Excel.Application object and then set the Visible property to True. Needless to say, that gives us a running instance of Microsoft Excel that we can see onscreen. We next call the Open method to open the file C:\Scripts\Test.xls, then use the following line of code to bind to the first worksheet in that file:

Set objWorksheet = objWorkbook.Worksheets(1)

Wow; that was simple, wasn’t it?

Amazingly enough, the rest of the script is equally simple. When you perform a search-and-replace operation in Excel, you must specify the portion of the worksheet (i.e., the “range”) in which to carry out this task. Because we want to replace text throughout the entire worksheet that means that our range must encompass the entire worksheet (that is, each and every cell that has data in it). Admittedly, that sounds pretty darn complicated: how are we supposed to know which cells have data in them and which ones don’t? To tell you the truth, we have no idea how we’re supposed to do that. Fortunately, though, it doesn’t matter; that’s because we can use Excel’s UsedRange property to create an instance of the Range object that automatically includes every cell that has data in it:

Set objRange = objWorksheet.UsedRange

If only deciding what to do today was that easy, eh?

After we’ve defined the range we can then call the Replace method, passing this method two parameters: the text we want to search for (C:\Test\Image.jpg) and the replacement text (C:\Backup\Image.jpg). That’s what this line of code is for:

objRange.Replace “C:\Test\Image.jpg”, “C:\Backup\Image.jpg”

Believe it or not, that’s all we have to do.

Well, that and figure out today’s agenda, of course. We know what you’re thinking: “Didn’t the Scripting Guy who writes this column carefully plan out an agenda (plus a backup agenda) for the entire vacation, just to ensure that problems like this didn’t happen?” Of course he did. And yet he’s saying that this didn’t prevent the problem from happening anyway? Let’s put it this way: if you’ve ever gone on a family vacation, then you understand why having an agenda and a backup agenda doesn’t really mean much.

Oh, well: ci vediamo domani. (Which, we think, means “See you tomorrow.” If it doesn’t, well, we’ll see you tomorrow anyway.)

ScriptingGuy1

Follow