Archive

Posts Tagged ‘ms-powershell’

PowerShell script to save all .pdf’s as .docx in and underneath a folder failing on Word 2016, working on Word 2010.

  1. Problem: Word 2016 shows erratic behavior when trying to save (admittedly: complex) .PDF as .DOCX – whether
    1. using automation
      1. “The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108  (RPC_E_DISCONNECTED))”
      2. “The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)”
    2. or trying manually.
      1. “There is a problem saving the file.”
      2. “A file error has occurred.”
      3. Or Word crashes.
  2. Workaround: My age-old Word 2010 installation on Windows Vista with PowerShell 2 (gasp! Smiley) manages this automation script (inspired by The Scripting Guy) just fine:
$Word = NEW-OBJECT –COMOBJECT WORD.APPLICATION  
# Acquire a list of DOCX files in a folder
$Files = GET-CHILDITEM -include *.pdf -exclude *_converted.pdf -recurse -path 'G:\bookz\office\excel' # 'G:\bookz\lang\vba' # 'G:\bookz\office\access' # 
 
Foreach ($File in $Files) {
    try{
        write-host "Trying  " $File.fullname 
        # open a Word document, filename from the directory
        $Doc1=$Word.Documents.Open($File.fullname)
        write-host "Opening " $File.fullname ". RESULT=" + $?
        # Swap out PDF with DOCX in the Filename
        $Name=($File.Fullname).replace("pdf",“docx”) # $Name=($Doc1.Fullname).replace("pdf",“docx”)
        # Save this File as a PDF in Word 2010/2013 - hm, and 2016 fails? 
        $Doc1.saveas([ref] $Name, [ref] 16) # see WdSaveFormat enumeration : 16 is word default, 
    }
    catch 
    { 
        $ErrorMessage = $_.Exception.Message
        $FailedItem = $_.Exception.ItemName
        write-host "Caught error saving " $FailedItem ". Msg: " $ErrorMessage 
    } 
    finally {
        $Doc1.close()
        [GC]::Collect() # watch me trying a number of things to get this to work with Word 2016... 🙂
        move-item -path $file.FullName -destination ($file.Directory.ToString() + "\" + $file.BaseName + "_converted" + $file.Extension)
    }
}

 

PowerShell Script to convert your Testing Anywhere run logs into a Excel pivot table data source

    1. If confronted with a sizable Testing Anywhere test script codebase which has been marginally, but not substantially enhanced/cleaned up in several years while producing a barrage of automation errors daily,
    2. you may find that the run suite errors that Testing Anywhere logs automatically in its rlgx files are your best data source  for monitoring and designing a plan of attack:
      1. Any oft-failing scripts should be put last during the daily run? how about length script needs to run?
      2. Any failing  script parts could be modularized and during the daily run? rlgx-excel-pivot-scripts-avg-duration-percentage
      3. any oft-failing scripts? E.g. here the top 8% of failing scripts have almost 30% of the errors. image
      4. Any oft-failing approaches that might benefit from refactoring? Starting with which  scripts? Main actions, then sub-actions:rlgx-excel-pivot-scripts-error-type-countrlgx-excel-pivot-scripts-error-type-count
      5. etc.
    3. Then this PowerShell script may help which
      1. extracts the non binary <runlog> items out of the binary rlgx files,
      2. and merges them into a single file
      3. which it wraps with an XML declaration and root level node that Excel can work with.
 
add-content -value '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 Get-childitem -path C:\td\testinganywhere\files\rlgx\arnold-pc1 |
? {$_.Extension -eq ".rlgx"} |  
% { $file = convertto-string $_.FullName  
$match = [regex]::Match($file,'\s+(.*)\s+',"SingleLine,IgnoreCase").value    
add-content $match -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 } 
add-content '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 
  1. Make this PowerShell script a Scheduled Task,
  2. So that you can auto-update said XML which you made the  data source for your Excel monitoring/planning work book.
    1. The post-processing of the default error log messages that makes meaningful pivoting actually possible, is left as an exercise to the reader by Testing Anywhere Smiley .

How to automate removing MS-Office VBA project protection for multiple files

  1. Problem:
    1. Need VBE extensibility to implement some tools and practices of the modern SDLC.
    2. Software consists of not only 1000s of Word templates which are anything but DRY, but also highly protected even during what should be the SDLC
      1. Not solvable by VBA automaton for security reasons:
        1. VBE password protection (OK, there is Sendkeys, but that is considered harmful).
        2. digitally signed.
      2. Developer tab read-only protection: this one is not covered here, since it can be dealt with through regular VBA automation.
  2. Workaround: PowerShell for starters:
    1. Get Unlock-OfficeMacro.ps1  – including the addition in the comments.
    2. Wrap the downloaded script like so:
    3.    
      Get-ChildItem -Include *.do?m* -Exclude *_unlocked* -Path "G:\imf\word templates\Quarterly Releases_unprotected_ps"  -Recurse |`  
      
      foreach{ $_.IsReadOnly = $false   $output_filename =  $_.Directory.ToString() + "\" + $_.BaseName + '_unlocked' +  $_.Extension .\Unlock-OfficeMacro.ps1 $_.FullName  $output_filename 
      }
      Exit 
       
  3. NextProblem: The script removes the warning dialogues on opening the altered MS-Word files remain. This still hampers automation.
  4. Next workaround: this script automates the GUI:
    1. “OK”’ing the warning dialogue: “The project file ‘C:\Users\tplagwitz\AppData\Roaming\Microsoft\Templates\documaker.dotm’ contains invalid key ‘DPx’.–Continue Loading Project?” 
      image
    2. making minor changes and saving the file (this also bypasses the "discard certificate" warning, if the file was also signed (as is my case).  image
    3. Prerequisites: none, other than putting your word files in a folder the script (which the script will prompt you for, and for an (optional) substring, to filter file names) .

    4. Limitations:
      1. I  used to have also have, per module in the VBA project, warnings: “Microsoft Visual Basic for Applications Unexpected error (40230) ” and try to bypass these also, but since I cannot replicate the warnings, this remains untested.image
      2. A superior approach (enabling round tripping) would be to attempt to automate entering the password, but the traditional SendKeys approach is unreliable, and  newer approaches (using SendMessage from the the Win32 API or bypassing the intended negative effects of password protection, via an in-memory substitution).
    5. And here is the AutoIt script:
  
include <Array.au3>
#include <debug.au3>
#include <File.au3>
#include <log4a.au3>
Opt("WinTitleMatchMode", 2)
Opt("MustDeclareVars", 1)
Dim $file, $runpath, $iPID, $i, $folderpath, $pattern, $files, $filepath, $files, $ret, $oAppl, $oDoc, $sFilter
_log4a_SetEnable()
_log4a_SetOutput($LOG4A_OUTPUT_BOTH)
$pattern = InputBox("File Pattern?", "Enter file pattern,  beyond (before) *.do?m (= Files with macros), that files have to match.", "_unlocked")
$folderpath = InputBox("Where?", "Enter folder to find files in...")
$sFilter = "*" & $pattern & "*.do?m|~*,Backup*"
If Not (FileExists($folderpath) And StringInStr(FileGetAttrib($folderpath), "D")) Then
	MsgBox(1, "Error", " The path you entered does Not seem To exist Or is Not a folder. Exiting....")
	Exit
Else
	$files = _FileListToArrayRec($folderpath, $sFilter, $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_RELPATH)
	For $i = 1 To UBound($files) - 1
		$file = $files[$i]
		If (StringRight($folderpath, 1) <> "\") Then $folderpath = $folderpath & "\"
		$filepath = $folderpath & $file
		Local $iPID = Run('"C:\Program Files\Microsoft Office 15\root\office15\WINWORD.EXE" /q /a /m "' & $filepath & '"', "", @SW_SHOWMAXIMIZED)
		$ret = WinActivate("- Word", "")
		$ret = WinWaitActive("- Word", "", 5)
		If ($ret = 0) Then
			_log4a_debug("cannot load: " & $filepath & @TAB & @ScriptLineNumber & @CRLF)
			$ret = ProcessClose($iPID)
			ContinueLoop
		Else
			_log4a_debug("processing: " & $filepath & @TAB & @ScriptLineNumber & @CRLF)
		EndIf
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		Send("{SHIFTDOWN}{SHIFTUP}")
		Sleep(100)
		Send("!{f11}")
		$ret = WinActivate("Microsoft Visual Basic for Applications", "invalid key")
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		$ret = WinWaitActive("Microsoft Visual Basic for Applications", "invalid key", 5)
		If ($ret = 0) Then
			_log4a_debug("nothing to do with invalid key, will close word and continue next file: " & @ScriptLineNumber & @CRLF)
			$ret = ProcessClose($iPID)
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
			ContinueLoop
		EndIf
		If $ret <> 0 Then
			$ret = ControlClick($ret, "", "Button1")
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		Else
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		EndIf
		Sleep(1000)
		$ret = 0
		Sleep(5000)
		While (0 <> WinActivate("Microsoft Visual Basic for Applications", "Unexpected error (40230)"))
			$ret = ControlClick("[CLASS:#32770
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		WEnd
		Sleep(3000)
		$ret = WinActivate("Microsoft Visual Basic for Applications", "")
		$ret = WinWaitActive("Microsoft Visual Basic for Applications", "", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		If ($ret = 0) Then WinActivate("Microsoft Visual Basic for Applications", "")
		$ret = WinWaitActive("Microsoft Visual Basic for Applications", "", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		$ret = ControlSend("Microsoft Visual Basic for Applications", "", "VbaWindow1", "'dummy" & @CRLF)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		Sleep(1000)
		Send("!q")
		Sleep(1000)
		Send("!{f4}")
		Sleep(1000)

		$ret = WinWaitActive("Microsoft Word", "", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		If ($ret = 0) Then $ret = WinActivate("Microsoft Word", "")
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		$ret = WinWaitActive("Microsoft Word", "", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		If ($ret <> 0) Then
			Send("!s")
			Sleep(1000)
			$ret = WinWaitActive("Microsoft Word", "discarded", 5)
			If ($ret = 0) Then
				_log4a_debug("the certificate dialogue is not up yet", True)
				$ret = WinWaitActive("Microsoft Word", "", 5)
				If ($ret <> 0) Then
					_log4a_debug("failure with !s: " & @ScriptLineNumber & @CRLF)
					Send("{Enter}")
				EndIf
			EndIf
			Sleep(1000)
		Else
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		EndIf
		$ret = WinWaitActive("Microsoft Word", "discarded", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		$ret = WinActivate("Microsoft Word", "discarded")
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		$ret = WinWaitActive("Microsoft Word", "discarded", 5)
		If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		If ($ret <> 0) Then
			$ret = ControlClick("Microsoft Word", "", "Button1")
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
			If $ret = 0 Then Send("!d")
		Else
			If ($ret = 0) Then _log4a_debug("failure on: " & @ScriptLineNumber & @CRLF)
		EndIf
		Sleep(1000)
		Send("{BACKSPACE}")
		Sleep(1000)
		Send("^s")
		Sleep(3000)
		ProcessClose($iPID)
		Sleep(1000)
	Next
	Sleep(1000)
EndIf

Adding APE input format support (and WMA output format) to Convert2MP3.ps

  1. FWIIW: When trying to run Paul Weterings’ script  against a media folder, it failed.
  2. I added exception handling, to arrive at : “Exception calling “Create” with “1” argument(s): “s:\multimedia1\Piano.Sonatas\vol1\I1.ape (taglib/ape)”.
  3. Took me a while to realize: Taglib-sharp seems to stumble over APE file formats. Even though the taglib release notes seem to say it is supported since 2009. And even though I upgraded taglib-sharp to current version 2.1.0.0.
  4. Now I am simply bypassing the call to taglib-sharp for files with the APE extension, and default to a bitrate of 64 for those.
  5. I also
    1. changed the conversion direction to WMA format, including from MP3 source format.
    2. added an option $blndelete to not delete source files.
  6. No warranties of any kind. All due credit goes to  Paul Weterings’ script here.
# trp: any2wma.ps1
# wma2mp3 conversion powershell script...
# dec 2010 version 1.1 Servercare, Paul Weterings
# Feb 2013 version 1.2 ServerCare, Paul Weterings, Byron &amp;amp; Chuck, added DRM check,
# more formats and bitrate
# trp: nov 2015: using *.wma as output, added *.ape as input (and *.mp3)
Set-StrictMode -Version Latest
# Set-PSDebug -trace 1
$blndelete = $true # $false # control whether originals get deleted after some success conversionchekc - good idea, but not yet
# Where are we?
# $loc = Get-Location
$loc = $PSScriptRoot # trp
# $tag = $tag = $loc.Path + "\MPTag" # trp: not really? butr this syntax apears more often , is this ps' append to existing variable
# trp:
$tag = $tag = $loc + "\MPTag" # trp: not really? butr this syntax apears more often , is this ps' append to existing variable

# Import MPTag module
# See: http://powershell.com/cs/media/p/9129/download.aspx

$errorcountbefore = $error.count

Import-Module $tag
If ($error.count &amp;gt; $errorcountbefore)
{
Write-host "trp:" + $lastexitcode + \n + $error[0]
}

# Use Windows Media Player
# See: http://msdn.microsoft.com/en-us/library/ee485348.aspx
$errorcountbefore = $error.count
$mpobj = New-Object -ComObject wmplayer.ocx
If ($error.count &amp;gt; $errorcountbefore)
{
Write-host "trp:"+ $lastexitcode + \n + $error[0]
}

$tool = "ffmpeg.exe" # trp: done:test:can i have ffmpeg in $env:Path?$loc.path+"\ffmpeg.exe"
$successcounter = $failurecounter = 0

# This is the root folder where script looks for the music, adjust this to your liking.#
# #
$strBaseDir = "S:\multimedia1\Piano.Sonatas" # ape
#
########################################################################################

$objParent = Get-ChildItem $strBaseDir -recurse -Include *.aac, *.flac, *.m4p, *.ogg, *.ra, *.rm,`
*.ram, *.raw, *.wav, *.mp3, *.ape
# trp: now target instead: *.wma
# todo: *.m4a,

foreach ($child in $objParent)
{

trap {
Write-Warning ('Failed to access "{0}" : {1} in "{2}"' -f $child.FullName, $_.Exception.Message, $_.InvocationInfo.ScriptName)
continue
}

# trp: exclude .ape from taglib-sharp and set default bitrate of 64k
if ($child.Fullname.EndsWith(".ape"))
{
$bitrate = 64
}
else
{
# Use the MPTag library to get the correct bitrate
$libmedia = Get-MediaInfo $child.Fullname
$bitrate = $libmedia.Properties.AudioBitrate

# sometimes the bitrate is reported way to high... anything over 192 gets lowered.
# adjust if wanted/needed
if ($bitrate -gt 192)
{
$bitrate = 192
}
}
# trp : work around Get-MediaInfo not working
if (!$bitrate) # yes = null-valued expression
{
$bitrate = 64
}
"-----------------------------------------------------------------------------"
"- Processing: " + $child.FullName + " at Bitrate $bitrate"

$media = $mpobj.newMedia($child.Fullname)
$protected = $media.getItemInfo('Is_Protected')
# Some files, such as flac or ogg may have the protection attribute empty
if ($protected)
{
$prot = [System.Convert]::ToBoolean($protected)
}
else
{
$prot = $false
}

if (!$prot)
{
$strInName = $child.FullName
$strOutName = $child.DirectoryName + "\" + $child.BaseName + ".wma" # trp ".mp3"

# The argument string that tells ffmpeg what to do...
# The generic syntax is:
# ffmpeg [global options] [[infile options][‘-i’ infile]]...
# {[outfile options] outfile}...
#
# -i filename (input) :: -i &amp;lt;string&amp;gt; :: input file name
# -y (global) :: -y :: Overwrite output files without asking.
# -acodec codec (input/output) :: -acodec libmp3lame :: Set the audio codec.
# This is an alias for -codec:a.
# trp: acodec wmav2 A..... wmav2 Windows Media Audio 2
# :: -ab 128k :: Set bitrate in bits to constant 128k bit rate
# -ac[:stream_specifier] channels (input/output,per-stream) :: -ac 2 ::
# Set the number of audio channels. For output streams it is set by default
# to the number of input audio channels. For input streams this option only
# makes sense for audio grabbing devices and raw demuxers and is mapped to the
# corresponding demuxer options.
# -ar[:stream_specifier] freq (input/output,per-stream) :: -ar 44100 ::
# Set the audio sampling frequency. For output streams it is set by default
# to the frequency of the corresponding input stream. For input streams this
# option only makes sense for audio grabbing devices and raw demuxers and is
# mapped to the corresponding demuxer options.
# :: $mp3name :: Output name
# file:///C:/ffmpeg-git-1eabd71-win32-static/doc/ffmpeg.html

# trp: $arguments = '-i ' + '"'+$strInName +'"' +' -y -acodec libmp3lame -ab ' + $bitrate`
$arguments = '-i ' + '"'+$strInName +'"' +' -y -acodec wmav2 -ab ' + $bitrate`
+'k -ac 2 -ar 44100 ' + '"' + $strOutName+ '"'

# This is where the conversion takes place
# trp:debug
Write-Warning "$tool + `r`n"
Write-Warning "$arguments + `r`n"
Invoke-Expression "$tool $arguments"

# Lets see what we just converted, did everything go OK?
$objOutFile = get-item $strOutName

# if conversion went well the mp3 file is larger than 0 bytes, so remove the original file,
# otherwise leave the wma file &amp;amp; remove the (zero length) mp3 file

if (!$strOutName.Length -gt 0) # no success converting -&amp;gt; delete failed converted file
{
echo "----- removing $strOutName"
Remove-Item -LiteralPath $strOutName
$failurecounter++
}
else # success converting
{
$successcounter++
if ($blndelete) # delete original requested
{
# you might want to consider moving the original file to
# anther location instead of removing it.
# Allowing you time to manually check if the conversions went OK
echo "----- removing $strInName"
Remove-Item -LiteralPath $strInName
}
}
}
else # $prot
{
"! File " + $child.FullName + " is DRM protected, skipping..."
$failurecounter++
}
}

# We are done, so lets inform the user what the success rate was.
Echo "Processing completed, $successcounter conversions were succesfull `
and $failurecounter were not."

Setting time zone for all and for all misconfigured OWA users

live@edu/Office365 Exchange in the cloud does not seem to allow setting a default time zone, but rather leaves it to the user to change the time zone (defaulting to the time zone the cloud server is in that the user happens to hit) on first login – in the world I operate in, большая ошибка!

How to use the cmdlet set-MailboxRegionalConfiguration with parameter TimeZone to change the time zone of all your mailboxes is nicely explained on the blog How Exchange Works here, including screenshot and PowerShell command.

Unfortunately such an operation is reported to have needed 3 days for updating all mailboxes in an educational live@edu installation with 30000 users… You can restrict the mailboxes touched by examining first which are not in your local time zone (consult the MS TimeZone table for syntax, e.g. US “Eastern Standard Time”):

 

$mymailboxes = get-mailbox

ForEach ($examinedmailbox in $mymailboxes){

$regionalconfig = get-MailboxRegionalConfiguration –identity $examinedmailbox.identity

 

if ($regionalconfig.timezone -ne “Eastern Standard Time”){                      

Set-MailboxRegionalConfiguration -identity $examinedmailbox.identity -TimeZone  “Eastern Standard Time”  -confirm:$false

}

}

LRC Resource mailboxes configuration using PowerShell

See below or click for larger view.