Saturday, March 31, 2012

Excel Part 1

The core MS Office apps have their application and inner objects exposed via COM.  These COM interfaces have distributable .NET Interop assemblies available to download.

There are two basic ways to interact with Excel via the COM objects and via the interop assembly.  Functionally I think the COM will allow you to accomplish the same tasks, but it will not be as easy.  To load the interop you will need:

001
002
003
004
005
006
007
#Load the Excel Assembly, Locally or from GAC
try {
    Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel"  | out-null
}catch {
#If the assembly can't be found this will load the most recent version in the GAC
    [Reflection.Assembly]::LoadWithPartialname("Microsoft.Office.Interop.Excel"| out-null
}

I tend to distribute the inerop DLL in my network share so I don't have to make sure that all servers and workstations have it installed.  The above should take care of either loading a local assembly or looking in the GAC.

To access Excel data, you have to be aware of the hierarchy of things.  At the top is the application class that contains one or more workbooks that contain one or more worksheets.  Within the worksheet are ranges.  Each layer can access down to some of the other layers.

001
002
003
004
005
006
007
008
009
Function Open-ExcelApplication {
Param([switch] $Visible,[switch] $HideAlerts)
    $app = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
    $app.Visible  = $Visible
    $app.DisplayAlerts = -not $HideAlerts
    return $app
}
$app = open-excelApplication -Visible
$app | gm active*

Yields:

Name                      MemberType Definition                                              
----                      ---------- ----------                                              
ActiveCell                Property   Microsoft.Office.Interop.Excel.Range ActiveCell {get;}  
ActiveChart               Property   Microsoft.Office.Interop.Excel.Chart ActiveChart {get;}  
ActiveDialog              Property   Microsoft.Office.Interop.Excel.DialogSheet ActiveDialog ...
ActiveEncryptionSession   Property   int ActiveEncryptionSession {get;}                      
ActiveMenuBar             Property   Microsoft.Office.Interop.Excel.MenuBar ActiveMenuBar {get;}
ActivePrinter             Property   string ActivePrinter {get;set;}                          
ActiveProtectedViewWindow Property   Microsoft.Office.Interop.Excel.ProtectedViewWindow Activ...
ActiveSheet               Property   System.Object ActiveSheet {get;}                        
ActiveWindow              Property   Microsoft.Office.Interop.Excel.Window ActiveWindow {get;}
ActiveWorkbook            Property   Microsoft.Office.Interop.Excel.Workbook ActiveWorkbook {...


All of the classes also have a .Application property that points back to the top.


001
002
003
004
005
006
007
008
009
010
011
012
013
function New-ExcelWorkBook {
Param([parameter(ValueFromPipeline=$true)] $ExcelApplication
,[switch] $Visible)
process {
if ($ExcelApplication -eq $null ) { 
$ExcelApplication  = Open-ExcelApplication -Visible:$Visible
}
$WorkBook = $ExcelApplication.WorkBooks.Add()
return $WorkBook
}
}
$book = $app |  New-ExcelWorkBook
$book | gm active*

Yields:

   TypeName: System.__ComObject#{000208da-0000-0000-c000-000000000046}

Name         MemberType Definition                  
----         ---------- ----------                  
ActiveChart  Property   Chart ActiveChart () {get}  
ActiveSheet  Property   IDispatch ActiveSheet () {get}
ActiveSlicer Property   Slicer ActiveSlicer () {get}

Alternately, you can open an existing workbook:
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
function Get-ExcelWorkBook {
Param([parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject
,[switch] $Visible
,[switch] $readonly)
[Microsoft.Office.Interop.Excel.ApplicationClass] $app = $null
if ($inputObject -is [Microsoft.Office.Interop.Excel.ApplicationClass]) {
  $app = $inputObject
  $WorkBook = $app.ActiveWorkbook
else {
  $app = Open-ExcelApplication -Visible:$Visible 
  try {
    if ($inputObject.Contains("\\"-or $inputObject.Contains("//")) {
      $WorkBook = $app.Workbooks.Open($inputObject,$true,[System.Boolean]$readonly)
    } else {
      $WorkBook = $app.Workbooks.Open((Resolve-path $inputObject),$true,[System.Boolean]$readonly)
  }} catch {$WorkBook = $app.Workbooks.Open((Resolve-path $inputObject),$true,[System.Boolean]$readonly)}
}
#todo: Add Switch to toggle Full Rebuild (this does an update data)
$app.CalculateFullRebuild()
return $WorkBook
}


The Interop allows you easy access to the classes and enumerations.  The largest caveat is what you may expect  vs what you get when you look at the COM collections.  These collections are built implementing default properties that do not come across in powershell.  A recorded macro may reference WorkSheets("Sheet1") but in PS you will need to say $WorkSheets.item("Sheet1").  So, what looks like it may be an array may need a call to the item property to do what you expect.

When you look at Excel you see cells, when you automate it you have ranges.

001
002
$Sheet = $Book.Worksheets.item("Sheet1")
$sheet | gm -MemberType *Property | where { $_.Definition -match "Range" }

Yields:

  TypeName: System.__ComObject#{000208d8-0000-0000-c000-000000000046}

 Name              MemberType            Definition                        
 ----              ----------            ----------                        
 Range             ParameterizedProperty Range Range (Variant, Variant) {get}
 Cells               Property              Range Cells () {get}              
 CircularReference Property              Range CircularReference () {get}  
 Columns         Property              Range Columns () {get}            
 Rows              Property              Range Rows () {get}                
 UsedRange     Property              Range UsedRange () {get}


 All of the following are the same:

001
002
003
004
005
006
007
$sheet.Range("A1").Text
$sheet.Range("A1:A1").Text
$sheet.Range("A1","A1").Text
$sheet.cells.Item(1,1).text
$sheet.Columns.Item(1).Rows.Item(1).Text
$sheet.Rows.Item(1).Columns.Item(1).Text
$sheet.UsedRange.Range("a1").Text


If you were going to use a formula in a cell, this same convention is used for the .Range ParameterizedProperty.  Cells you have to use the .Item property but you can more easily use in loops as the cell is a coordinate.  UsedRange is limited to the cells that have or have had data in them as a block.  So furthest X and furthest Y make up the range.  If you want to know what these bounds are:

001
002
$sheet.UsedRange.Columns.Count
$sheet.UsedRange.Rows.Count


To close it all down you can do something like:
001
002
003
004
005
006
007
008
009
010
011
012
Function Close-ExcelApplication {
Param([parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject)
if ($inputObject -is [Microsoft.Office.Interop.Excel.ApplicationClass]) {
$app = $inputObject 
else {
$app = $inputObject.Application
Release-Ref $inputObject
}
$app.ActiveWorkBook.Close($false| Out-Null
$app.Quit() | Out-Null
Release-Ref $app
}

This will work fine if you only have one workbook open with changes.  If you have more than one open you will need to make sure that you close or save each sheet.  If you try to close and have more than the active workbook not saved, excel will prompt you to save.  This is not something that you want if you expect your script to run unattended.

Another big point to consider is the garbage collection.  I know it was a big concern with Office 2003 and may be unneeded in 2007 or 2010, but an extra step to clean up your variables should be used.

001
002
003
004
005
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref-gt 0 | Out-Null
[System.GC]::Collect() | Out-Null
[System.GC]::WaitForPendingFinalizers() | Out-Null
}

If you don't do this, Excel may (may)  fail to close.


More later...

Wednesday, March 28, 2012

7z SFX without the temp Config File

I prefer to not use temp files unless I absolutely need to so I replaced

001
002
003
$SetupConfig | out-File "$path\$ConfigName" -enc UTF8
get-content $SFX,$ConfigName,$ZipName -Enc Byte -Read 512 | set-content $OutName -Enc Byte

with:

001
002
003
004
005
$SFXBytes = [io.file]::ReadAllBytes("$PAth\$SFX")
$ConfigBytes = [text.encoding]::ascii.GetBytes($SetupConfig)
$ZipBytes = [io.file]::ReadAllBytes("$PAth\$ZipName")
($SFXBytes,$ConfigBytes,$ZipBytes| set-content $OutName -Enc Byte

I would like to have 7z build the zip to STDOUT but I don't think I can have it go to the 7z format.  Will continue to look at it.

This was an update to http://import-powershell.blogspot.com/2012/03/powershell-7zip-sfx-installer.html

PowerShell 7zip SFX Installer


  1. Save the below two scripts 
  2. Download 7zsd_All_x64.sfx
  3. 7z the 3 files together as 7z.7z, or at a minimum zip Setup.ps1 as 7z.7z
  4. Run 7zInstaller.ps1
  5. It will build a New.EXE then run the EXE.
  6. This will extract the files to a temp folder, and call a powerShell prompt that will run the Setup.PS1 and leave the prompt open.

Modify the Setup.ps1, remove the -NoExit argument, and you can use the script to move the files from the temp location to anywhere you need it.  When the powershell script finishes, the temp folder should be removed.

You can also specify an "InstallPath="path_to_extract"" in the SetupConfig block.  If you do this you don't have to manually copy the files and 7z will not delete the files.

Take a look at http://7zsfx.info/en/parameters.html it has a good collection of what options can be used in the SetupConfig block.  Of note, you can use environment variables, so you could extract directly to a user profile if you wanted to.

7zInstaller.ps1

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041

# Script will not create the 7z file.
$ZipName= "7z.7z"
# Output SFX EXE
$OutName= "new.exe"

# This config data tells the SFX how you want to do things.
# Other than the initial popup, we push all the other options into the PS1.
# http://7zsfx.info/en/parameters.html
$SetupConfig = @"
;!@Install@!UTF-8!
Title="Title Spot"
BeginPrompt="Do you want to install now?"
RunProgram="powershell -NoExit -noprofile -executionpolicy unrestricted -file %%T\\Setup.PS1"
;!@InstallEnd@!
"@


# get from http://7zsfx.info/en/ this was pulled from the "7zSD extra" option.
# There are a selection of these that may display a console vs winforms UI, and
# may also switch between 32bit and 64bit extraction.
$SFX="7zsd_All_x64.sfx" #"7z.SFX"


# this name can be whatever. It will overwrite each time.
$ConfigName= "SetupConfig.txt"

# Need to Move PWD to scripting folder (This simplifies the below, everthing should work with full paths)
$path = $script:MyInvocation.MyCommand.Path
cd $path

$SetupConfig | out-File "$(Split-Path $path)\$ConfigName" -enc UTF8


# The first sample on Binary merges used ADD-Content. This is why I had to kill the file.
# Set-Content though in the same pipeline will append all of the data so we are good.
# The test-path and cleanup is just something nice to keep around.
if (Test-Path $OutName) { del $OutName }
get-content $SFX,$ConfigName,$ZipName -Enc Byte -Read 512 | set-content $OutName -Enc Byte

#Test the installer
Start-process $OutName


Setup.ps1:

001
002
003
Write-Host "Installer Script"
$script:MyInvocation.MyCommand | fl
ls env:7z*Folder*


Posted an update @ http://import-powershell.blogspot.com/2012/03/7z-sfx-without-temp-config-file.html


Wednesday, March 21, 2012

Prize Drawing

We did a simple script with the attendance list piped to a Get-Random to pull winners for the SIG last week.
The problem was for multiple prizes the list didn't get smaller.

PS Script to HTML for Blog


I needed something other than Blogger's QUOTE for script.

Try 1:
If (-not (Test-Path "$home\Documents1\WindowsPowerShell\Highlight-Syntax 2.0.ps1"))
{
    throw "You Need to get `'Highlight-Syntax 2.0.ps1`' from http://poshcode.org/1498`n`n"
}

$psISE.CurrentPowerShellTab.AddOnsMenu.SubMenus.Add("Out-Highlight",{
    if ($psise.CurrentFile.Editor.SelectedText) {
        $source  = $psise.CurrentFile.Editor.SelectedText
    } else {
        $source  =$psise.CurrentFile.Editor.Text
    }
    (& "$home\Documents\WindowsPowerShell\Highlight-Syntax 2.0.ps1" $source $true) |
          Out-Clipboard
},$null) | out-null


Now this worked, then I kept reading and found a Lee Holmes example that was much further along.

Try 2:
Get script from http://www.leeholmes.com/blog/2009/02/03/more-powershell-syntax-highlighting/ I had to fix some single quotes in the $tokenColours definition from copying off the site and the download wasn't clean either.

Around line 203 you find:

001
002
003
004
005
006
if (-not $psise.CurrentOpenedFile)
{
    Write-Error 'No script is available for copying.�
    return
}     
$text = $psise.CurrentOpenedFile.Editor.Text


From the try one modify this to:

001
002
003
004
005
006
007
008
009
010
if (-not $psise.CurrentFile)
{
    Write-Error 'No script is available for copying.�
    return
}
if ($psise.CurrentFile.Editor.SelectedText) {
    $text = $psise.CurrentFile.Editor.SelectedText
else {
    $text = $psise.CurrentFile.Editor.Text
}

...so I can pull a selection.

Now, I can copy with color to WordPad, but still not to MSWord, or the iframe that Blogger uses to compose in.  In Word I get the plain text and in Blogger I get nothing.

With:

001
[System.Windows.Clipboard]::GetDataObject().GetFormats()

The Script produces:

HTML Format
UnicodeText
System.String
Rich Text Format
and copy from Blogger produces:
Text
UnicodeText
System.String
HTML Format
Locale
OEMText
So I added the following.

001
002
$dataObject.SetText([string]$text, [Windows.TextDataFormat]::Text)
$dataObject.SetData([System.Windows.DataFormats]::OemText,[object]$text)

Still no go!

Local is a byte[4].  I assume it is EN_US or something similar to show localization.

I did a copy from each method then ran:

001
[System.Windows.Clipboard]::GetText([System.Windows.TextDataFormat]::Html)


There is some variation in building the "CF_HTML" header.  I think part of the problem may be formatting issues from making a copy of the script off the HTML, with converted quotes.

Reading this code and the associated blog, I understand how the HTML works much better now, but I also learned that PSCX Set-Clipboard has arguments to pupulate the HTML and RTF portions of the clipboard.  So that really solves my problem.  Unfortunately the solution is ignoring the problem.  But I don't see a need to reinvent the wheel.  The down side is that Set-Clipboard doesn't let you stack clipboard formats.  So I can specify RTF, Text, or HTML.  a bummer really.

Called Via an ISE Menu:

001
002
003
$psISE.CurrentPowerShellTab.AddOnsMenu.SubMenus.Add("Copy Color",{
    (& "$home\Documents\WindowsPowerShell\Set-ClipboardScript.ps1")
},$null| out-null

Thursday, March 15, 2012

PowerShell for Rapid Prototyping

I do a large amount of work integrating with third party and internal COM and .NET components.  Lately I am doing this just in PS but I still have a heavy need for compiled applications to manage complex production interactive process.

When evaluating or exploring the features of a component I used to do a basic win forms app, add a breakpoint then use a combination of watch variables and the object explorer.  If it comes down to it I may load the assembly into ILSpy by Sharp Code, or the original reflector when it was owned by Lutz.

The variable: psdrive along with Get-Member allow me to explore things very easily.  If you add-type use the passthrou argument and save it to a variable.  This will show you what base classes are made available.

I posted a question on TechNet (http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/494f64ea-7173-44a6-835d-31814d8e3bbf) about pulling out the list of types that can be used from an assembly.


function Get-Type
{
    Param([Regex]$Pattern = '.')
    $ErrorActionPreference = 'SilentlyContinue'
    [AppDomain]::CurrentDomain.GetAssemblies() |
         ForEach-Object { $_.GetExportedTypes() } |
            Where-Object {($_ -match $Pattern) -and $_.IsPublic} |
                Sort-Object FullName
}

#Find All Loaded Soap related Classes
Get-Type '(?i-)soap'

The Regex is case sensitive by default, so the example has a option modifier in the front.



Arrays and Ranges

Not an overly insightful item, but I thought it worth sharing.

One of the guys here was working on managing Virtual Machine lifespans for Engineering and QA.

One of the concerns was that people not keep a single VM around for any real length of time.  So he was calculating the age of the VM in days.  As each VM hit a specific age an email will be generated to remind them of the short life policy.

He was  calculating an [integer] age from a time stamp then using
[integer]Get-Age($VM) -Match "90|120|180" 
to ID for an email.  I pointed out that casting to a string for the Regex was inefficient and that after 180 days people would never be reminded again.  Now the batch will run daily, so we don't need to worry about weekend gaps.  I realize the inefficiency of casting from integer to string is not really measurable for a small number of objects, but.....

Off my head I suggested using a range for the dates
(90,120,180) -contains [integer]Get-Age($VM) 

Then a thought and one or two "Not the right way to do it" later, I had:
((90,120) + (180..365)) -Contains [integer]Get-Age($VM) 

That way they get a daily email for a year, once they breach 6 months.

Sunday, March 11, 2012

Dreams

I told my boss that I have nightmares about work.  They are really about software development.  And they aren't scary the just get repetitive.  And they will come back even if I wake up.

So last night I was dreaming about refactoring code.  You know the "it works but would be better if it was different."  Now the catch was that I wasn't just refactoring code.  I was looking at code that was represented by a some form of '90s tile based RPG.  Loops were little jagged pixels on a tile.  Nested loops were easy to see because the tile was just to busy.

Things were just very abstract.  I would change one tile or set of tiles and others would shift.

One thing I like about power shell is the ability to update a function or run a selected block. And just keep changing minor things until I get it working the way it works.

But this is a bad thing to dream about.  Every time you fix something, you just find more things to fix.   This may not be too different from reality, but it can be haunting.

Friday, March 9, 2012

A Quick Download Assist

We bought the "I Love Lucy" complete episode collection.  Included with the TV episodes are some radio broadcasts of "My Favorite Husband" where Lucille Ball has a very similar role to the Lucy character that she later played (the link in the script will explain if you are interested).  The broadcast are now in public domain. My wife and I are fans of radio shows in general and she asked that I find these.  From a Google search and  also via Wikipedia I found a few collections of these shows @ Archive.Org.  The problem was that I could down load one massive MP3 or manually download 109 files.  I tried a quick and dirty. Click Click Click...  But only had about 30 or so made it down due to the simultaneous download limits in IE.

So I pull up my PowerShell 3 console and started hacking:

$clnt = new-object System.Net.WebClient
$URL = "http://www.archive.org/details/MyFavoriteHusband_866"
$path = "$home\Downloads\MFH\"
$t = $clnt.Downloadstring($url)
$regex = [regex] '"([^"]*[.]mp3)">'
$fileList = $regex.Matches($t)
$URI = [system.URI] $URL
Clear
$i = 0
$fileList | % { $_.Groups[1].Value } | %{
      $URI2 = new-object "System.URI" -argument $URI,$_
      $DownLoadName = $Path + $URI2.Segments[-1]; $DownLoadName
      $i++;$i
     $clnt.DownloadFile($URI2.AbsoluteUri,$DownLoadName)
}
The website returns download links relative to the domain as /Download/....  fortunately the URI class is built for this.  If you New-Object a URI and pass it an old URI and a relative URL, then the returned URI is a fully qualified version of the absolute URL.  URI also has a Segments property that splits the URL by it's parts so I can directly access the Download name.

The site contained 2 links for every MP3, so I needed to make the Regex pick up only one of them.  This meant that I had to grab extra data.  This made my For-Each a bit more complicated. as Each Match Looked something like (The quotes were in the result):
"/download/MyFavoriteHusband_866/Mfh1951-03-24124IrisLizsEaster.mp3">

Had I my trusty cheat sheet, I would have done the Regex as:
$regex = [regex] '(?<=")([^"]*[.]mp3)(?=">)'
The changes are "Zero-width positive lookbehind assertion" and "Zero-width positive lookahead assertion"s In short they say look for, but don't return as part of the match. http://msdn.microsoft.com/en-us/library/bs2twtah(v=vs.85).aspx

Then I could have done my For-Each as

$fileList | %{          
            $URI2 = new-object "System.URI" -argument $URI,$_.Value
It is amazing,but not surprising, how much a wrong Regex may work, but make everything more complicated that follows.


To make sure I could "See it work" I added in an $i to index and the name of the file coming down.  Yes, I should have made this a progress bar.  Maybe later!

- Josh
-----------
Update: Fixed Typos
Update2: Added note on the Regex Change

Wednesday, March 7, 2012

Add-Type issue with multi-assembly project

I have a project that loads plugins from a folder.  The assemblies are each loaded and parsed to catalog the methods in that assembly that contain a specific attribute.  This was easier than using a type check because it allowed me to use inheritance for my plug-ins as well as allow them to call others from the same assembly without exposing those publicly.

At any rate, my base class for these plug-in was building to a BIN\DEBUG in its own source tree.  Many of the other class were building to a common Bin\Debug.  In doing this they would copy the base assembly over.

When running in the application UI, the base class was loaded directly.  The other assemblies all resolved and used this reference.  When launching via PowerShell even after an Add-Type for the base class these assemblies could not be loaded.  They would throw System.Reflection.ReflectionTypeLoadException" Unable to load one or more of the requested types" and suggest that details be pulled from "LoaderExceptions
".

Changing the build path for the base class assembly to the same as the other and having the other refrences to this location seems to have resolved the issue.



Tuesday, March 6, 2012

When to code and when to script?

When I describe PowerShell to someone new, I tend to refer to it as a glue language.  It is very good for tying different types of technology together.  In practical use, I have found that it is very beneficial for describing custom business logic to a problem.

Now it is true that in PowerShell you can embed C#, VB, F# and the like.  You are sitting in .NET land so you can reasonably do anything in PowerShell that you can do in C#.  The question is; where do you draw the line?  Fortunately PowerShell has many shades of color between your Visual Studio work, and your ISE work. Picture the rainbow.

What we really need to consider, is the proper choice between the following.
1.     Pure C#.
2.     C# with PowerShell Classes implemented (http://msdn.microsoft.com/en-us/library/windows/desktop/ee706563(v=vs.85).aspx)
3.     PowerShell with a compiled Module
4.     PowerShell Module written in PowerShell calling C#
5.     Script with C#
6.     PowerShell Module written in PowerShell with no add-type definitions.
7.     Pure PowerShell

What attribute of our processes do we want to consider?
1.     Platform (Web based , win forms, WPF, console)
2.     Complexity
3.     What do you know how to do?
4.     How are you going to deploy it?
5.     Do you want to own this process forever?
6.     Do you centrally maintain source code?
7.     How much of this process is potentially going to change.

WMF 3.0 has the ability to make a web service in PowerShell, but if you want a rich web based UI, do it in visual studio.  If you want something else you can probably do it in PowerShell with a sliding scale of things getting crazy.  

Complexity is probably the most important item to consider and potentially the most difficult to determine.  Sometimes very simple things can be subject to scope creep (http://en.wikipedia.org/wiki/Scope_creep).  If you are going to use something that is best implemented via object oriented abstractions, then you may want to consider doing your classes in Visual Studio.

What do you know how to do? How much do you know about how things work, vs. what you need to get done?  These are two simple questions, but they should still be considered.  If you want to have a dialog box for user import, this can probably be done easily enough in PowerShell using ShowUI (http://showui.codeplex.com/) or directly with the Windows.Forms classes.  Now if you don't know anything about these, download VS2010 Express, select a library assembly, draw your form, and mark your UI elements as public.  It is a bunch of extra steps, but using a UI to wire up the GUI can make a big difference in headaches and subtle bugs.   

Remember, if you need to, you can just save the .CS file and pull it in via "Add-Type -TypeDefinition".  VS could be used just to author the form.  Keep in mind that there is a designer partial class that will need to be loaded also, just append the two files before your Add-Type.  I recommend that if you are importing more than a few lines of code via a TypeDefinition, that you keep this in an external file.  The PowerShell ISE won't syntax check your C#, and won't be much help in debugging either.

Does deployment matter?  If I am going to do automation for another team in the office, it is very crucial that they have access to everything needed to maintain the item.  It may be years from now, but a random EXE sitting in accounting is going to get thrown away if things change.  If it is implemented in a script, they have everything.  They have my name in comments and the entire source to change or pass to someone else.  True you could zip any source and pass it around with the EXE, or embed it as a resource with an option to extract it... but that gets a little convoluted.  It also requires that someone have Visual Studio or some variant.  Ownership is closely paired with this.  A script, if it can be done cleanly can much more easily be moved to another process owner if there is no corporate process for managing things like this.  If you do centrally manage your source code across anyone that may use your automation, then some of this doesn't apply.

The second largest question is looking at how much of what you are going to do will change.  Sure, if you have 5 lines of script and it never changes, then this is a bit mote. But if you are powering Excel to graph frequencies of log entries in a pivot table, and someone asks why you used “THAT” font.  A tweak in a script is easier than an application recompile.

I need to think about these attributes some.  Then I will look at trying to build some better ideas about how to choose between the options.

Friday, March 2, 2012

Debugging my .NET assembly after New-Object

Don't judge me.  I have to work with Access Databases, this forces me to to built 32bit assemblies.  To automate my applications I have to use 32bit PowerShell.  I also for some arbitrary reason do most of my work .NET 4.0.

So I go to: C:\Windows\syswow64\Windowspowershell\v1.0\
I created a new "powershell.exe.config" containing:


<?xml version="1.0"?>
<configuration>
    <startup useLegacyV2RuntimeActivationPolicy="true">
          <supportedRuntime version="v4.0.30319" />
    </startup>
</configuration> 
The same can be done to create a "powershell_ise.exe.config"

Then fired up my 32bit .NET 4.0 powershell.
Changed directory to my build folder.  And called an Add-Type.
Of note: if you do this, and need to rebuild the app, you will need to close powershell and reopen.  The Add-Type loads the assembly, so it is locked from being written by the compiler.
In Visual  Studio, I loaded my solution, went to Debug/Attach to process, and found the 32bit Powershell instance.  I was concerned that I wouldn't be able to tell it apart from my 64bit Powershell instance, so I have processExlorer up and used the handy "Find Window's Process (drag over window)".  When I found the process Visual Studio did identify it as 32-bit.

I set a break point in my class constructor, did a New-Object, and that pretty yellow step threw highlight showed up in Visual Studio.

- Josh