none
Powershell script to gather mailbox data and pipe it into Excel - trouble with Excel part and a question. RRS feed

  • Question

  • Greetings,

    I already have a script that gathers all of the necessary data from Exchange (19 attributes per mailbox) and populates them into a Powershell Datatable. That parts works exactly as I need it to, even though I have 18K+ mailboxes (I read somewhere once not to exceed 2K items in a Datatable, but have found no evidence of that statement since so I assume its okay to go bananas with a Datatable).

    My goal is to create individual Excel spreadsheets, one per sub-organizational unit (that is a text string we pull from an AD Attribute) and send those individual Sub-Org spreadsheets out to their respective Sub-Org contacts. I am using a pre-existing Excel template as a source file since it has a lot of formatting in it (and a pre-created PivotTable) and trying to populate it with a sub-set of data from the Datatable.

    Currently I am piping the Datatable to a Group-Object command using the property that identifies the sub-org unit, and creating a hashtable of the results using the -asahashtable command. Much to my surprise this works fine, and it puts each sub-org into it's own hashtable entry, with each corresponding row from the Datatable in that sub-org's value. One of the sub-org's hashtable entries has over 5000 Datatable rows in the Value which honestly surprised me that it would work that way.

    My question - is using a hash table to group the contents of a Datatable in this manner a good idea, or will I eventually hit boundaries/issues trying to stuff say 10,000 rows from a Datatable into the value of a hash table entry? I.E. It seems to be working now, but will I cause the world to implode if my mailbox #s increase, and/or is there a better way to do this short of creating cloned Datatables and populating them individually (which is doable but would be more code since the hashtable creation is already organized and populated in the same single line of code that I am sorting the Datatable with).

    Now on to my Excel issue. I can copy the Excel template file to the appropriate name and location, open it to the correct sheet ("Data"), but I can't seem to get the data from the hash table entry into the Excel. I read online that populating one cell at a time can be VERY slow, and that the better way to do this was to try and populate the data as a whole range. However I can't quite seem to get the rows from the hashed Datatable (called $MailboxData) to load into Excel. Here is the code I am using with a small test of 38 mailboxes, and I am trying to start the population of the "Data" tab on the second row because I already have the header row and just want the data:

    $SubOrgArray = $MailboxData | Group-Object -Property SubOrg  -AsHashTable
    # Loop through each individual SubOrg.
    $SubOrgArray.GetEnumerator() | Foreach {
        $SubOrgName = $_.Name
        $SubOrgFile = $ReportPath + "\" + $Date + "_" + $SubOrgName + ".xlsx"
        Copy-Item $SubOrgTemplate $SubOrgFile -Force
        Write-Host "The count for" $SubOrgName "is" $SubOrgArray.$SubOrgName.Count
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $True
        $ExcelWorkBook = $Excel.Workbooks.Open($SubOrgFile)
        $ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item("Data")
        $Range = "A2:S" + ($SubOrgArray.$SubOrgName.Count + 1)
        # $ExcelWorkSheet.Range($Range).Value2 = $SubOrgArray.$SubOrgName
        $ExcelWorkSheet.Range("A2:S39").Value2 = $SubOrgArray.$SubOrgName
        # $ExcelWorkBook.Saveas($SubOrgFile)
        # $Excel.Quit()
    }

    I commented out the save and quit lines so I can look at the document and see what was done (in this case nothing is done as the example line I have on row #2 only is not overwritten). I also commented out the line where I used $Range in the Excel formula thinking that something was wrong with it, and specified the 38 rows manually with 19 attributes (columns), but that didn't help.

    Excel opens and the "Data" tab comes up, but gets changes (the example row #2 just sits there), and Powershell reports this error:

    Exception setting "Value2": "Exception from HRESULT: 0x800A03EC"
    At C:\scripts\MailboxReport_Test.ps1:181 char:37
    +     $ExcelWorkSheet.Range("A2:S39"). <<<< Value2 = $SubOrgArray.$SubOrgName
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException

    Can someone show me how to get the hashed Datatable rows into Excel as a whole range in order to save time? I.E. The equivalent to a "$Datatable | Export-CSV", but into a specific range of cells in Excel. Again as I understand it writing cell by cell (19 of them) and row by row will be VERY slow not to mention a waste of code if I can do it in the above manner. I could write it row by row I guess, but I would prefer to just dump the whole thing if it is possible, and found an example of someone's array being dumped into Excel as a range in this manner.

    Also if someone could let me know if how I am piping the Datatable into a sorted hashtable is a good idea or not, I would appreciate that too.

    Thursday, November 4, 2010 5:27 PM

Answers

  • I updated the code on poshcode http://poshcode.org/2349 to ensure that all data that goes into the multidimensional array is a string.  Here's a sample of code to use the convertto-multiarray and assign it to a range of cells starting at A1.  Obviously this little bit should be functionalized to allow you to start at another cell than a1:

    $excel = New-Object -ComObject Excel.Application
    $excel.visible = $true
    $workbook = $excel.Workbooks.Add()
    
    ### Magic Line
    $array = (Get-Process |ConvertTo-MultiArray).Value
    
    $starta = [int][char]'a' - 1
    if ($array.GetLength(1) -gt 26) {
      $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
    } else {
      $col = [char]($array.GetLength(1) + $starta)
    }
    
    $range = $workbook.ActiveSheet.Range("a1","$col$($array.GetLength(0)+1)")
    $range.Value2 = $array
    
    To be honest, after all of that.  I think the paste method is much easier to remember and use if you need to do it on the fly.... that is, unless we're developing a nice suite of excel cmdlets finally.


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 9:27 PM
  • Figured it out. This is the line to use:

        $ExcelWorkSheet.PivotTables("BillingReport").PivotFields("SubOrg").ShowDetail = $False

    So now I need to turn that into a loop and process all tabs in a spreadsheet as I want them all collapsed by default.

    Sunday, November 7, 2010 6:24 PM

All replies

  • Regarding your Excel output - The COM object will be way too slow for your purposes.  I would suggest dumping it out to a csv with export-csv.  You can continue to name the csv file per the grouping.  If you really want to modify the xlsx properties you can use the COM object to open the exported CSV, set the properties you need, and then save it as an xlsx file.
    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Thursday, November 4, 2010 7:53 PM
  • Thanks for the follow up and confirmation that the process will be slow. I am trying to make the best of a bad situation as we need to get the data into a spreadsheet that has various views and pivot tables built into it. I.E. I have the spreadsheets ready to go as templates, I just need to populate the data into them. Today we cut and paste the data which is arduous, time consuming, and prone to error. Considering we use these as billing reports, the more automation we build into them the better.

    So if the COM process is too slow to populate Excel from Powershell, is there another way in Powershell to get the data into the pre-existing spreadsheets (that have all the formatting and billing information we need to provide)?

    I found the source of the example for populating a range in one shot:

    http://msmvps.com/blogs/nateoliver/archive/2010/03/02/i-wrote-my-first-powershell-script-today.aspx

    I realize that's an array, and not the rows in a DataTable, but I feel like I am still close. And then I read this:

    http://www.codeproject.com/KB/office/FastExcelExporting.aspx

     Hopefully I am not delusional. :)

    Thursday, November 4, 2010 8:35 PM
  • You hit a topic that I've been doing a lot of research on lately.  I kept thinking if we can just automate data->import from text file we can make it work.  I started playing with opentextfile(), but it can only create a new worksheet.  I found that you can import into a range of cells using a provider and doing a SQL query against your csv.  I stepped away from this to have dinner with my family.  My two-year old daughter picked up a piece of meat and put it in her juice.  For some reason that triggered something in my brain that made me think why not just lift and ____ or copy and paste.

    So here's what I came up with.  If you put tab delimited text into your clipboard you can easily paste the data into any range in excel.  The nice thing about copy/paste is that it keeps the formatting for your xlsx file.  

    You'll need a method to pipe the tab delimited text into your clipboard.  There's two methods that I know work very well.  PSCX has an out-clipboard and Lee Holmes has a set-clipboard script that is in his book, "The PowerShell Cookbook Version 2".  I personally use Lee's method as a function that I have loaded as one of my utility modules called out-clipboard, but you could easily just run create the function prior to using it in your script.

    Here's an example of getting all of your processes, putting them as tab delimited text in your clipboard, opening up an xlsx document, and pasting into a range starting with a5 with as many rows as are entries in the processes.  I think this will work perfectly for you.

     

    $procs = Get-Process 
    $procs |ConvertTo-Csv -Delimiter "`t" -NoTypeInformation |out-clipboard
    $excel = New-Object -ComObject Excel.Application
    $excel.visible = $true
    $workbook = $excel.Workbooks.Open("C:\Dropbox\My Dropbox\scripts\test.xlsx")
    $range = $workbook.ActiveSheet.Range("b5","b$($procs.count + 5)")
    $workbook.ActiveSheet.Paste($range, $false)

     

    Obviously you'll need to tailor this to your needs, but I think the technique is very effective.  It's extremely quick when it gets to the paste function.

     


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 12:35 AM
  • That is a very interesting approach. I had gone down the Datatable hash -> Array -> Excel range because of comments I saw like the one in the second link I posted above where the author noted the approach as 35 times faster in his tests. The poblem was the code in that particular link is it is in either VB.NET or C#, and I am not good enough to convert those to Powershell at this point in time.

    It sure seemed like I should have been able to export the datatable hash into an array, I just wasn't connecting the dots. Do you think it's possible to do in Powershell what the author in the 2nd link did with VB.NET and C#?

    My main concern with piping outside of Powershell to the clipboard is Admins will run this script and I wouldn't want to bump out whatever they had on the clipboard, nor would I want 5000 mailbox lines sitting in the clipboard and they tried to do an RDP based paste when they thought they were pasting something else (and have them be really confused why the URL they thought they were posting was now 5000 mailbox entries).

    In general I am hesitant to load outside extensions that aren't maintained inside the script, just to make this portable down the road w/o tons of instructions of what else to install to make it work (in case I get hit by a bus), so the function in the script route might be the way to go if we end up using the clipboard idea.

    Your comment about formatting of the Excel cells brings up the excellent point of that being one of the main motivators for using a pre-configured and formatted template. For example - If I just drop the Blackberry PIN (one of our fields) into a column in Excel with no formatting rules, Excel messes up some of them thinking it's doing what it should with them when we just want it to be a normal string. Therefore maintaining formatting is very important. I was under the (possible false) impression that the .Value2 Excel data entry method I had in my original script example above would maintain the existing Excell cell formatting because it was just updating the value in the cell and not the whole cell itself.

    I would be curious as to your thoughts on comparing the ingenious clipboard method you came up with to the array -> Excel method others have used in other languages.

    Also if you have any insight into my other question on splitting a datatable into several hashtable entries, where some hash table values had over 5000 objects, and whether that would be a bad thing or if it's perfectly natural - I would really appreciate it.

    Thanks!

    Friday, November 5, 2010 3:19 AM
  • If it's doable in c# it's doable in PowerShell.  It's just a matter of grunting through it sometimes - and what do you know!  It works really nice too! :)

    $excel = New-Object -ComObject Excel.Application
    $excel.visible = $true
    $workbook = $excel.Workbooks.Add()
    $range = $workbook.ActiveSheet.Range("a1","b2")
    $array = New-Object 'object[,]' 2,2
    $array[0,0] = 'Hello'
    $array[0,1] = 'Test'
    $array[1,1] = 'Hello2'
    $array[1,0] = 'hello3'
    $range.Value2 = $array

    Regarding copy/paste - Lee Holmes also has a get-clipboard function on poschode and in the cookbook.  You could read the clipboard into a variable, create your object and export it to tab delimited in the clipboard, paste into Excel, then set the clipboard back from the variable.  If you wind up writing something that writes a lot of text to excel I'd be curious to hear some speed results from both techniques.  I assume that the multi-dimensional array will be quicker, but you never know.


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 3:59 AM
  • I put together a sample of converting an array of PowerShell objects into a multidimensional array so that you can assign it to Value2.  This should be pretty easy to convert into a versatile function at this point, but for now I'm going to bed.

    $dir = dir c:\
    $members = $dir[0] |gm -MemberType Property
    $array = New-Object 'object[,]' ($dir.count + 1),$members.count
    $j = 0
    $members |%{
      $array[0,$j] = $_.name
      $j++
    }
    $i = 1
    $dir |% {
      $item = $_
      $j = 0
      $members | % {
        $array[$i,$j] = $item.($_.name)
        $j++
      }
      $i++
    }
    
    $excel = New-Object -ComObject Excel.Application
    $excel.visible = $true
    $workbook = $excel.Workbooks.Add()
    if ($members.Count -gt 26) {
      $col = [char]([int][math]::Floor($members.Count / 26) + ([int][char]'a' - 1)) + [char](($members.count % 26) + ([int][char]'a' - 1))
    } else {
      $col = [char]($members.count + ([int][char]'a' - 1))
    }
    
    $range = $workbook.ActiveSheet.Range("a1","$col$($dir.count + 1)")
    
    $range.Value2 = $array
    


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 4:46 AM
  • I posted a utility function that will take a collection of PowerShell objects and convert them into a multidimensional array so that you can easily pass it to the value2 parameter of a range.  Next step is to create the automatic function to put the data into Excel.

    http://poshcode.org/2349

    It has full help, but here's a sample of how to use it:

    $range.Value2 = (Get-Process |ConvertTo-MultiArray).Value


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 4:07 PM
  • I updated the code on poshcode http://poshcode.org/2349 to ensure that all data that goes into the multidimensional array is a string.  Here's a sample of code to use the convertto-multiarray and assign it to a range of cells starting at A1.  Obviously this little bit should be functionalized to allow you to start at another cell than a1:

    $excel = New-Object -ComObject Excel.Application
    $excel.visible = $true
    $workbook = $excel.Workbooks.Add()
    
    ### Magic Line
    $array = (Get-Process |ConvertTo-MultiArray).Value
    
    $starta = [int][char]'a' - 1
    if ($array.GetLength(1) -gt 26) {
      $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
    } else {
      $col = [char]($array.GetLength(1) + $starta)
    }
    
    $range = $workbook.ActiveSheet.Range("a1","$col$($array.GetLength(0)+1)")
    $range.Value2 = $array
    
    To be honest, after all of that.  I think the paste method is much easier to remember and use if you need to do it on the fly.... that is, unless we're developing a nice suite of excel cmdlets finally.


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Friday, November 5, 2010 9:27 PM
  • You are really awesome for following up multiple times in detail like you have.

    I am taking direction from the code examples you have provided and customizing them for my own needs. When I am done I will repost them here as the final product.

    I would like to make one suggestion - if you are showing a neophyte like me an example piece of code for something like making a function out of an array, using meaningful variable names in your code would be extremely helpful. Specifically the $i and $j are hard to understand while looking in the middle of the code. You did put in the latest version online a description of what $j and $i were, but even so $ArrayRow and $ArrayColumn would be much more meaningful to a person like me who is learning hard and fast. This is just a suggestion and is in no way meant to disrespect you or the work you have put into helping me. :)

    I hope to be able to post the final code soon. I have it working, I just want to clean it up and annotate it some.

     

    Saturday, November 6, 2010 3:07 PM
  • You don't happend to know a way to get all pivot tables on all the worksheets to collapse their rows do you?

    In other words I would like all pivot tables to be "minimized" to their first "+" characterd. Something akin to the $ExcelWorkBook.RefreshAll(), but like a $ExcelWorkBook.CollapseAll().

    I tried $ExcelWorkBook.Outline.Showlevels = 1, and $ExcelWorkSheet.Outline.Showlevels = 1, but neither one works in powershell. Ideally I would collapse EVERY pivot tabe on every sheet to it's base/default levels, but currently they seem to expand out after I populate the Data tab and do a RefreshAll. I even tried saving the template after expanding all rows in each pivottable, and then collapsing all rows in a each pivot table with no luck.

    I am really stuck in regards to what commands are supported for a worksheet or workbook as I I have tried various syntax on the internet to no avail, and therefore it seems we are left in the dark in regards to what functions we can execute in a workbook com object. If someone has a cheatsheet in regards to what we can "." after defininging a new Excel Com Object, I would be most grateful in learning that part.

    Thanks!

    Saturday, November 6, 2010 5:26 PM
  • No problem.  It's not all selfless.  I used the research and work I did for this post as subjects for two articles I posted on my blog:

    http://powertoe.wordpress.com/2010/11/04/how-to-get-data-into-an-excel-spreadsheet-very-quickly-with-powershell/

    http://powertoe.wordpress.com/2010/11/05/how-to-get-data-into-an-excel-spreadsheet-very-quickly-with-powershell-contd/

    You know, I'm usually the one complaining about variable names on this forum ;)  It's old habit for me to use i and j for my variable names when referring to integer increments - especially when they're used in arrays.  That is exactly why I spelled it out in the comments though.  Unfortunatley, I don't think I'm going to change that.  Funny thing is that when I was learning Perl I had a coworker who used to use $a $b $c $d in order for every variable.  His code was horrid to try and work through.  I wound up doing a lot of ReplaceAll on his code to put my own variable names in place of his.  I'm surprised that you didn't complain about the % instead of using the foreach keyword.  I hope that means that it's starting to be acceptable to start using % and ? shortcuts with beginners.  Enough digression.

    The COM objects are decently documented, but it can be intimidating to figure them out because there are so many methods, properties, and objects with very little spelled out for you.  http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel(VS.80).aspx

    I'm not sure how new to PowerShell you are, but one of the coolest things is that you can use get-member on any object to inspect what the object contains.  For example:

    $workbook.ActiveSheet |gm #short for Get-Member

    The above will show you that there is a pivottables() method.  Unfortunately, when using COM objects in PowerShell you don't always get all the info you need so you need to resort to the documentation.  As you can see, most of the methods returned from the above command require variants, but no info on the data types.  At least you know they exist and you can look them up on the msdn documentation.  (Try it with the native PowerShell and .Net objects and you'll see you rarely have to go to the documentation - actually, there are some devs out there who use PowerShell just for it's ability to inspect .NET so easily)

    Unfortunately, I don't know enough about pivot tables in Excel to figure out what you're trying to do.  I can create simple tables, but I don't have +- in them.  You have three options.  You can try to figure it out with the above clues, wait for someone else on the forum to respond, or if you can spell out how to create a simple pivot table that will have the +-. I would love to take a swing at it.  


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Saturday, November 6, 2010 10:53 PM
  • I'm glad you didn't take my suggestions as negative feedback. We all have our own styles and I remember back in the day my Windows command scripts use %%i always for whatever reason in my FOR loops. I.E. I get what you mean about not stopping using $i and $j. :)

    That documentation was very helpful, it would have same me countless hours of searching to find the RefreshAll that I eventually found online. I can't seem to drill down into what I can do with Rows or Columns, other than to see they are members of the Worksheet object, but I will figure it out eventually.

    I am not new to scripting or programming (I cut my teeth on Pascal back in the day), so for the most part Powershell makes sense to me, it's getting to know the nuances of CMDLets and also external connections like tapping into COM object. I have seen people reference using the Get-Member, but I haven't had a reason to us it up until this point, so now hopefully this exercise will get me into the habit of doing so.

    Meanwhile PivotTables are probably the coolest thing in Excel I have seen in a long time (because it's relatively new to me and instantly solved a "how do I" problem for my team). You can take a list of data with columns for "attributes" and rows for user entries, and turn them into meaningful summaries. Here is a decent online tutorial:

    http://www.timeatlas.com/5_Minute_Tips/Chunkers/Learn_to_use_Pivot_Tables_in_Excel_2007_to_Organize_Data

    In my scenario I have a workbook I generate for each Sub-Org with three sheets. The first sheet has a PivotTable with the overall summary of the data, the second sheet has a PivotTable with a specific view of the data, and the third sheet is the raw data itself. Essentially when I populate the data into the 3rd "Data" sheet, I have to refresh the PivotTables to get them to reflect the new data. The Workbook.Refreshall() works great for this, except it seems as a by-product all the sorted rows (like in the 3rd picture in the link) are expanded. We want to collapse them all like we can do in the GUI with the collapse all command.

    I also have a Master spreadsheet with the above 3 sheets (with the first sheet being a summary of all of my sub-orgs put together, plus an additional sheet for each sub-org. This is my team's global view.

    It takes minutes to create a PivotTable in Excel, so I am hopeful you will have the time to mimic what's in the linked demo and maybe connect the dots for me on how to collapse all the expanded rows in a (preferably all) PivotTables.

    I will post my code in its entirety when its done to share with the community since I haven't seen any scripts out there that gather mailbox data and create reports on that data to the magnitude of what we are doing.

    Sunday, November 7, 2010 11:42 AM
  • Gah... I feel like I am so close.

    Using the Get-Memeber funciton on a specific Pivottable, I see a bunch of property and methods. One in particular caught my eye which was RowAxisLayout. I thought if I set this to a compact value, it would collapse all the rows in the table for me. No such luck. Here is the command I tried:

        $ExcelWorkSheet.Pivottables("BillingReport").RowAxisLayout.xlCompactRow

    I then tried recording a Macro in Excel which looked promising as if gave me this code:

    ActiveSheet.PivotTables("BillingReport").PivotFields("SubOrg").ShowDetail = _

    So I tried to put it into the two following syntax formats with no luck:

        $ExcelWorkSheet.PivotTables("BillingReport").PivotFields("SubOrg").ShowDetail = _

        $ExcelWorkSheet.PivotTables("BillingReport").PivotFields("SubOrg").ShowDetail = "_"

    Still looking.

    Sunday, November 7, 2010 12:53 PM
  • Figured it out. This is the line to use:

        $ExcelWorkSheet.PivotTables("BillingReport").PivotFields("SubOrg").ShowDetail = $False

    So now I need to turn that into a loop and process all tabs in a spreadsheet as I want them all collapsed by default.

    Sunday, November 7, 2010 6:24 PM
  • Hey cool stuff.  I figured they (Excel Pivot Tables) were drill down reports of some kind, but for some reason I always assumed you needed external data sources.  Duh - Excel is the simplest of data sources of course you can create Pivot tables of the data inside of it.  I have some large csv datasets I'm going to have to play with this week now.

    Regarding Get-Member isn't it awesome!  No other language I know holds a candle to the ability to do self discovery like this.  Keep at it, and forget COM, you'll know the .NET classes inside and out soon enough.  I guarantee you will use get-member every day you code in PowerShell now :)  Wait 'til you start playing with breakpoints in powershell_ise.exe.  You can inspect variables and objects, and test snippits of code inside of loops or wherever else you might need to.  It's really helpful to pass $_ to get-member in the middle of a loop sometimes.  You can verify that things are what you expect them to be.  Really powerful stuff.

    Based on your post I can tell you that PowerShell will be very easy for you to not only pick up, but master very quickly.  Best of luck in your quest here.  At least you know there's a good place to turn to when you get stuck - that is if you don't mind bad variable names ;)

     


    http://twitter.com/toenuff
    write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""
    Monday, November 8, 2010 2:46 AM
  • FYI I finally posted the script I put this code into - which is used to generate a monthly mailbox billing report via Excel:

    http://gallery.technet.microsoft.com/scriptcenter/Exchange-Mailbox-Billing-a33bcbec

    Friday, February 3, 2012 9:38 PM