Archive
PowerShell script to save all .pdf’s as .docx in and underneath a folder failing on Word 2016, working on Word 2010.
- Problem: Word 2016 shows erratic behavior when trying to save (admittedly: complex) .PDF as .DOCX – whether
- using automation
- “The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))”
- “The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)”
- or trying manually.
- “There is a problem saving the file.”
- “A file error has occurred.”
- Or Word crashes.
- using automation
- Workaround: My age-old Word 2010 installation on Windows Vista with PowerShell 2 (gasp!
) 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
- 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,
- 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:
- Any oft-failing scripts should be put last during the daily run? how about length script needs to run?
- Any failing script parts could be modularized and during the daily run?
- any oft-failing scripts? E.g. here the top 8% of failing scripts have almost 30% of the errors.
- Any oft-failing approaches that might benefit from refactoring? Starting with which scripts? Main actions, then sub-actions:
- etc.
- Then this PowerShell script may help which
- extracts the non binary <runlog> items out of the binary rlgx files,
- and merges them into a single file
- 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
- Make this PowerShell script a Scheduled Task,
- So that you can auto-update said XML which you made the data source for your Excel monitoring/planning work book.
- 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
.
- 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
How to automate removing MS-Office VBA project protection for multiple files
- Problem:
- Need VBE extensibility to implement some tools and practices of the modern SDLC.
- 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
- Not solvable by VBA automaton for security reasons:
- VBE password protection (OK, there is Sendkeys, but that is considered harmful).
- digitally signed.
- Developer tab read-only protection: this one is not covered here, since it can be dealt with through regular VBA automation.
- Not solvable by VBA automaton for security reasons:
- Workaround: PowerShell for starters:
- Get Unlock-OfficeMacro.ps1 – including the addition in the comments.
- Wrap the downloaded script like so:
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
- NextProblem: The script removes the warning dialogues on opening the altered MS-Word files remain. This still hampers automation.
- Next workaround: this script automates the GUI:
- “OK”’ing the warning dialogue: “The project file ‘C:\Users\tplagwitz\AppData\Roaming\Microsoft\Templates\documaker.dotm’ contains invalid key ‘DPx’.–Continue Loading Project?”
- making minor changes and saving the file (this also bypasses the "discard certificate" warning, if the file was also signed (as is my case).
-
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) .
- Limitations:
- 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.
- 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).
- 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.
- And here is the AutoIt script:
- “OK”’ing the warning dialogue: “The project file ‘C:\Users\tplagwitz\AppData\Roaming\Microsoft\Templates\documaker.dotm’ contains invalid key ‘DPx’.–Continue Loading Project?”
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
- FWIIW: When trying to run Paul Weterings’ script against a media folder, it failed.
- I added exception handling, to arrive at : “Exception calling “Create” with “1” argument(s): “s:\multimedia1\Piano.Sonatas\vol1\I1.ape (taglib/ape)”.
- 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.
- 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.
- I also
- changed the conversion direction to WMA format, including from MP3 source format.
- added an option $blndelete to not delete source files.
- 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; 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 &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 &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 &lt;string&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; remove the (zero length) mp3 file if (!$strOutName.Length -gt 0) # no success converting -&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.