Archive

Archive for the ‘audience-is-IT-staff’ 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

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

How to add control of student sound/recording volume, sidetone, restart, and more to a Sanako Study 1200 environment, using the Launch Program feature and AutoIt

2014/08/22 1 comment
  1. UPDATE: A Windows7 (and Vista) version – which also uses a simplified deployment mechanism – is in the works, check back for a new post here.
  2. In refining our Sanako classroom setup, we improved the control, that the Sanako Study 1200 affords the teacher over the student clients in the computerized classroom,
  3. by extending the built-in Launch Program feature
  4. with custom-made executables (realized in AutoIt V3) that can control the volume (here on Windows XP SP3).
  5. This it how it works: Launch any of the programs (what each does is in its name) to any individual/group of students or the entire class in order to do any of these things on the student computers that the Sanako out of the box does not allow you to control, and that I often wish I could do when teaching language classes in a Sanako (or other computerized classroom management system) environment, like
    1. controlling the volume of what the students
      1. hears
      2. records
    2. turning the student sidetone (= echoing back the student microphone into the student headset) on and off
    3. starting/pausing Windows Media Player
    4. launching/closing quiz files in MS-Word
    5. restarting an entire (misbehaving) student applications
  6. Here is what we have: sanako-launch-programs-autoit1
  7. Here is how using what we have looks like:
  8. sanako-launch-programs-autoit
  9. You can now request the download of these language lab enhancing programs, including source code, here.

Windows XP user profiles copy to greyed out ?

  1. Problem:
    1. For all profiles, not only for the logged in profile… Not likely a permissions problem? I can delete
    2. 201202-copy-testuser-final
  2. Solution:
    1. “if you’ve ever been logged into a profile without rebooting you won’t be able to copy that profile either. So you might have to restart before you can copy the profile.”
    2. or use shutdown /r when connecting via MSTSC.

Bad audio quality in Moodle Kaltura recordings on iMacs under ambient sound conditions. A running log (closed)

  1. Symptom: See title.
  2. Cause: While speakers of the 3.5 mm headset take over from the iMac speakers, when plugged in, the 3.5 mm headset micro does not disable the webcam micro. The webcam micro remains the recording source for Moodle Kaltura (mac os x.6, safari 6, flash 10).
  3. Possible solutions:
      1. 1st choice: is there a way for the admin to override this permanently?
      2. 2nd choice: is there a way for the end user to override this on a case-by-case basis?
      3. "The Califone 3066AV is compatible with both Windows and Mac audio outputs (but is not compatible with iMacs)." ("The Wow starts now!").. Do we have to tie up our boom and lavalier microphones for this?
      4. Try the flash settings by right clicking in the video window, selecting the microphone icon, then choosing the external (= non-webcam) microphone for input.
      5. kaltura flash microphone settings0kaltura flash microphone settings
      6. Fail. The real culprit is the iMacs line-in audio-in for which you need a preamp to get it to work with analog microphones. Preamps seem to start at $40, but for that I can get a PC webcam (Microphone included). 

ScreenToGif Debugging: Object reference not set to an instance of an object (#7)

  1. Trying to contribute a tiny bit to the development of this great utility ScreenToGif:
  2. 1st version: image
    1. crashes on pressing “stop”: image
    2. the Log
    3. Title: NullPointer in the Stop function 7
      Message: Object reference not set to an instance of an object.
      Source: ScreenToGif
      TargetSite: Void Stop()
      StackTrace:    at ScreenToGif.Legacy.Stop()
      Date/Time: 7/3/2014 12:55:16 PM
      ==============================
  3. Newer version: image
    1. does not hang on “stop”
      1. after adding overlay text (great new feature!), image
        1. seems to hang on pressing “Done” with “Analizing [sic] Unchanged Pixels”
        2. crashes on pressing window close: image
        3. Maybe I was just not patient enough? for:
      2. without adding overlay pixels:
        1. seems to also take a long time (2-3minutes analyzing, without a progress bar, or any other indication that the program is still working and has not crashed – the couple of minutes “Processing” seem to go by faster, thanks to the progress bar and frame counter) for a 5fps 1073*810, about 300frames, but hey! It works:image
        2. Plus you can now open the resulting GIF file directly from the creator.
        3. Actually, when I click “Stop” (I realize  I am supposed to click “Close”), I still get a NullReferenceException (#9), probably should not: image
    2. No log has been outputted with the new version .

Sanako Web browsing activity not compatible with modern web infrastructure (SunGard Portal, Outlook Web Access)?

  1. To access campus resources – here an assignment in a Moodle course – our students need to log into a SunGard portal (with SSO – a pretty common product in higher education)l, but already the redirection after login fails with this session expiration error.
    1. We could work around this problem by browsing to the target site and logging into Moodle directly.  But our students normally do not know how to do that.
  2. More problems arose when students tried to open an email in their campus OWA, in a popup window, which, however, gets redirected to the first browser homepage.
  3. This was during an “open/prevent policy” web browsing-activity, where – to keep students on task – only certain sites (social web, webmail) were blocked.., as can be seen in this screenshot: webbrowsing-prevent-policy