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

How to prevent OneNote from crashing when hovering over search results

  1. Problem: In a strange new development, my OneNote crashes (multiple times, until things eventually become stable again) when I search my notebooks  from the upper right search box. Specifically, as soon as I hover my mouse over the search results display.
  2. My environment: OneNote 2013 32-bit on Windows 7 64-bit. These are notebooks on a network share, and they are updated from multiple machines. Not aware of any recent changes to my mouse settings or drivers.
  3. My Workaround: Navigate the search results using the keyboard. This seems to prevent those crashes.

I’ll see you on the dark side of the moon…

 
      ... ove mirabilmente era ridutto
      ciò che si perde o per nostro diffetto,
      o per colpa di tempo o di Fortuna:
      ciò che si perde qui, là si raguna.

Will pass us by and you’ll never have to see it again, okay? – So it won’t hit us? – Not a chance.

You are receding. Before you know it you’re frozen. Until 2033, 50th.

Categories: Personal

How to split an Excel workbook into one file per sheet with VBA (reworked)

  1. There are other code snippets on stackoverflow.com, but I went with the top match in the Google search.
  2. Couldn’t get it to work (error # 424 Object required on xWS.copy in Excel 2013, with the VBA run from a separate utilities workbook) until I made these changes:
'TRP reworked http://www.extendoffice.com/documents/excel/628-excel-split-workbook.html
Sub Workbook_Split() 'Updated by TRP 20150924

    Dim xPath As String
    Dim myWorkSheet As Worksheet
    xPath = Application.ActiveWorkbook.Path 'the source workbook needs be saved first
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each xWS In ActiveWorkbook.Sheets    'trp: replaced ThisWorkbook.Sheets
        'trp: replaced xWS.Copy 'toask: this fails with 424: object required
        Set myWorkSheet = xWS
        myWorkSheet.Copy 'If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.
        Application.ActiveWorkbook.SaveAs FileName:=xPath & "\" & myWorkSheet.Name & ".xlsx" 
        'trp: replaced xWS.Name & ".xls"
        Application.ActiveWorkbook.Close False    'trp:savechanges:=False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

“"Mira, mamá! Sin manos!". Can speech recognition tools be soundly applied for L2 speaking practice?” FLEAT VI, August 11-15 at Harvard University

How to easily rearrange your sections via the headings in MS-Word’s Navigation Pane

  1. Short answer Show Navigation (from ribbon ./ View), then Drag&drop/
  2. word-navigation-pane-drag-headings
  3. Maybe obvious, but had escaped me so far – I thought such ease would only come only with outline view  – what a great feature!
Follow

Get every new post delivered to your Inbox.

Join 100 other followers