{"id":67243,"date":"2006-05-25T22:02:00","date_gmt":"2006-05-25T22:02:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/05\/25\/how-can-i-change-all-the-lowercase-letters-in-an-excel-worksheet-to-uppercase-letters\/"},"modified":"2006-05-25T22:02:00","modified_gmt":"2006-05-25T22:02:00","slug":"how-can-i-change-all-the-lowercase-letters-in-an-excel-worksheet-to-uppercase-letters","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-change-all-the-lowercase-letters-in-an-excel-worksheet-to-uppercase-letters\/","title":{"rendered":"How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I change all the lowercase letters in an Excel worksheet to uppercase letters?

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

Hey, BC. Have you ever heard the old expression, \u201cIf you want something done you have to do it yourself\u201d? Well, to tell you the truth, we Scripting Guys had heard<\/I> of that old expression, but we never paid much attention to it. Do something ourselves, when maybe someone else could do it for<\/I> us? That\u2019ll be the day! (Editor\u2019s Note: Actually, the expression is \u201cIf you want something done <\/I>right you have to do it yourself.\u201d But the Scripting Guys don\u2019t pay much attention to that either.)<\/I><\/P>\n

Much to our surprise, however, it turns out that today is<\/I> the day. When we first saw your question we figured, \u201cWell, this should be easy: we\u2019ll just get Excel to change the letter case for us.\u201d Or so we thought. Try as we might, however, we couldn\u2019t figure out a way to get Microsoft Excel to make this change for us. We assumed that Excel had a Change Case command similar to that in Microsoft Word; it doesn\u2019t. We assumed that we could simply select all, change the font properties and make all the letters uppercase; we couldn\u2019t. We assumed we could do a search-and-replace, changing the letter case along the way; no such luck.<\/P>\n

Needless to say, by that time we were in a state of panic. But then Dean Tsaltas, the wisest of all the Scripting Guys, said, \u201cIf you need to change the letter case, why not just make that change yourself, without relying on a built-in Excel function to make the change for you.\u201d Talk about profound; no wonder all the Scripting Guys look up to Dean and turn to him whenever we need advice.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Do all the Scripting Guys really<\/I> turn to Dean when we need advice? What do you think we are: crazy? Of course<\/I> not. However, Dean thinks we pick on him all the time in this column, so we decided to say something nice about him for a change. He\u2019ll never know whether we really mean it or not!<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

As usual, once we listened to Dean the rest was easy:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1,1) = “abcdef”\nobjWorksheet.Cells(1,2) = “ghijkl”\nobjWorksheet.Cells(1,3) = “mnopqr”\nobjWorksheet.Cells(1,4) = “stuvwx”<\/p>\n
Wscript.Sleep 2000<\/p>\n
Set objRange = objWorksheet.UsedRange<\/p>\n
For Each objCell in objRange\n    objCell.Value = UCase(objCell.Value)\nNext\n<\/PRE>\n
Let\u2019s talk about what we did, and why. We start off by creating an instance of the Excel.Application<\/B> object and then set the Visible<\/B> property to True; that simply gives us a running instance of Excel that we can see on-screen. After that we use these two lines of code to create a new workbook, and to bind to the first worksheet in that workbook:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. You say that you\u2019re new to scripting with Microsoft Excel, and none of this makes much sense to you? No problem: just visit the Office Space archive<\/B><\/A>, where you\u2019ll find all sorts of articles devoted to scripting with Microsoft Excel.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After all that initial setup we use these four lines of code to type some characters in cells A1 through D1:<\/P>
objWorksheet.Cells(1,1) = “abcdef”\nobjWorksheet.Cells(1,2) = “ghijkl”\nobjWorksheet.Cells(1,3) = “mnopqr”\nobjWorksheet.Cells(1,4) = “stuvwx”\n<\/PRE>\n
We then pause the script for two seconds (Wscript.Sleep 2000<\/B>), just so we\u2019ll have a chance to see the spreadsheet before we start changing letter cases. Speaking of which, that spreadsheet should look something like this:<\/P>\"Microsoft \n

This, of course, is the point where we figured Excel would change the letter case for us. As we found out, Excel isn\u2019t going to be of much help here. Therefore, we did what Dean would do, and changed the letter cases ourselves.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, we didn\u2019t really<\/I> do what Dean would do; that would have involved buying a latte and checking out used car prices on the Internet. But you know what we mean.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
So what would<\/I> Dean do in this situation (at least in theory)? Well, he\u2019d no doubt begin by selecting all the cells on the worksheet that contained data. That sounds complicated, but it\u2019s actually no harder than this:<\/P>
Set objRange = objWorksheet.UsedRange\n<\/PRE>\n
What we\u2019re doing here is creating a Range<\/B> object (with the object reference objRange) that\u2019s mapped to the worksheet\u2019s UsedRange<\/B> property. The used range represents the range of contiguous cells that have data in them. For example, in our sample spreadsheet we have data in cells A1 through D1; thus the used range consists of cells A1 through D1. Suppose we had data in cell A1 and another piece of data in cell Z100. In that case the used range would consist of cells A1 through Z100, including all the blank cells in between.<\/P>\n
Yes, a very handy little thing to know about.<\/P>\n
Once we have a collection of cells we can set up a For Each loop to walk through each cell in the collection:<\/P>
For Each objCell in objRange\n    objCell.Value = UCase(objCell.Value)\nNext\n<\/PRE>\n
As you can see, inside the loop we do only one thing: we set the value of the cell to the uppercase version of whatever happens to be the value of the cell at the moment. (That\u2019s what the VBScript function UCase<\/B> is for: it converts letters to uppercase.) For example, the current value of cell A1 is this:<\/P>
abcdef\n<\/PRE>\n
After running cell A1 through the UCase function the value of cell A1 will be this:<\/P>
ABCDEF\n<\/PRE>\n
Etc., etc.<\/P>\n
Here\u2019s what we\u2019ll see once we\u2019ve run through the entire collection:<\/P>\"Microsoft \n

Which is just exactly what we wanted to see: all the letters on the worksheet have been converted to their uppercase equivalents. Best of all, we did it all by ourselves. Thanks, Dean: like we\u2019ve always said, you\u2019re the best!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I change all the lowercase letters in an Excel worksheet to uppercase letters?— BC Hey, BC. Have you ever heard the old expression, \u201cIf you want something done you have to do it yourself\u201d? Well, to tell you the truth, we Scripting Guys had heard of that old expression, but […]<\/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-67243","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 change all the lowercase letters in an Excel worksheet to uppercase letters?— BC Hey, BC. Have you ever heard the old expression, \u201cIf you want something done you have to do it yourself\u201d? Well, to tell you the truth, we Scripting Guys had heard of that old expression, but […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67243","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=67243"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67243\/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=67243"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67243"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67243"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}