{"id":64223,"date":"2007-08-16T01:35:00","date_gmt":"2007-08-16T01:35:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/08\/16\/how-can-i-replace-text-in-an-excel-spreadsheet\/"},"modified":"2007-08-16T01:35:00","modified_gmt":"2007-08-16T01:35:00","slug":"how-can-i-replace-text-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-replace-text-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Replace Text in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— PD <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

It\u2019s possible<\/I> 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\u2019s great tragedies, alongside the Great Chinese Earthquake of 1556, the sinking of the Titanic (1912), and the release of Windows ME (1999).<\/P>\n\n<\/THEAD>\n\n\n
\n

Note to our fellow Microsoft employees<\/B>. Come on, guys, loosen up a little: we\u2019re just kidding<\/I>. After all, everyone knows that Windows ME was a truly great<\/I> operating system, one that introduced a huge number of innovative new features to the computer world.<\/P>\n

Like what<\/I>? Well, like \u2013 sorry; gotta run. We have a train to catch.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

At any rate, the Scripting Guy who writes this column doesn\u2019t know what he\u2019s 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\u2019t want to do today, here\u2019s a script that searches a worksheet in an Excel spreadsheet and (per PD\u2019s 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.<\/P>\n

Wait; scratch that: there\u2019s a good chance that the Scripting Family will never<\/I> reach a consensus. But here\u2019s the Excel search-and-replace script anyway:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set objRange = objWorksheet.UsedRange<\/p>\n
objRange.Replace “C:\\Test\\Image.jpg”, “C:\\Backup\\Image.jpg”\n<\/PRE>\n
You say you\u2019d like to know how this script works? What a coincidence; so would we. Let\u2019s see if we can go through it line-by-line and come up with the answer.<\/P>\n
As you can see, the script starts out simple enough: all we do is create an instance of the Excel.Application<\/B> object and then set the Visible<\/B> 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<\/B> 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:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Wow; that was<\/I> simple, wasn\u2019t it?<\/P>\n
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 \u201crange\u201d) 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\u2019t? To tell you the truth, we have no idea how we\u2019re supposed to do that. Fortunately, though, it doesn\u2019t matter; that\u2019s because we can use Excel\u2019s UsedRange<\/B> property to create an instance of the Range<\/B> object that automatically includes every cell that has data in it:<\/P>
Set objRange = objWorksheet.UsedRange\n<\/PRE>\n
If only deciding what to do today was that easy, eh?<\/P>\n
After we\u2019ve defined the range we can then call the Replace<\/B> 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\u2019s what this line of code is for:<\/P>
objRange.Replace “C:\\Test\\Image.jpg”, “C:\\Backup\\Image.jpg”\n<\/PRE>\n
Believe it or not, that\u2019s all we have to do. <\/P>\n
Well, that and figure out today\u2019s agenda, of course. We know what you\u2019re thinking: \u201cDidn\u2019t 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\u2019t happen?\u201d Of course he did. And yet he\u2019s saying that this didn\u2019t<\/I> prevent the problem from happening anyway? Let\u2019s put it this way: if you\u2019ve ever gone on a family vacation, then you understand why having an agenda and a backup agenda doesn\u2019t really mean much.<\/P>\n
Oh, well: ci vediamo domani. (Which, we think, means \u201cSee you tomorrow.\u201d If it doesn\u2019t, well, we\u2019ll see you tomorrow anyway.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I replace text in an Excel spreadsheet?— PD Hey, PD. We\u2019ll talk about replacing text in an Excel spreadsheet in just a moment. But first, let\u2019s address the question that\u2019s on everyone\u2019s mind: what is the Scripting Guy who writes this column going to do today as his Italian vacation […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-64223","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I replace text in an Excel spreadsheet?— PD Hey, PD. We\u2019ll talk about replacing text in an Excel spreadsheet in just a moment. But first, let\u2019s address the question that\u2019s on everyone\u2019s mind: what is the Scripting Guy who writes this column going to do today as his Italian vacation […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64223","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64223"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64223\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}