Friday, February 12, 2010

sqlplus Wrapper Script For Linux

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.

Tuesday, February 9, 2010

How to Delete Embedded Excel Objects in VBA

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

Thursday, November 20, 2008

OLK Outlook Temp Folder

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
  • Monday, August 11, 2008

    Changing All Your Powerpoint Slides to a New Template

    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