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.

Tuesday, October 11, 2011

Feature Requests for Idera's PowerShell Plus v4.1

If you would like these enhancements done as well, consider copying the list below and sending an email to support@idera.com.  The more customers ask for an enhancement, the quicker it gets done!

Name:
Company:
Product Version: 4.1
PowerShell Version: 2.0
Question/Issue: Enhancement requests
·         One-chord execution to comment or uncomment selected lines in the editor (e.g., ctrl+shift+c to comment, ctrl+shift+u to uncomment)
·         Live IntelliSense for new variables without having to first execute the script (PowerGUI has that!)
·         More standard keystrokes or customizable keystrokes for editor shortcuts such as
o   Ctrl+k, ctrl+k for bookmark, like SQL Management Studio
o   F3 to repeat last search
o   Ctrl+h for search and replace
·         Option to reopen last-opened files when starting PowerShell Plus
·         Option to save “solutions” or groups of files to open at once
·         Start page to remember a longer list of recently opened files, and make its window a lot bigger.

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.

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


.DESCRIPTION
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.

.EXAMPLE
.\Install-DF_Deploy
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."
}
else{
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."
}
else{
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
}

}
}

Monday, April 25, 2011

How to Pass a Variable to PowerShell from a DOS Batch File

Update: Many thanks to @ye110wbeard for a much simpler solution to pass the current working directory to a powershell command from a DOS batch file.


Powershell -executionpolicy remotesigned -file %~dp0\YourScript.ps1.


Scott


------------------------------------------------------


I'm sure there is a better way, but here is a way to pass the current directory for an executing batch file to a PowerShell script, to execute a PowerShell script that is in that directory. Then control returns to the batch file. Thanks to Wes' Puzzling Blog for the tip on the %~dp0 trick!





  • Echo the current directory to a text file in the $pshost directory. This is almost always the directory c:\windows\system32\WindowsPowerShell\v1.0.




  • The command to do this in the batch files is:
    echo %~dp0 > c:\windows\system32\WindowsPowerShell\v1.0\currentBatchPath.txt




  • Now start the PowerShell session, read the file contents into a variable. The variable will end in a backslash, so use substring to parse out all but the last character. Then use the Set-Location to change to the batch directory, and execute the PowerShell script.




  • The (ugly) command looks like this:




  • powershell -executionpolicy remotesigned $currentBatchPath=get-content c:\Windows\System32\WindowsPowerShell\v1.0\currentBatchPath.txt;Set-Location $currentBatchPath.substring(0,($currentBatchPath.Length-1));.\YourPowerShellScript.ps1