Archive

Posts Tagged ‘ms-office’

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

Alter embed URL letter case to fix your broken MS-Office-file embeds in WordPress

  1. Problem: I just notice that a lot of my WordPress OneDrive embeds are broken (not sure since when).
  2. Root cause:
    1. Upon comparing the embed URLS in WordPress posts with what embed URLS sharing with OneDrive results in, it appears that the letter case in the embed URLs has been changed to all uppercase, pointing to resources that do not exist (doh!).
    2. Not sure who is to blame for altering the case:
      1. WordPress,
      2. OneDrive (note there is also the transition from “skydrive” to “onedrive”),
      3. Windows Live Writer? The latter hardly, since I did not touch these posts.
  3. Solution:
    1. Given that, while “cid=” seems all uppercase, “resid=” all lowercase, but “authkey=” is mixed case , there is not easy case change operation you could do locally to restore the original try.
    2. So you have to find the original files you embedded (not trivial from the URL which does not contain even a hint of the file name or extension) and re-embed it through sharing with OneDrive.

LRC Fall 2013 announcements

  1. The LRC has upgraded to Windows 7 and Office 2010.
    1. Benefits:
      1. Your students can use the computer interface from the default English to  about 20 languages, including non-Western.
      2. Your students can also use speech recognition (in English, French, German, Japanese, Mandarin, and Spanish), e.g. for dictation exercises (Example videos:  very bad French, decent German). Students can train the computers to their voice and take their training data with them.  I’d love to explore with you possibilities for pronunciation practice with automated intelligent feedback .
      3. Your students can use old and new MS-Office Proofing tools.
    1. Caveat:  W are still trying to restore some former functionality (e.g. no Google Arabic, Farsi and Russian IME etc.). Please bear with us while we deal with the new college tech infrastructure. 
  1. The LRC has upgraded its Sanako digital audio lab software.
    1. Because of budgetary constraints, our software agreement had to end  with version 5 . This summer, the vendor presented us with a free upgrade to version 7, with compliments for my blog posts about using the Sanako.
    2. Benefits:  We decided to implement the upgrade lest you and your students need relearn in the middle of the academic year and since Version 7 adds valuable language learning :  which I would love to explore with you: Vocabulary exercises  and Pronunciation exercises which  make use of the computerized text-to-speech capabilities we just implemented with windows 7
    3. Caveats:
      1. We are still trying to restore the old Sanako configuration. E.g. Pairing recording is not working currently.
      2. I hope to upgrade my LanglabEmailer software to support the new version after the term is underway.
  1. For students attending distance classes with Saba Centra in the LRC, microphone audio on listening stations fixed, no more 30 minute delay  when joining class.
  1. UNCC is upgrading to Moodle 2. The CTL is investigating how the LRC Metacourses for audio materials I created can be converted to Moodle 2. If you need the audio materials from the metacourses,  we can help you upload them into your individual courses temporarily. 
  1. Classroom AV: We found a temporary workaround for the projector image quality and are investigating permanent solutions. Currently no VHS video and doc cam display during classes  (we would love to  scan your text anyway and distribute them digitally).
  2. LRC Calendars and Booking:
    1. In the LRC Room and Equipment List, your will notice some new film studies equipment (calendars requested from ITS).
    2. We added new calendars to the Quicklinks on LRC home pageTutors and LRC assistants. Please keep checking how we fill these open positions over the next few weeks, and use the help they can offer you.
    3. When booking, you can
      1. get help at the LRC reception desk;
      2. book yourself  from anywhere,
      3. or have your “delegate” book (planned; setup requested from ITS).
  1. I will continue next week with the biweekly Sanako Clinic to aid teachers with their LRC class preparation. Please consult the LRC calendar if you want to drop in, or reschedule one with me for your needs.
  2. I am also offering LRC introductions for your class during the week 2 and 3 on a “first-come, first-served” basis, and à la carte (I suggest consulting a one-sheet menu with an overview  of LRC facilities that I am  preparing.) Please let me know if you are interested.

Protected: Windows 7 LRC image: The list

2013/06/21 Enter your password to view comments.

This content is password protected. To view it please enter your password below:

How you can share MS-Office files via MS-OneNote instead of directly through MS-SkyDrive

  1. Simple steps:
    1. Drag and drop your MS-Office File to your MS-OneNote page.
    2. When prompted, choose to “insert a copy” (rather than merely linking the original file). image
    3. This puts a copy of the file in the MS-OneNote folder on your local drive,
    4. which (file and folder) gets synched with your online (MS-SkyDrive) version,
    5. which, if you shared it, gets synched with the MS-OneNote folder on the local drive of the PC of the person you are sharing with,
    6. who, by double-clicking, can open and edit his synched local version of MS-Office file in the corresponding MS-Office application.
  2. Stepping back:
      1. Benefit: If you have a working MS-OneNote-based workflow, embedding MS-Office file can quickly extend this workflow.
      2. Risk: If you do not share the MS-OneNote with other editors, you should have no problem. Be aware, though, that concurrency is limited. Unlike accessing the MS-Office file in MS-Office through Office Web apps from MS-SkyDrive directly, editing the MS-Office file from MS-OneNote does not block updating the MS-Office file on remote computers – so expect synching conflicts later if you do not manage concurrency (e.g. by limiting editing sessions). 

Overview of MS natural language support on Windows Vista+7/Office 2007, 2010

Snapshot summer 2012 in conjunction with our language center upgrade to Windows7 and Office 2010. Click here for larger version.

Foreign language support in LRC MS-Office 2010

  1. A full set of proofing tools is available, thanks to MS-Office Language Packs installed on the Windows 7 computers, for all non-classical languages studied here:
  2. Language Native name
    Arabic العربية
    Chinese (Simplified) 中文(简体)
    Chinese (Traditional) 中文 (繁體)
    English English
    French français
    German Deutsch
    Greek Ελληνικά
    Hebrew עברית
    Hindi हिंदी
    Italian italiano
    Japanese 日本語
    Korean 한국어
    Polish polski
    Portuguese (Brazil) Português
    Portuguese (Portugal) português
    Russian Русский
    Spanish español
  3. Some languages have only limited features provided by the MS-Language Interface Pack:
  4. KiSwahili Kiswahili
    Persian (Farsi) فارسی
    Yoruba ede Yorùbá