{"id":13031,"date":"2011-08-12T00:01:00","date_gmt":"2011-08-12T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/08\/12\/use-powershell-to-work-with-pictures-in-microsoft-excel\/"},"modified":"2011-08-12T00:01:00","modified_gmt":"2011-08-12T00:01:00","slug":"use-powershell-to-work-with-pictures-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-work-with-pictures-in-microsoft-excel\/","title":{"rendered":"Use PowerShell to Work with Pictures in Microsoft Excel"},"content":{"rendered":"

Summary<\/strong>: Guest Blogger Robert Robelo shows how to use Windows PowerShell to work with images in Microsoft Excel.<\/p>\n

 <\/p>\n

Microsoft Scripting Guy Ed Wilson here. Our guest blogger today is Robert Robelo. First, I want to give Robert a chance to tell us a little bit about himself.<\/p>\n

I live in Nicaragua. I have been an incomplete quadriplegic (paralyzed from the chest down, but have limited mobility in upper limbs) for more than twenty years. Because of that I have not had a job since then. Before I got shot, I was an officer in the Nicaraguan Resistance Army, better known as Contras. One of my multiple responsibilities was to train, support, and maintain the IT structure (guess that would be a chief information officer), which was no more than twenty individual machines. My other responsibilities included intelligence, counterintelligence, and friendly forces liaison officer; obviously, before becoming an officer, I was an infantry and artillery soldier. I later became a sniper.<\/p>\n

Robert is going to talk to us about working with images in Microsoft Excel via Windows PowerShell. Take it away, Robert!<\/p>\n

 <\/p>\n

Inserting an image in an Excel spreadsheet with Windows PowerShell is not an everyday chore for IT pros, but there very well may come a time where it would be useful to know how to do such a task.<\/p>\n

An Excel spreadsheet has a Shapes<\/b> property; it holds a collection of objects in the drawing layer, such as AutoShapes, freeforms, OLE objects, and images. To insert an image in the spreadsheet, use the Shapes <\/b>collection’s AddPicture <\/b>method. This method takes seven required arguments, which are shown in the following figure.<\/p>\n

\"Image<\/a><\/p>\n

The AddPicture<\/b> method returns a Shape<\/b> object, which should be assigned to a variable. The Shape<\/b> can then be relocated, resized, rotated, transformed, cropped, formatted, duplicated, and so on.<\/p>\n

Let’s fire up an Excel instance, get a new workbook, and grab the ever-present Sheet1. Before we do that, let’s set up a few Constant<\/b> variables. First, the MsoTriState<\/b> constants that apply to the AddPicture<\/b> method and then a couple Constant<\/b> variables of our own. These will hold the default width and height of a cell; they can be very useful to place and size the image we are going to insert. However, they are not necessary if you prefer to place and size the image with greater accuracy. In this sample, we will place the image’s upper left corner in cell C3’s upper left corner—that is two cells from the left and two cells from the top of the sheet, and we will make the image two cells wide and four cells tall. (Note<\/b>   The image path is set to C:\\Ensign.jpg; you can substitute this with your own image or download the .zip file<\/a> that contains that image as part of a test kit. Read more below.)<\/p>\n

# Excel Constants<\/p>\n

# MsoTriState<\/p>\n

Set-Variable msoFalse 0 -Option Constant -ErrorAction SilentlyContinue<\/p>\n

Set-Variable msoTrue 1 -Option Constant -ErrorAction SilentlyContinue<\/p>\n

 <\/p>\n

# own Constants<\/p>\n

# cell width and height in points<\/p>\n

Set-Variable cellWidth 48 -Option Constant -ErrorAction SilentlyContinue<\/p>\n

Set-Variable cellHeight 15 -Option Constant -ErrorAction SilentlyContinue<\/p>\n

 <\/p>\n

$xl = New-Object -ComObject Excel.Application -Property @{<\/p>\n

 Visible = $true<\/p>\n

 DisplayAlerts = $false<\/p>\n

}<\/p>\n

$wb = $xl.WorkBooks.Add()<\/p>\n

$sh = $wb.Sheets.Item(‘Sheet1’)<\/p>\n

 <\/p>\n

# arguments to insert the image through the Shapes.AddPicture Method<\/p>\n

$imgPath = ‘C:\\Ensign.jpg’<\/p>\n

$LinkToFile = $msoFalse<\/p>\n

$SaveWithDocument = $msoTrue<\/p>\n

$Left = $cellWidth * 2<\/p>\n

$Top = $cellHeight * 2<\/p>\n

$Width = $cellWidth * 2<\/p>\n

$Height = $cellHeight * 4<\/p>\n

 <\/p>\n

# add image to the Sheet<\/p>\n

$img = $sh.Shapes.AddPicture($imgPath, $LinkToFile, $SaveWithDocument,<\/p>\n

 $Left, $Top, $Width, $Height)<\/p>\n

$xl.Speech.Speak(‘Add an image to the Sheet through the Add Picture Method.’)<\/p>\n

Pretty easy, isn’t it? You can examine the Shape <\/b>object with the Get-Member<\/b><\/a> cmdlet to see the different methods and properties it has. There are too many to explore in one blog post anyway.<\/p>\n

Now, let’s close the workbook without saving it, quit Excel, and clean up its insubordinate instance with the Remove-ComObject<\/b> function<\/a>. Basically, the Remove-ComObject<\/b> function collects all variables that were assigned System.__ComObject<\/b> objects within the caller scope and are not Constant<\/b> or Read-Only<\/b> variables:<\/p>\n

# close without saving the workbook<\/p>\n

$wb.Close($false)<\/p>\n

$xl.Quit()<\/p>\n

Remove-ComObject<\/p>\n

There are many techniques that attempt to release these defiant System.__ComObject<\/b> objects. Some do get the job done eventually, but they look chaotic or involve repeated calls to Runtime.Interopservices.Marshal<\/b>‘s ReleaseComObject<\/b> method. Using the Remove-Variable<\/b><\/a> cmdlet the traditional way to get rid of these vestigial System.__ComObject<\/b> objects will not suffice either, but using it as I do in the Remove-ComObject<\/b> function, this capricious cmdlet does its job very well with a little help from its friends.<\/p>\n

First, the Remove-ComObject<\/b> function briefly pauses for half a second to let Windows PowerShell mark the System.__ComObject<\/b> object as Disposed<\/b> and ready to be collected by the Garbage Collector<\/b>. Next, a variable is set with the Management.Automation.ScopedItemOptions<\/b> ReadOnly<\/b> and Constant<\/b> enumerations. This variable will be the second half of the filter that will be applied in the following step. Then, the Get-Variable<\/b><\/a> cmdlet fetches all variables in the caller scope (-Scope 1<\/b>) and pipes them to the Where-Object<\/b><\/a> cmdlet, where a filter is applied to exclude Constant<\/b> or Read-Only<\/b> variables, but whose value are of type System.__ComObject<\/b>. This first half of the filter is done by comparing the value’s PSTypeNames<\/b> collection against the string System.__ComObject<\/i><\/b> with the Contains<\/b> operator. The unfortunate and rebellious variables that go through are then piped to the Remove-Variable<\/b><\/a> cmdlet and are slyly shredded to oblivion. Finally, the Garbage Collector<\/b> gets a call to sanitize the area and collect the scraps.<\/p>\n

The Remove-ComObject<\/b> function is an advanced function because it has common parameters, which permits the user to use its Verbose<\/b> flag to divulge which variables were removed. I do want to point out a very important fact: the Remove-ComObject<\/b> function removes all variables declared in the function’s caller scope. It is safer to use it within scripts, functions, or modules, but you can also call it from the command line. Just beware that you may wipe out other System.__ComObject<\/b> objects. This is the reason the function excludes Constant<\/b> or Read-Only<\/b> variables. It is up to you to declare those precious variables as such.<\/p>\n

I have set up a test that demonstrates the efficacy of my technique to successfully release System.__ComObject <\/b>objects before they become ghosts in your machine. You can download the test kit here<\/a>; the results are displayed in the console. The test will show RAM usage and Excel processes running before and after the execution of two scripts, one that cleans up with a Remove-ComObject<\/b> call and another that does not. These scripts also demonstrate other image manipulation that you might find interesting. Unzip the contents to your favorite test directory, run TestExcelDemo.ps1<\/b>, watch and listen. It is less than five minutes. Excel 2010 or Excel 2007 is required.<\/p>\n

 <\/p>\n

Robert, I want to thank you for writing a really cool script, and for sharing your Windows PowerShell goodness with us.<\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Guest Blogger Robert Robelo shows how to use Windows PowerShell to work with images in Microsoft Excel.   Microsoft Scripting Guy Ed Wilson here. Our guest blogger today is Robert Robelo. First, I want to give Robert a chance to tell us a little bit about himself. I live in Nicaragua. I have been […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[56,48,49,279,3,45],"class_list":["post-13031","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-guest-blogger","tag-microsoft-excel","tag-office","tag-robert-robelo","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Guest Blogger Robert Robelo shows how to use Windows PowerShell to work with images in Microsoft Excel.   Microsoft Scripting Guy Ed Wilson here. Our guest blogger today is Robert Robelo. First, I want to give Robert a chance to tell us a little bit about himself. I live in Nicaragua. I have been […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/13031","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\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=13031"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/13031\/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=13031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=13031"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=13031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}