Snippets and Snails

sqlplus Wrapper Script For Linux

2010-02-12T16:19:00.000-08:00

Installed Oracle 10g on RHEL5 x86_64 today and the sqlplus command prompt was driving me bats. This post gave some great tips on fixing sqlplus behavior. I combined them in one shell script to fix the backspace issue and the lack of history.

First, as the root user, install rlwrap. It should be a simple download, untar, then configure; make install; as root.

Once rlwrap is installed and working, create sqlplus.sh as the user who is going to be running sqlplus (change ~/ to whatever path you deem appropriate for the script):


#!/bin/bash
stty erase ^H
rlwrap sqlplus $@
stty erase ^?

Make the script executable:
chmod 0755 ~/sqlplus.sh

And in ~/.bashrc, add


alias sqlplus=’~/sqlplus.sh’

Voilà! Your Linux sqlplus prompt should now be a bit more friendly.

How to Delete Embedded Excel Objects in VBA

2010-02-09T13:54:00.000-08:00

I wanted to do a bulk delete of embedded chemical structures in a series of Excel workbooks, without touching embedded graphs and data tables. Here's the resulting VBA code. You should be able to easily modify this to delete any custom object types in an Excel workbook.



Sub WipeObjects
' Delete all current embedded chemical structures from currently open Excel workbook
' Example sub to call ChemObjectWipe function
    MsgBox ChemObjectWipe()
End Sub

Function ChemObjectWipe() As String
    Dim currSheet As Worksheet
    Dim itemnum As Long
    For Each currSheet In ActiveWorkbook.Sheets
        Dim structures As New Collection
        Dim embeddedItems As OLEObjects
        Dim shp As OLEObject
        Dim deleteStatus As String
        Dim creator, currName As String
        Set embeddedItems = currSheet.OLEObjects
        If (embeddedItems.Count > 0) Then
        For Each shp In embeddedItems
            currName = ""
            creator = ""
            currName = shp.Name
            creator = UCase(shp.progID)
'            MsgBox currName & creator & " item number "
            deleteStatus = ""
' Return value
            ChemObjectWipe = ChemObjectWipe & currName & "::" & creator
' Change substrings here to match different object creators
            If ((InStr(creator, "ISIS") <> 0) Or (InStr(creator, "CHEM") <> 0) Or (InStr(creator, "MDL") <> 0)) Then
'                MsgBox "Adding to delete queue " & creator & "::" & currName
                ChemObjectWipe = ChemObjectWipe & "(ERASED)"
                structures.Add (currName)
            End If
            ChemObjectWipe = ChemObjectWipe & ";"
        Next shp
'        MsgBox "Count = " & Str(structures.Count)
        For itemnum = 1 To structures.Count
            embeddedItems(structures.Item(itemnum)).Delete
        Next itemnum
        End If
    Next currSheet
End Function

OLK Outlook Temp Folder

2008-11-20T17:54:00.001-08:00

The Outlook 2003 design team came up with some strange ideas of how to cache attachments. From Microsoft :

When you open file attachments that are considered safe, Outlook 2003 or Outlook 2007 puts these attachments in a subdirectory under the Temporary Internet Files directory as an additional precaution. When Outlook 2003 or Outlook 2007 first tries to use a temporary file, it examines the registry to determine whether the following value exists, depending on your version of Outlook.

Outlook 2003
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security

Value Name: OutlookSecureTempFolder
Data Type: REG_SZ
Outlook 2007
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security

Value Name: OutlookSecureTempFolder
Data Type: REG_SZ
If the value exists, and if the value contains a valid path, Outlook 2003 or Outlook 2007 uses that location for its temporary files.

If the registry value does not exist, or if it points to an invalid location, Outlook 2003 or Outlook 2007 creates a new subdirectory under the Temporary Internet Files directory and puts the temporary file in the new subdirectory. The name of the new subdirectory is unknown, is randomly generated, and takes on the following form, depending on your version of Outlook.

Outlook 2003
C:\Documents and Settings\username\Local Settings\Temporary Internet Files\OLKxxx
In this example, username is the user name that is used by the person who is currently logged on to the computer, and xxx is a randomly generated sequence of letters and numbers.

If you open an attachment, change the file, and try to "Save As..." you will notice the default folder is this OLKxxx folder. The OLK folder is hidden and you cannot use Explorer to browse to it without typing the address in the Path bar.

There are several downsides to this approach.

Users can save important changes to a hidden folder, cannot find them via Explorer, then wail about disappearing files and version confusion.

Locked files won't be deleted by Outlook upon close and will accumulate in the OLK folder creating a treasure trove of semi-secret documents hidden on your local hard drive.

The foldername is semi-random, so it is difficult to script changes to the directory in a company.

I wrote a vbs script to fix these problems that should be deployable as a logoff or logon script. It does not require admin rights and should work with limited users in XP. It does the following:

Find out what the current OLK directory is from the registry

Add Modify permissions to the directory via CACLS.exe, so accumulated attachments can be deleted

