<\/div>\n
Here is today\u2019s script:<\/p>\n
$excel = new-object -comobject excel.application\n$excelFiles = Get-ChildItem -Path C:\\fso -Include *.xls, *.xlsm -Recurse\nForeach($file in $excelFiles)\n{\n $workbook = $excel.workbooks.open($file.fullname)\n $worksheet = $workbook.worksheets.item(1)\n $excel.Run(\"CreateChart\")\n $workbook.save()\n $workbook.close()\n}\n$excel.quit()\n<\/pre>\nThe script begins by creating an instance of the Excel.Application<\/b> object. This is the main object that you use when you are working with automating Office Excel. From the Excel.Application<\/b> object, we gain access to all the other items that are needed for automation. To create an object in Windows PowerShell, we use the New-Object<\/b> cmdlet. This cmdlet has an option of creating a COM object, and this is what we need here. This is the same object you would use if you were working in VBScript. Because we will need to use the Excel.Application<\/b> object later in our script, we store the object in a variable we call $excel<\/b>. This is seen here:<\/p>\n$excel = new-object -comobject excel.application<\/pre>\nNext we need to obtain a list of all the .xls and .xlsm files in the folder, and to do this we use the Get-ChildItem<\/b> cmdlet. If you are not familiar with it, a file with an .xlsm extension is a macro-enabled Office Excel workbook. This file type was introduced in Microsoft Office 2007. To limit our search to only these two types of files, we use the include<\/b> switch with the Get_ChildItem<\/b> cmdlet. The include<\/b> switch is allowed to receive an array of file types, and we can therefore use more than one file type separated by commas. The recurse<\/b> parameter is used to tell Get-ChildItem<\/b> to burrow its way down through the folder. The resulting collection of file objects is stored in the $excelFiles<\/b> variable. This line of code is shown here: <\/p>\n$excelFiles = Get-ChildItem -Path C:\\fso -Include *.xls, *.xlsm \u2013Recurse<\/pre>\nWe then need to work our way through the collection of files stored in the $excelFiles<\/b> variable. Whenever you hear the word collection<\/b> you should think foreach<\/b>, or in VBScript you should think foreach next<\/b>. The variable $file<\/b> is used to allow us to access a single file at a time from the collection. This line of code is seen here: <\/p>\nForeach($file in $excelFiles)<\/pre>\nNow we need to open the Office Excel workbook. To do this, we use the open<\/b> method from the workbooks collection. We get the workbooks<\/b> collection by using the workbooks<\/b> property from the Excel.Application<\/b> object. When we use the open<\/b> method, we need to supply the path to the workbook we wish to open. The open<\/b> method returns a workbook<\/b> object, which we store in the variable $workbook<\/b> as seen here: <\/p>\n$workbook = $excel.workbooks.open($file.fullname)<\/pre>\nWe then use the worksheets<\/b> property from the workbook<\/b> object to obtain a collection of worksheets. We then use the item<\/b> method to return the first worksheet in the collection. We store that worksheet in the variable $worksheet<\/b>. This is seen here:<\/p>\n$worksheet = $workbook.worksheets.item(1)<\/pre>\nAfter we have selected a specific worksheet, we use the run<\/b> method from the Excel.Application<\/b> object to run a specific macro. The macro is seen here:<\/p>\n
![\"Image](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/2009\/january\/hey0113\/hsg_excel2_01.jpg\")
<\/p>\n
<\/p>\n
The run<\/b> method can be used to run either a macro or a function. This line of code is seen here:<\/p>\n$excel.Run(\"CreateChart\")<\/pre>\nWhen you run the script, you will not see any output on the screen. But if you open your Office Excel workbook, you will see that the macro ran and created a nice chart. This is seen here:<\/p>\n
![\"Image](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/2009\/january\/hey0113\/hsg_excel2_02.jpg\")
<\/p>\n
<\/p>\n
Now that we have run the macro, we want to save the workbook and then close it out: <\/p>\n
$workbook.save()\n$workbook.close()\n<\/pre>\nWe continue looping through the collection of workbooks until we are done. When we are done, we call the quit<\/b> method to end the Office Excel process: <\/p>\n$excel.quit()<\/pre>\nWell, GG, that is about it. I sincerely hope this script will ease your workload and help ease some of the tension you have at work. Sorry I do not have a script to transform a pointy-headed boss into a normal human being. See you tomorrow. Until then, peace!<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I don’t know about you, but I love Microsoft Office Excel macros. Come Valentine’s Day, my computer gets a card. Office Excel macros save me so much time, it is just unbelievable. There is just one problem, and that is that I have to open Office Excel and manually run the macro. […]<\/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,45],"class_list":["post-54623","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I don’t know about you, but I love Microsoft Office Excel macros. Come Valentine’s Day, my computer gets a card. Office Excel macros save me so much time, it is just unbelievable. There is just one problem, and that is that I have to open Office Excel and manually run the macro. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54623","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=54623"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54623\/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=54623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54623"}],"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,")
<\/h2>\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,")
<\/p>\n