\n| \n Note<\/B>. Sure it\u2019s tough, but if you\u2019re going to make your home in sun-drenched Seattle then having your hair bleached out – so much so that your hair begins to look<\/I> grey – is just one of those things you have to learn to live with.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n<\/DIV>\n At any rate, it turns out that we\u2019ve never<\/I> told people how to center text in an Excel cell. Well, until now, that is:<\/P>Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add<\/p>\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n objWorksheet.Cells(1, 1) = “A”\nobjWorksheet.Cells(1, 2) = “B”\nobjWorksheet.Cells(1, 3) = “C”<\/p>\n objWorksheet.Cells(1, 2).HorizontalAlignment = -4108\n<\/PRE>\n At the risk of spoiling the suspense, we\u2019ll tell you that the only line of code you really need to worry about is the last one; the rest of the script simply creates an Excel spreadsheet and adds some data to it. To do that, the script first creates an instance of the Excel.Application<\/B> object and then sets the Visible<\/B> property to True; that gives us a running instance of Excel that\u2019s visible on screen. We use the Add<\/B> method to add a new workbook to our instance of Excel, then use this line of code to bind to the first worksheet in that workbook:<\/P>Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\nOf course, a script that centers text in a cell isn\u2019t all that impressive unless you actually have some text in that cell. Therefore, we use these three lines of code to put the letters A<\/I>, B<\/I>, and C<\/I> into cells A1, B1, and C1, respectively:<\/P>objWorksheet.Cells(1, 1) = “A”\nobjWorksheet.Cells(1, 2) = “B”\nobjWorksheet.Cells(1, 3) = “C”\n<\/PRE>\nIn case you\u2019re wondering, that gives us a spreadsheet that looks like this:<\/P>![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/centered1.jpg\") \n
Now how do we center the text in one of the cells? Well, this line of code centers the text for cell B1 (that is, cell row 1, column 2):<\/P>
objWorksheet.Cells(1, 2).HorizontalAlignment = -4108\n<\/PRE>\nAs you can see, all we have to do is set the value of the HorizontalAlignment<\/B> property of the cell to -4108; that will center the text in the cell. If we wanted the cell to be right-aligned, we\u2019d set HorizontalValue to -4152; set the value to -4131 to left-align the text.<\/P>\n Run the script, and the finished spreadsheet will look like this, with the text in cell B1 centered:<\/P>![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/centered2.jpg\") \n
Another day, another question answered. Speaking of which, how many times have<\/I> we answered a question about centering text in Excel? <\/P>\n
Oh, right: once. The sun appears to be bleaching out our brains as well as our hair \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! How can I center text in an Excel cell?— MV Hey, MV. You know, every now and then we get asked a question and we think to ourselves, \u201cWow, how many times do you suppose we\u2019ve answered this question?\u201d And, of course, when we go to double-check we find out that we\u2019ve […]<\/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":[48,49,3,5],"class_list":["post-68983","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" Hey, Scripting Guy! How can I center text in an Excel cell?— MV Hey, MV. You know, every now and then we get asked a question and we think to ourselves, \u201cWow, how many times do you suppose we\u2019ve answered this question?\u201d And, of course, when we go to double-check we find out that we\u2019ve […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68983","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=68983"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68983\/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=68983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n