Reassign the OLK directory to a fixed path, create the new directory if it doesn't already exist

Remove Modify permissions from the new OutlookTempFolder directory.

Users will now get a "Read Only" error when trying to save changes to the OutlookTempFolder. The script leaves users with Read and Write privileges to allow Outlook to write the initial attachment to the directory. Clicking "Save" on a modified document generates a "Read-Only File" warning in Outlook 2003. If the user persists in trying to save to the directory, "Saving As..." results in a 0-byte file and error, since Outlook is creating the file, then Modifying it to add the data from the original file. Either way it is more difficult to do the wrong thing, rather than change directory to a valid save location.

Here is the script. May it save many gnashed teeth.


' Outlook 2003 Temp Folder Fix Script
' (c) Dave S. Nov 2008
' I assume no liability for use of this script.
' http://snippetsandsnails.blogspot.com/
' Please verify that it does what you want in a test environment first!
' Freely redistributable with this attribution notice
const HKEY_CURRENT_USER = &H80000001
const HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
Set StdOut = WScript.StdOut
myDomain = "WIDGETCO\"

'' Get OLK directory from registry as user 
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_ 
strComputer & "\root\default:StdRegProv") 
strKeyPath = "SOFTWARE\Microsoft\Office\11.0\Outlook\Security"
strValueName = "OutlookSecureTempFolder"
oReg.GetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue
OLKdir = "nonexistentdirectory"
OLKdir = strValue

' Set up the new location for OLK to reside
Set WshShell = CreateObject("WScript.Shell")
newOLK = WshShell.ExpandEnvironmentStrings("%HOMEDRIVE%") &  WshShell.ExpandEnvironmentStrings("%HOMEPATH%") & "\" & "OutlookTempFiles"
' Get current username
userName = myDomain & WshShell.ExpandEnvironmentStrings("%USERNAME%")

'' We have the OLK directory, now empty it 

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(OLKdir)
' Set colSubfolders = objFolder.Subfolders

If objFSO.FolderExists(OLKdir) Then
'    Wscript.Echo "OLK Folder " & OLKdir  & " exists."
    Set objShell = CreateObject("Wscript.Shell")
' Grant change rights to allow deletion of files    
    iRetVal = objShell.Run("%SystemRoot%\System32\Cacls.exe " & Chr(34) & newOLK & _
        Chr(34) & " /E /T /G " & userName & ":C", 0, True) 
    Set objShell = Nothing    
    Set files = objFolder.Files
    On Error Resume Next
    For each file In files
        OLKFile = OLKdir & "\" & file.Name
' Do not delete subdirs, only delete files that are not locked etc.
        If objFSO.FileExists(OLKFile) Then
            objFSO.DeleteFile OLKFile, True
        End If
        If Err.Number Then
'            Wscript.Echo "Failed deletion:" & OLKFile
            Err.Clear
        End If
    Next
Else
    Wscript.Echo "OLK Folder " & OLKdir & " does not exist, registry is in error."
End If

' Set up new OLK location

If objFSO.FolderExists(newOLK) Then
' Reset permissions on already existing folder    
    Set objShell = CreateObject("Wscript.Shell")
' Note /P replaces the user perms, /G additively adds perms    
   iRetVal = objShell.Run("%SystemRoot%\System32\Cacls.exe " & Chr(34) & newOLK & _
        Chr(34) & " /E /T /P " & userName & ":W", 0, True)
    iRetVal = objShell.Run("%SystemRoot%\System32\Cacls.exe " & Chr(34) & newOLK & _
        Chr(34) & " /E /T /G " & userName & ":R", 0, True) 
    Set objShell = Nothing
Else
' Create the folder and reset the registry
    WScript.Echo "Creating new Outlook Temp Directory: " & newOLK
    objFSO.CreateFolder(newOLK)
    strValue = newOLK
' Directory is created.  Now grant Read and Write but not Modify to prevent OLK saving
    Set objShell = CreateObject("Wscript.Shell")
    iRetVal = objShell.Run("%SystemRoot%\System32\Cacls.exe " & Chr(34) & newOLK & _
        Chr(34) & " /E /T /P " & userName & ":W", 0, True)
    iRetVal = objShell.Run("%SystemRoot%\System32\Cacls.exe " & Chr(34) & newOLK & _
        Chr(34) & " /E /T /G " & userName & ":R", 0, True)  
    Set objShell = Nothing        
    oReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,newOLK
    If Err = 0 Then
        oReg.GetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,finalValue
 '       WScript.Echo _
 '      "Can't set new directory - outlook folder location value is: " & finalValue
    Else 
 '       WScript.Echo "Error in creating key" & _
 '      " and String value = " & Err.Number
    End If
End If

Changing All Your Powerpoint Slides to a New Template

2008-08-11T17:33:00.000-07:00

So someone has decided that your current Powerpoint template sucks and needs to be replaced with a shiny new template. Next question is, how do you update the 500+ legacy powerpoint presentations to the new template with a minimal amount of pain?

Not VB! I should have bit the bullet and done this in C# but it evolved from a simple sub to a larger script. In any case, it works well enough and gets most slides right. The goal was to be able to tolerate slide decks with multiple masters and only replace the background on slides using the previous template. It's very chatty so redirect the output to a log file for large runs. Also, rename the new Design to something unique so you can tell the new slides from the old ones later in case you need to re-run the script.

Save the following code to a .vbs file and modify the top variables to reflect your own environment.

EnumDesigns is taken from http://skp.mvps.org/designs.htm
CheckFolders is from http://www.windowsdevcenter.com/examples/windows/vbscriptpr_code.html


' setNewMasterDesign.vbs
' This script will find ppts under the current directory, and set the template and
' design to the current corporate template.
' Makes duplicates of all slides found.
' Works with PPT 2003 on WinXP
' (c) Dave S. Aug 11 2008

Dim currDir, templateFile, suffix, masterReplaceString, newDesign
suffix=".ppt"
''''''''
' User defined variables
currDir = "."
' Where is the source template for the new master?
templateFile = "C:\mytemplates\Default.pot"
' Change this to whatever substring matches your old master slide design(s)
masterReplaceString = "Default"
' This is the name of the new Design to be applied to slides whose Design name matches masterReplaceString
newDesign = "FreshAndClean"


'''''''''''''
WScript.Echo "Start Time:" & Now

Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(currDir)
Set files = folder.Files

Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = true

WScript.Echo templateFile & " is being opened...."
Set masterPPT = objPPT.Presentations.Open(templateFile)
EnumDesigns(masterPPT)

' Search recursively for powerpoint files in the current directory
CheckFolder folder, suffix, masterReplaceString
objPPT.Quit

''''''''''''''''''''''''''''''''''''''
' Subs Begin
''''''''''''''''''''''''''''''''''''''

Sub CheckFolder(objCurrentFolder, suffix, masterReplaceString)
   Dim strTemp
   Dim strSearch
   Dim objNewFolder
   Dim objFile
   strSearch = suffix
   WScript.Echo "Current folder is: " & objCurrentFolder.Name
      'Recurse through all of the folders, go to bottom first
      For Each objNewFolder In objCurrentFolder.subFolders
              CheckFolder objNewFolder, suffix
      Next  
      For Each objFile In objCurrentFolder.Files
          strTemp = Right(objFile.Name, 4)
               If UCase(strTemp) = UCase(strSearch) Then
                   massagePPT objFile, masterReplaceString
               End If
      Next  
End Sub

' ppt file filtering is done in CheckFolder, not in massagePPT
Sub massagePPT(objFile, masterReplaceString)
       Dim fullfile
       fullFile = objFile.Path
       WScript.Echo fullFile & " being opened at " & Now
       Set objPresentation = objPPT.Presentations.Open(fullFile)
       objPresentation.Designs.Load templateFile
       With objPresentation
           If Not .HasTitleMaster Then .AddTitleMaster
       End With
       EnumDesigns(objPresentation)
       Dim MySlide
       Dim slideNum
       slideNum = 0
       WScript.Echo "Presentation: " & objFile.Name
       For Each MySlide In objPresentation.Slides
           slideNum = slideNum + 1
           Dim text
           Dim designName
           designName = ""
           With MySlide
                   WScript.Echo "Master:" & .Master.Name
                   WScript.Echo "Design:" & .Master.Design.Name
                   designName = .Master.Design.Name
                   WScript.Echo "SlideNum:" & slideNum
                   WScript.Echo "Layout:" & .Layout
           End With
           ' Test if the design name is the one we want to replace
           If InStr(1,designName,masterReplaceString) = 0 Then
               WScript.Echo "Skipping slide " & slideNum & " due to non-matching master design: " & designName
           Else
               WScript.Echo "Slide " & slideNum & " Master Design name matches: " & masterReplaceString
' This section controls which design elements will propagate to the target slides
' In my case, I care mostly about the background but want to leave out master shapes and color schemes
           With MySlide
               .FollowMasterBackground = True
'               .ColorScheme = objPresentation.SlideMaster.ColorScheme
               .DisplayMasterShapes = False
               .Design = objPresentation.Designs(newDesign)
               End With
           End If
       Next
       objPresentation.SaveAs(objFile.Path & "." & newDesign & ".ppt")
       objPresentation.Close
End Sub
      


Sub EnumDesigns(oPres)
   Dim lCtrA
   With oPres
       WScript.Echo "Number of applied templates: " & .Designs.Count
       For lCtrA = 1 To .Designs.Count
           WScript.Echo "Template Design name: " & .Designs(lCtrA).Name
           WScript.Echo vbTab & "Slide master name: " & .Designs(lCtrA).SlideMaster.Name
           If .Designs(lCtrA).HasTitleMaster Then
               WScript.Echo vbTab & "Title master name: " & .Designs(lCtrA).TitleMaster.Name
           Else
               WScript.Echo vbTab & "No Title master present"
           End If
       Next
   End With
End Sub