Archive

Archive for the ‘audience-is-any’ Category

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

Can speech recognition tools be soundly applied for speaking practice in second language acquisition programs? Paper at SLRF 2014

For your convenience, I provide two versions of this presentation: the slide deck:

And a screencast of my presentation:

(Unfortunately, PowerPoint 2013 seems to have introduced a bug in alignment of narration and video. If they fix it, I will republish),

How to export BMC Remedy data into Excel other than via copy-paste

  1. This question came up during consulting (Disclaimer: BMC provides non-customers only limited access to online documentation and trial software).
  2. BMC Remedy Action Request System 8.1  supports a wide variety of reports, including from a query builder, and editing of reports, using the free Eclipse data visualization and reporting plugin BIRT, and exports of reports in a wide variety of formats, including Excel ( you can find more on Exporting to Excel in these articles).
  3. BMC can also read excel and other data: “Data Management is a web-enabled dataload tool that enables you to import data into your system” (source) .
  4. BMC Remedy provides ODBC connectivity (note: read-only , so no round-tripping!). Applications that have been used with this feature include Access, Crystal Reports and Excel. In principal, querying an ODBC data source from Excel (2007 and up) works like this:
  5. On the Excel machine, install the BMC-provided ODBC driver into ODBCINST.INI (contains information about ODBC drivers available system-wide).
  6. Create an ODBC connection, save office data connection files as .odc  file for easy reuse.
  7. Query the Remedy database  using SQL from ribbon: ” Data” / section:”Get External Data”:”From other sources”/ menu item: “From Microsoft Query”, select the Remedy Database, clear “Use the Query Wizard”, log in, in “Microsoft Query” window, add Remedy data tables, then columns to display, then go to menu: “File”/ “Return Data to Microsoft Office Excel”.
  8. Some BMC-ODBC-driver-specific limitations are covered in BMC Remedy AR System ODBC considerations
  9. If the exact required functionality is not built in, BMC remedy also provides extension points for developing plugins. See Application development with BMC Remedy Developer Studio, which happens with  using BMC Remedy Developer Studio, a freely downloadable add-on which can be enabled by a vendor call

Protected: LRC speech recognition speaking practice task: Implementation (Prerequisites installation, configuration, training)

2014/09/14 Enter your password to view comments.

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

Software prerequisites for Speech recognition speaking practice tasks

  1. Tools needed for my LRC speaking practice task design using Windows7 Automated Speech recognition:
    1. proofing tools and tracking changes (suggest MS-Word),
    2. a screencast recorder (free MS-Community Clips: MGFGX3),
    3. an LMS for uploads of one to several dozen MBs files),
    4. and the W7ASR Profile Tool (to back up individual voice training data in a shared, "frozen" LRC).

How speech recognition speaking practice integrates with LRC activities for oral practice, assessment and ePortfolio

  1. My use of Windows7 Automated Speech recognition in 7 languages integrates with
  2. other LRC activities for oral practice, assessment and ePortfolio.
    1. A lower-key and more frequent homework assignment
    2. than our Kaltura student presentation webcam recordings NRBFS (using a URL shortener, as in http://goo.gl/NRBFS),
    3. and with better feedback than our voice-insert recordings with Sanako mV1DR,
  3. these homework assignments prepare for in-class assessments
    1. chapter tests with textbook audio recordings z2KYtk,
    2. screencast recordings of student presentations Cmd3gQ, pair conversations l6H12, and question-response midterm/final exams ZL7DG using Sanako digital audio lab.
  4. All except Kaltura (incompatible with Mahara) also produce language learner ePortfolio pieces.

Logging learning center attendance using MS-Exchange Calendars

  1. Over the years, I had to find a number of solutions for monitoring student attendance in the learning center, by repurposing existing infrastructure (there are dedicated solutions which, however, are often too costly for a departmental center, or not shared well). Here is another idea:
  2. We’ve used the existing campus-wide MS-Exchange infrastructure’s resource mailboxes for room booking and equipment circulation.
  3. We have creatively repurposed this infrastructure for managing the staffing of “offices” (center help desk and tutors). Here we needed to allow conflicts:
    1. multiple students staff the help desk.
    2. 1 tutor staffs the “office”- 1 additional student can book the tutor.
    3. Additionally, we set up a time clock system, based on an Excel Macro, to prevent cheating:
      1. Only the person logging in can sign up to the resource;
      2. while only the help desk can provide the time clock value.
  4. It looks like that a similar setup could be extended to support the common requirement that students, while taking a certain course, spend a certain amount of time per week working in the learning center:
    1. which has been traditionally handled using paper-based sign-in-sheets or, at best, spreadsheets.
    2. With digital input, the data could be basis for analytics and visualizations, taking advantage of existing tools like MS-Calendaranalytics.
    3. Such a system would, however, require creation of resource calendar per course section that need to monitor learning center attendance. However, this needs to be done only once and would be reusable, since data can be filtered by input time, as long as no entirely new courses/sections come online.
  5. But hasn’t mandatory weekly learning center attendance been made obsolete by ubiquitous computing and the web?
    1. Definitely in some of its more antiquated forms: I have worked at institutions where the computer-savvy students attended the learning center once at the beginning of the term to copy all the learning materials on the network share onto a thumb drive, and I would not want to force somebody to come to the learning center continuously for such a trivial purpose as accessing files on a not-web-accessible network share.
    2. However,  there seems to be a lot of unmined pedagogical value in learning center group work and blended instruction (under tutor supervision), like in a homework emporium (provided your program is big enough to have continuous tutor support and sufficient learner overlap).