About Me

My photo

I'm a developer in SSIS ETL, C#, ASP.NET, and PowerShell and reside in Phoenix, Arizona. Professional classical guitarist, Renaissance lutenist.

Saturday, July 30, 2011

PowerShell/Excel Automation with ACE drivers

This is to document my experience using the ACE drivers to automate generating a report from a multisheeted Excel spreadsheet.
The spreadsheet was created by someone else, and the worksheet names included a hyphen, for example, "Project A - Client1". I experienced PowerShell exception errors when I tried to list the data, with the error message stating the punctuation is not allowed in the TABLE_NAME (which is the worksheet name) property, even though it's not a problem for Excel itself.
I had to find a way to change the worksheet names programmatically because renaming the Excel sheet by right clicking the tab did not change the name in the TABLE_NAME property itself for some reason, even if I closed the spreadsheet file and powershell session and reopened them.

Sunday, July 24, 2011

A PowerShell Script to deploy/share/update a profile.ps1 and module

I hope this script is helpful to anyone who needs to share or deploy a customized PowerShell profile script and module.

Install DF_Deploy module file to $pshome and profile.ps1 in the "My Documents\WindowsPowerShell" folder.

Creates a WindowsPowerShell folder under the current user's My Documents folder,
copies profile.ps1 to that folder, creates a module folder under $pshome,
copies module files to it.

Have a copy of the profile and module files in the same folder as this script.

$mydocs = [system.Environment]::GetFolderPath("MyDocuments")
$UserPoshHome = Join-Path $mydocs "WindowsPowerShell"
$UserModulesPath = Join-Path $userPoshHome "Modules\DF_Deploy"
if (!(Test-Path($UserPoshHome))) {
md $UserPoshHome # -WhatIf
Write-Host "Created $userPoshHome"
else {

Write-Host "$userposhhome exists already.`n"

if (!(Test-Path($UserModulesPath))) {
md $UserModulesPath # -WhatIf
Write-Host "Created $UserModulesPath`n"

if (!(Test-Path("C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy"))) {
md "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\DF_Deploy" # -WhatIf
Write-Host "Created C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy`n"

if (Test-Path($UserPoshHome)) {
Write-Host "$userPoshHome is a valid path."
Write-Host "Copying profile.ps1 to $UserPoshHome"
$currentDir = [Environment]::CurrentDirectory=(Get-Location -PSProvider FileSystem).ProviderPath
if(Test-Path (Join-Path $currentDir profile.ps1)) {
Copy-Item (Join-Path $currentDir profile.ps1) $UserPoshHome # -WhatIf
Write-Host "Copied profile.ps1."

if(Test-Path (Join-Path $currentDir DF_Deploy.psm1)) {
Copy-Item (Join-Path $currentDir DF_Deploy.psm1) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf
Copy-Item (Join-Path $currentDir dflogo.ico) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf

if (Test-Path "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy\df_deploy.psm1") {
Write-Host "Copied DF_Deploy.psm1."
Write-Host "Copying of DF_Deploy.psm1 failed." -BackgroundColor red -ForegroundColor white

if (Test-Path "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy\dflogo.ico") {
Write-Host "Copied dflogo.ico."
Write-Host "Copying of dflogo.ico failed." -BackgroundColor red -ForegroundColor white

if(Test-Path (Join-Path $currentDir DF_Deploy.psd1)) {
Copy-Item (Join-Path $currentDir DF_Deploy.psd1) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf