Creating ZIP files with VBA / VBScript

I wanted to create a Windows scheduled task to regularly compress a log file on several servers. The simple solution would be to install a command-line ZIP program and write a CMD script. After some searching, it became more interesting to write a VBScript program that uses the Compressed (zipped) folder feature in the Windows Explorer.

The result is the ZipFile VBA and VBScript program at the end of this posting. I wrote and tested the VBA program first then converted it to VBScript. You can run the VBScript program by providing the path of the ZIP archive and the path of the file to compress.

The program first deletes any existing ZIP file with the same name and creates an empty ZIP folder (actually a file). Then it calls the Windows Shell CopyHere() method to add a new file into the ZIP folder. The CopyHere() method doesn't block (i.e. it returns control immediately to the script) so the program polls the ZIP folder once a second to check if a file has been added (the ZIP folder's Items.Count is incremented). Without this polling loop, the program ends before the file is added (you can test it by commenting out the polling loop statements and archiving a large file).

The numeric argument for the CopyHere() method is a bit-string to avoid displaying the Windows Compressing... progress window. However, I found that the progress window is still displayed but it doesn't seem to affect the ZIP archive when the program is run as a scheduled task (whew!).

References

• ZIP files
• Converting VBA Programs to VBScript using SED
• Folder.CopyHere Method

VBA Version

Attribute VB_Name = "ZipFile"
Option Explicit
Option Base 0

Declare Sub Sleep Lib "kernel32" (ByVal dwMiliseconds As Long)

'VBA add these references
'1. Microsoft Scripting Runtime
'2. Microsoft Shell Controls and Automation

Public Sub MakeZip(zipPath As String, filePath As String)
  MakeEmptyZip zipPath
  AddFile zipPath, filePath
End Sub

Private Sub AddFile(zipPath As String, filePath As String)
  Dim sh As Shell32.Shell, fdr As Shell32.Folder, cntItems As Integer 'cnt = Count
  Set sh = CreateObject("Shell.Application")
  Set fdr = sh.Namespace(zipPath)
  cntItems = fdr.Items.Count
  fdr.CopyHere filePath, 4 + 16 + 1024
  Do
    Sleep 1000
  Loop Until cntItems < fdr.Items.Count
  Set fdr = Nothing
  Set sh = Nothing
End Sub

Private Sub MakeEmptyZip(zipPath As String)
  Dim fso As Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(zipPath) Then
    fso.DeleteFile zipPath
  End If
  fso.CreateTextFile(zipPath).Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0))
  Set fso = Nothing
End Sub

'Entry MakeZip WScript.Arguments(0), WScript.Arguments(1)

VBSCript Version

Option Explicit


'VBA add these references
'1. Microsoft Scripting Runtime
'2. Microsoft Shell Controls and Automation

Public Sub MakeZip(zipPath, filePath)
  MakeEmptyZip zipPath
  AddFile zipPath, filePath
End Sub

Private Sub AddFile(zipPath, filePath)
  Dim sh, fdr, cntItems
  Set sh = CreateObject("Shell.Application")
  Set fdr = sh.Namespace(zipPath)
  cntItems = fdr.Items.Count
  fdr.CopyHere filePath, 4 + 16 + 1024
  Do
    WScript.Sleep 1000
  Loop Until cntItems < fdr.Items.Count
  Set fdr = Nothing
  Set sh = Nothing
End Sub

Private Sub MakeEmptyZip(zipPath)
  Dim fso
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(zipPath) Then
    fso.DeleteFile zipPath
  End If
  fso.CreateTextFile(zipPath).Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0))
  Set fso = Nothing
End Sub

MakeZip WScript.Arguments(0), WScript.Arguments(1)

Converting VBA Programs to VBScript using SED

Writing VBScript programs can be a pain without an IDE with early-binding to ensure that the name and arguments of procedures are correct and you have start the Microsoft Script Debugger to debug your code separately from your editor. How very 90s! On the other hand, MS-Office has an IDE for writing and debugging VBA programs. What's more, since VBScript is very similar to VBA (and VB), it can be simpler to write a VBA program first, export it as a BAS file then convert it to VBS file (still very 90s but slightly less painful).

Below is a simple text converter using sed (the Unix stream editor), followed by an explanation of each rule. To use these rules, copy and save them into a file such as bas2vbs.sed, then run it using sed -f bas2vbs.sed <file>.bas > <file>.vbs. You should be familiar with sed regular expressions to follow the rules. The conversion isn't exhaustive or perfect but it works well enough for me.

/^Attribute/d
/^Declare/d
/^Option Base/d
s/ As [^,)]*//g
s/Next [^ ]*/Next /
s/^'Entry //

General Syntactic Conversions

/^Attribute/d

An exported VBA file has one or more lines of metadata such as Attribute VB_Name="<module>". These metadata lines aren't supported in VBScript, so this rule deletes them.

/^Declare/d and Sleep

The Windows Scripting Host (WSH / WScript) includes a useful function called Sleep() which has an equivalent function in kernel32.dll. To make the kernel32.dll function visible in VBA, you declare it like this:

Declare Sub Sleep Lib "kernel32" (ByVal dwMiliseconds As Long)

then use it like this:

Sleep(1000)

VBScript doesn't support the Declare keyword, so that declaration is deleted. When the VBSscript script is run, the call to Sleep(n) function calls WScript.Sleep(n).

/^Option Base/d

In VBA, you can specify the default lowest index value for your variables. Coming from programming in C-style languages, I habitually define Option Base 0 so that my arrays start from index 0. In VBSript, array indexes start from 0 and the Option Base declaration is not supported, so that declaration is deleted from the VBS code.

s/ As [^,)']*//g

If you declare the type of your variables, the VBA IDE can auto-complete the class constants, properties or methods relating to your variables using early-binding. If you use Option Explicit, you have to declare variable names but all VBScript variables are type Variant so the type name after the variable is not required. This sed rule deletes the type name from statements (e.g. Dim x as type1, y as type2 becomes Dim x, y) and procedure definitions (e.g. Sub f (ByVal a as type3, ByVal b as type4) becomes Sub f (ByVal a, Byval b)).

s/Next [^ ]*/Next /

VBA allows you to specify the control variable to advance in a For...Next statement, e.g. the variable i in For i ... Next i. Specifying the control variable after Next is optional but I use it to make loops (especially nested ones) easier to read. VBScript doesn't allow a control variable after the Next keyword, so the variable string has to be deleted.

Inconsistently, the control variable is allowed after Next in For Each...Next statements in VBA and VBScript!

Environment or Library Conversions

s/New \([^ ]*\)/CreateObject("\1")/

You can create new ActiveX objects in VBA using the New keyword. This keyword isn't available in VBScript so you have to use the CreateObject() function instead. The rule just takes the string after the New keyword and makes it an argument for the CreateObject function. However, the two strings aren't necessarily the same (see below).

You can create new ActiveX objects in VBA using the New keyword. This keyword is available in VBSCript (though it's not listed) to create new instances of classes but you have to use the CreateObject() to create an ActiveX object in VBScript.

s/Shell32.Shell/Shell.Application/

A VBA project may include the Microsoft Shell Controls and Automation library in VBA to interact with the Windows Shell. To use the Windows Shell in VBA, you could write Set ws = New Shell32.Shell (the namespace for this library is Shell32 and Shell represents Windows Shell class). Since the New keyword is not available in VBScript, the CreateObject rule and this rule would write Set ws = CreateObject("Shell.Application"), where Shell is the name of the automation server and Application is type of object to create.

Convenience Conversion

s/^'Entry //

A VBScript module requires some entry point that takes command-line arguments or to run a procedure, for example DoSomething WScript.Arguments(0). You can't add this statement by itself into a VBA module (all statements have to be within a procedure) so this convenience rule takes any comment starting with 'Entry and removes that prefix, leaving you with a VBS statement.

References

• Windows Scripting 5.8 Array Function
• Windows Scripting 5.8 VBScript Variables
• VBA For...Next Statement
• Windows Scripting 5.8 For...Next Statement
• VBA For Each...Next Statement
• Windows Scripting 5.8 For Each...Next Statement
• How Object Creation Works in Visual Basic Components
• Windows Scripting 5.8 CreateObject Function
• Windows Scripting 5.8 Keywords (VBScript)

GameMaker 8.0: First Impression

Finished GameMaker (GM) Tutorial 1, the Fruit Game. The GM IDE is pretty basic and allows you to build a simple 2-D game without too much fuss. The generated executable is 2.3 MB, which is pretty good considering that the game engine is included.

After using other mainstream IDEs such as Visual Studio and NetBeans, my preferences for the IDE would be:

• Use a tab and property sheet for each resource type instead of having a child window appear for each object.
• Leave the IDE open when testing a game instead of iconifying it.

Not major issues and there's still plenty to explore.

See Also

• GameMaker

Automatically Update Last Row in Range Formulas

In OpenOffice Calc, worksheet formulas that apply a range of rows, such as SUM(), need to be updated when an extra row is added just above the cell with that formula. For example, if you start the following worksheet values and formulas ...

  A
1 4
2 7
3 9
4 =SUM(A1:A3)

... then when you insert a new row between rows 3 and 4, OO-Calc does not automatically update the second argument to the SUM() formula (Note: MS-Excel 2003 does). A solution is to use the OFFSET formula to automatically update the second argument like this: =SUM(A1:OFFSET(A4;-1;0;1;1)). In this example, the OFFSET returns a reference to a cell one row above cell A4.

See Also

• OFFSET Function

Starting Excel 2003 Faster

I start two or three Excel 2003 sessions at the same time so that I can use multiple displays and work on a couple of tasks at a time. To start a new Excel session, you launch it from the Windows Start menu instead of opening an Excel file in Explorer (opening an Excel file loads it in the active Excel session). A new Excel session has, by default, a Task Pane (a panel titled 'Getting Started') and a new workbook, both of which I rarely use, so I close them before opening an existing file.

If you don't want the Task Pane opened by default, open Excel's Options dialog and uncheck View, Show, Startup Task Pane.

If you don't want a new workbook by default, create a new shortcut to start Excel with the /e argument and open Excel with that shortcut.

See Also

• Customize how Excel starts

Name Log Files in a Consistent Format

I expect developers to program like I do, which didn't happen this week.

The task is to concatenate log files in a folder into a single file to import into a database. The Windows command to do the job is for /f %a in ('dir /b *.csv') do type %a >> dest.csv. (dir /b lists entries in a folder without any heading.) While testing using echo in place of type, I noticed that due to the way dir sorts file, the script would concatenate log files in this order: Log-02.csv, Log-03.csv, ..., Log.csv i.e. the first log file last. This order seems to correspond with the characters in the ASCII table.

(Aside: Windows Explorer lists Log.csv first because Explorer doesn't use ASCII sort order.)

Of course, just to get the job done, the work-around is to rename Log.csv to Log-01.csv before running the script.

It threw me that developers wouldn't name log files consistently.

SetLocal EnableDelayedExpansion in CMD Scripts

In a Windows CMD for loop, variables within the loop aren't, by default, resolved into a value for every iteration of the loop. Here's a script that should list all files in a folder.

@echo off
for /f %%a in (*) do (
  set test=%%a
  echo %test%
)

However, when you run the script, only the first file found is output (the variable test is only set in the first iteration of the loop). Turns out that I'm the n'th generation of CMD programmers to trip over this issue. It seems that CMD only processes the for (...) statement once, so the variable test is only set once. To get CMD to reprocess your variable in each iteration, put your code into a setlocal EnableDelayedExpansion ... endlocal block and delimit with exclamation marks any variables that CMD should reprocess in each iteration of the loop (hence the unintuitive name of the block).

@echo off
setlocal EnableDelayedExpansion
for %%a in (*) do (
  set test=%%a
  echo test=!test!
)
endlocal

I couldn't find an official MS documentation of EnableDelayedExpansion or the use exclamation marks for variables.

Restoring Standard Outlook Forms

If you can't see any of the standard Outlook forms, there are two solutions:

• Clear your Outlook forms cache:
1. In Outlook, select menu item Tools, Options.
2. In Options dialog, select Other tab.
3. In Other tab, press Advanced Options… button.
4. In Advanced Options dialog, press Custom Forms… button.
5. In Custom Forms tab, press Manage Forms… button.
6. In Forms Manager dialog, press Clear Cache button, then press the Close button.
7. Close all the Option dialogs and try accessing the form again.
• If the above doesn't work, a more risky solution is to get Outlook to recreate its cache file:
  1. Exit Outlook.
  2. Use Windows Explorer to open the temporary forms folder. On my WinXP PC, the path is C:\Documents and Settings\<user>\Local Settings\Application Data\Microsoft\FORMS.
  3. Rename or delete the file FRMCACHE.DAT.
  4. Start Outlook again.

See Also

• How to restore default Contact Form

Fixing Broken Image Links in Outlook

If Outlook stops displaying images embedded in HTML e-mail, the fix is to delete files in its temporary file folder. On my WinXP computer, that temporary folder is: C:\Documents and Settings\<user>>\Local Settings\Temporary Internet Files\OLK<random>.

To find the location of Outlook's temporary folder on your PC, open an e-mail message with a Word or Excel attachment, open the attachment and save it. The default location displayed in the application's Save dialog is the location of the temporary folder. This technique works for Word or Excel but other applications may instead show the last folder you used.

For the motivated, the temporary folder's path is stored in the registry in this key: HKCU\Software\Microsoft\Office\<Office version>\Outlook\Security\OutlookSecureTempFolder.

Notes:

• You can't see that folder using Windows Explorer by browsing the parent folder, C:\Documents and Settings\<user>>\Local Settings\Temporary Internet Files. Instead, to see the contnts of the folder, you have to type in its path in the Explorer's address bar.
• If you use a CMD console, you can list that folder using the dir command.
• If you make a shortcut to that folder and try to use the shortcut, Windows shows this warning: This page has an unspecified potential security risk..

See Also

• Attachments remain in the Outlook Secure Temporary File folder when you exit Outlook 2003 or Outlook 2007

Invisible USB Drive

If you plug in a USB drive and Windows doesn't assign a letter to it, you can assign a letter yourself using the Disk Management console:

1. Run diskmgmt.msc
2. In Disk Management window, locate the box corresponding to your USB drive.
3. Select context menu item Change Drive Letter and Paths....
4. Assign a letter to your drive.

This problem happened after I had mapped a remote drive to Z: then plugged in a USB key. I could see the USB key and eject it in the Windows Start Bar but no letter was assigned to it.

See Also

• Assign USB Drives to Folder
• USB Flash Drive is invisible

Vim Delete Lines With Regex

To use vim to delete all lines that have a specific string: :g/s1/d. To delete all lines that don't have a specific string, add an exclamation mark before the string: :g!/s1/d. If there are alternative strings to match (logical OR), insert an escaped vertical bar, e.g. :g/s1\|s2/d.

VBA One-liner to Concatenate Excel Cell Values

Here's a VBA one-liner to concatenate a column of Excel cell values, with a separator, into one string: Join(Application.Transpose(<column range>), ","). The column range is an Nx1 array, Transpose() returns a one-dimensional N-element array and Join() function a one-dimensional array as the first parameter.

To concatenate values from a row of cells, you have to use Transpose() twice like this: Join(Application.Transpose(Application.Transpose(<row range>)), ","). Excel's Range always returns a two-dimensional array (in this case, a row is a 1xM array) and you flatten the two-dimensional array by calling Transpose() twice. There doesn't seem to be a built-in function to flatten two-dimensional arrays.

References

• The Join Function
• WorksheetFunction.Transpose Method

Avoiding #N/A in Range VLOOKUP

I was using VLOOKUP to put percentages in buckets. Here's the lookup table:

   a b
1  0 1
2 25 2
3 50 3
4 75 4

And here's the formula I added to my data sheet: =VLOOKUP(input, A1:B4, 2, TRUE). When the fourth parameter, Range_Lookup is set to TRUE, the function will match a range instead of an exact input value. If the input value was greater than 0 (e.g. 0, 25.5), VLOOKUP returns a bucket number, as expected. My input data was dirty and included negative numbers (e.g. -0.5, -1), so VLOOKUP would then return #N/A and dependant formulas would show an error.

One solution was enclose the input value in an IF function and avoid negative input values: =VLOOKUP(IF(input < 0, 0, input), A1:B4, 2, TRUE).

After reading about VLOOKUP, Range_Lookup parameter ...

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

... another way to avoid the #N/A error is to provide a dummy value smaller than all the negative values and an extra bucket:

    a b
1 -99 0
2   0 1
3  25 2
4  50 3
5  75 4

References

• VLOOKUP

Rudimentary C# COM Interop Client for Excel

I started writing some basic C# COM Interop clients for Excel to keep a hand in C# and be able to develop more sophisticated applications than VBA could provide. Here are some basic things I learnt and a complete working example to read data from an Excel workbook.

Configure your project

1. Add to this project reference in the IDE: GAC (or COM) Microsoft.Office.Interop.Excel.
2. Add to the source code:

   using System.Runtime.InteropServices;
   using Microsoft.Office.Interop.Excel;
   

Clean up COM References

After your program has finished using Excel, it should release all references to COM objects otherwise the Excel automation process remains running (check the Task Manager process list for Excel.exe). It seems a bit back-to-front to mention this first but I like to know how free unused resources before I start using them. Here is a discussion and statements you should add to your code.

Optional Parameters and Type.Missing

C# (pre-2010) doesn't support optional parameters so you have to supply an argument for all parameters in exposed methods. Since many frequently-used Excel methods require a lot of parameters, you have to tolerate verbose function calls in your C# source code.

Often you would use the default value for most parameters and you can simply provide the value Type.Missing. If you have Visual Studio Tools for Office (VSTO), you can use a global variable called missing. In the example below, Workbooks.Open() requires 15 arguments but I only provide the file name and use the default value for the remaining parameters.

      Excel.Application app = new Excel.Application();
      Excel.Workbook wbk = app.Workbooks.Open(
        path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Getting Data From Excel - Indexers, Arrays and Accessors

You can access Worksheets, Ranges and their values in VBA with this syntax: object.property or collection(index). Using the Excel object model in C# via COM Interop requires knowing about indexers, arrays and accessors.

Items in Excel collections (e.g. Worksheets) can be accessed using indexers, e.g. Workbook.Worksheets[1]. Like their VBA counterparts, some indexers support different types of arguments. The return value is always of type object so you have to cast the return result. For example:

      Excel.Worksheet wks0 = (Excel.Worksheet)wkss["Sheet1"];
      Excel.Worksheet wks1 = (Excel.Worksheet)wkss[1];

For me, an Excel indexer is syntactic sugar for an accessor method called get_Item(). (Note: The SharpDevelop IDE doesn't list this accessor in the list of object methods but VS C# 2008 Express does):

      Excel.Worksheet wks2 = (Excel.Worksheet)wkss.get_Item("Sheet1");
      Excel.Worksheet wks3 = (Excel.Worksheet)wkss.get_Item(1);

Another thing to note is that the indexers return a type object so you have to cast the return result.

Once you have a reference to a Worksheet, you can refer to individual cells using its Cells array (and remember to cast the return result):

      Excel.Range cell0 = (Excel.Range)wks0.Cells[1,1];
      Excel.Range cell1 = (Excel.Range)wks0.Cells.get_Item(1,1);

You can also get a single cell using the Worksheet's Range property. Unlike the Cells property, Range is a parameterised property, so you have to use the Worksheet method get_Range(object Cell1, object Cell2):

      Excel.Range rng0 = wks0.get_Range("A1", Type.Missing);

A common way to refer to a set of cells is to provide the top left and bottom right cell to the Worksheet's get_Range() accessor:

      Excel.Range rng1 = wks0.get_Range("A1", "A6");

If you try to get a Range using the function or array syntax, e.g. Excel.Range rng1 = wks0.Range("A1", "A6"); or Excel.Range rng0 = wks0.Range["A1","A6"];, you get a compile-time error: Property, indexer, or event 'Range' is not supported by the language; try directly calling accessor method 'Excel._Worksheet.get_Range(object, object)', which I take it to mean that Range is exposed as a property but can only be used as a method since C# (pre-2010?) doesn't support parameterised properties.

After getting a reference to a Range, you can get its data using either the get_Value() accessor or Value2 property (like Range, the Value property is a parameterised property). Finally, since a Range object can refer to a single cell or a set of cells, you have to know whether you are retrieving a single object or an object array from a Range.

      string s0 = (string)rng0.get_Value(Type.Missing);
      string s1 = (string)rng0.Value2;
      object[,] arr0 = (object[,])rng1.get_Value(Type.Missing);
      object[,] arr1 = (object[,])rng1.Value2;

Complete Example

Putting all that information together, here is the complete code for a rudimentary C# COM Interop client to read data from cells A1:A6 in an Excel workbook. Note that some statements are redundant and are there to illustrate different ways of getting the same result.

The program's output is:

$A$1:$A$6
1,1 Text:Text
2,1 1:1
3,1 5.678:5.678
4,1 0.5:0.5
5,1 1/06/2010 12:00:00 AM:40330
6,1 21.5078:21.507812345
Check Excel.exe in Task Manager . . .

The program outputs both a cell's Value and Value2 property. As you can see, cell A5's Value is a date while its Value2 property is a number, and cell A6's Value is a currency amount to four decimal points while its Value2 property is a double. Just before it exits, all COM references to Excel should be cleaned up and the program prompts the user to check the Task Manager, where there should not be any Excel.exe processes in the process list.

//Add to project reference:
//GAC, Microsoft.Office.Interop.Excel
using System;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ExcelAutomation1 {
  class Program {
    public static void Main(string[] args) {
      string path = @"C:\Users\hung\Documents\Programming\C#\ExcelAutomation1\ExcelAutomation1\Test.xls";
      Excel.Application app = new Excel.Application();
      Excel.Workbook wbk = app.Workbooks.Open(
        path, false, true, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      Excel.Sheets wkss = wbk.Worksheets;
      Excel.Worksheet wks0 = (Excel.Worksheet)wkss["Sheet1"];
      Excel.Worksheet wks1 = (Excel.Worksheet)wkss[1];
      Excel.Worksheet wks2 = (Excel.Worksheet)wkss.get_Item("Sheet1");
      Excel.Worksheet wks3 = (Excel.Worksheet)wkss.get_Item(1);
      Excel.Range cell0 = (Excel.Range)wks0.Cells[1,1];
      Excel.Range cell1 = (Excel.Range)wks0.Cells.get_Item(1,1);
      Excel.Range rng0 = wks0.get_Range("A1", Type.Missing);
      //Excel.Range rng1 = wks0.Range("A1", "A6"); //Not supported
      //Excel.Range rng1 = wks0.Range["A1","A6"]; //Not supported
      Excel.Range rng1 = wks0.get_Range("A1", "A6");
      Console.WriteLine(rng1.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
      string s0 = (string)rng0.get_Value(Type.Missing);
      string s1 = (string)rng0.Value2;
      object[,] arr0 = (object[,])rng1.get_Value(Type.Missing);
      object[,] arr1 = (object[,])rng1.Value2;
      int cntRow = arr0.GetLength(0);
      int cntColumn = arr0.GetLength(1);
      for (int r = 1; r <= cntRow; ++r) {
        for (int c = 1; c <= cntColumn; ++c) {
          Console.WriteLine(r.ToString() + "," + c.ToString() + " " + arr0[r,c] + ":" + arr1[r,c]);
        }
      }
      rng0 = rng1 = null;
      cell0 = cell1 = null;
      wks0 = wks1 = wks2 = wks3 = null;
      wkss = null;
      
      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();

      wbk.Close(Type.Missing, Type.Missing, Type.Missing);
      Marshal.FinalReleaseComObject(wbk);
      app.Quit();
      Marshal.FinalReleaseComObject(app);
      Console.Write("Check Excel.exe in Task Manager . . . ");
      Console.ReadKey(true);
    }
  }
}

References

• How to properly clean up Excel interop objects in C#
• Named and Optional Arguments (C# Programming Guide)
• Type.Missing, C#, and Word
• Programming Office Applications Using Visual C#
• Stupid C# tricks: Working with indexers
• Parameterized Properties and the Office Interop API Extensions
• Parameterized Properties in C# and the mystery of Value2 in Excel

Excel Inplace (or embedded) without ActiveWorkbook

An Excel workbook in a SAP InPlace view (or embedded in within a window) stopped working, displaying a run-time 1004 error: Method 'Sheets' of object '_Global' failed. The problem was caused by a UserForm dialog in that workbook which the user can open to enter a value. The code in the dialog then filtered the data in all the sheets based on the user's input. The code failed when it tried to call the Sheets() method.

The Sheets() is a short-cut for Application.ActiveWorkbook.Sheets(), so when a workbook is embedded in a view and loses focus (such as when the dialog was opened), Application.ActiveWorkbook doesn't have a valid value so the Sheets() method fails. When the workbook is opened in an Excel window, there is always an active workbook (and a valid ActiveWorkbook value).

The VBA code was part of the workbook, not in a separate add-in file, so a solution was to use ThisWorkbook.Sheets(). Another solution could be to use Application.Workbooks(1).Activate.

There is also an Application.IsInPlace property which returns TRUE if Excel is embedded within another window.

2010-05-20: Hm ... I could have also used ThisWorkbook.Activate to ensure that there was always an active workbook.

References

• Displaying Data in Excel Inplace View
• ActiveWorkbook Property
• ThisWorkbook Property
• IsInPlace Property
• Macro to Determine If Excel Is Open for In-Place Editing

Start External Program to Debug a Class in Visual C# 2008 Express

While playing with an automation server for MS-Excel, I wanted to debug my class method by starting Excel in Visual C# Express and using the formula in a worksheet. After the usual couple of minutes of hunting through the project property sheets, it dawned on me that you can't define the start program in the Visual C# Express IDE.

So I reopened my project in SharpDevelop (another .Net IDE), defined Start External Program in my project and continued playing with my automation server.

Checking the project files, I found that SharpDevelop added or modified the StartAction and StartProgram properties to the project's .csproj file:

  <PropertyGroup Condition=" '$(Configuration)' == 'Debug' ">
    <StartAction>Program</StartAction>
    <StartProgram>D:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE</StartProgram>
  </PropertyGroup>

Then I reopened the solution in Visual C# Express and found that I could start Excel to debug the class, too! It seems that Visual C# Express debugger and MSBuild.exe tool does support the feature but the IDE doesn't allow you to edit the Start Action parameter.

References

• How to: Change the Start Action for Application Debugging
• SharpDevelop

Prevent VBScript in Custom Outlook Form From Executing

My custom Outlook form added some canned text in the message object in the Item_Open() function. Annoyingly, when I open the form in design mode, the Item_Open() is called, cluttering up the message object. It turns out that you have to hold down the Shift key when opening a form to prevent VBScript in the form from executing.

Holding down the Shift key while starting MS-Access also prevents start-up options from executing, so it is a somewhat consistent feature in MS-Office.

References

• Microsoft Outlook Custom Forms, Tips
• 201087: How to update existing items in an Outlook folder to use a new custom form
• Ignore startup options, Microsoft Office Access 2003

Adding Text To Message Body in Outlook Custom Form

I had a simple request to add some canned text to the message body of a Outlook custom form when the form was first opened, so I did this in VBScript:

Sub Item_Open()
  ...
  Item.HTMLBody = "Blah blah blah"
  ...
End Sub

Yet when I created a new item, the message body was empty! The same problem occurs with Item.Body. After the usual bit of trial-and-error, I discovered that the text only appears if you use HTMLBody or Body on the RHS of a statement:

  Dim strTest
  strTest = Item.HTMLBody
  Item.HTMLBody = "Blah blah blah"

More concisely:

  Item.HTMLBody = "Blah blah blah" & Item.HTMLBody

The text also appears if I display a message box after I add text to the message box.

It seems like the HTMLBody or Body field is not instantiated or refreshed unless it is used.

References

• Description of the Message or Notes field in Outlook 2002

Outlook To: Field Unexpectedly Shrinks

While developing a custom Outlook form, I found that the To: field would unexpectedly shrink to a few pixels tall and pull the remaining controls in the form upwards. The problem occurred when I added Item.Recipients.Add "test" in my VBScript code to populate the To: field with a dummy e-mail address then clicked in the To: field. (I was using a dummy address to avoid accidentally sending test messages to unsuspecting recipients.) It seems as if putting the cursor into that field while Outlook is resolving the non-existent address (i.e. before it marks that address with a wavy red underline) causes the problem. The problem never happens if you populate the To: field manually with a dummy address, probably because Outlook is resolving the address as you type.

In my case, I found that typing Ctrl+K (Check Names) in the To: field restores the height of the field.

The solution during testing is to use a real address (such as your own).

Notes on Developing Outlook 2003 Forms

I just finished developing a simple e-mail workflow system implemented using Outlook forms and VBScript and thought I should jot down some things that made development easier:

Configuring Your Development Environment

• Disable Outlook Autosignature, otherwise your signature is inserted into the message object each time you create a new form and you cannot clear the text in the message object using the Form Designer. While you can clear the message object using VBScript using Item.Body = "", it's annoying to have so much noise during development.
• If you use VBScript in your form definition, don't send the form definition in each item, otherwise you cannot run VBScript in your item. In the Form Designer's Properties tab, ensure that Send form definition with item is unchecked.
• Each form definition has a unique Message Class. To ensure that you are opening items based on your form definition in Outlook, add the Message Class field into your Outlook View using the following steps:
  1. Select menu item View, Arrange By, Current View, Customize Current View. Outlook should open the Customize View dialog.
  2. In Customize View: dialog, select Fields... button. Outlook should open the Show Fields dialog.
  3. In Show Fields dialog, select All Mail Fields in the drop down list of available fields.
  4. Select and add Message Class to your view.
• Create a desktop shortcut to use your form. Using the shortcut is faster than the alternative of using the menu item Tools, Forms, Choose Form and then picking your form from the Choose Form dialog. Plus, when you finish development and publish your form, you can send the shortcut file to your users.

One-Off Forms

If you have VBScript in your form definition, you must avoid making a one-off forms (i.e. an item which no longer references its form definition) because Outlook security does not allow VBScript to run in one-off forms. A one-off form can be identified if its Message Class is not the same as the form definition's and / or its size is much larger than expected. For instance, if you define a form named IPM.Note.Test, instances of this form should have the same message class and a size of about 5 to 6 Kbytes, while a one-off form may be called IPM.Note and / or have a size of in tens of Kbytes. If you think the rules for identify one-off items are a bit vague, read this.

There's a fair bit of documentation and discussion about one-off items online because that's how Outlook forms were first deployed before they were regarded as a security risk. I ignored one-off items (which seemed quite useless nowadays) and treated my form definition as my 'program'. During development, my form definition is published in Personal Folders and I always use that definition to create an item. I also found that using the form designer's Tools, Run This Form menu item creates one-off items.

VBScript Development

When writing VBScript for Outlook forms, you are stuck with the minimal Script Editor which doesn't have features such as highlighting syntax, auto-completion or a debugger; it's so minimal that it doesn't have a Save button or allow you to choose another font! At least changes in the Script Editor are always saved when you publish, so you can leave the Script Editor window open during a coding session.

Script Editor doesn't have a debugger, so you have to use the Outlook Script Debugger, which is the Web Debugger in read-only mode (!) that doesn't allow you to modify Outlook VBScript. The only silver lining that you can inspect objects and find out what properties they have in the script debugger.

Final Thoughts

Developing Outlook Forms with VBSCript was somewhat painful because of the lack of tool support. I hope the tips in this article would be useful for others starting on a similar project.

References

• OutlookCode.com by Sue Mosher has lots of information about programming Outlook, though I wish the site's font isn't so eye-wateringly small and faint.
• XCLN: Custom Form with AutoSignature Cannot Be Modified to Remove the Signature
• How to simplify the process to open Organizational Forms in Outlook
• Description of form definitions and one-off forms in Outlook 2002
• About the Outlook script debugger

Visio Shape Positioning Annoyance

In technical diagrams, I align the positions and dimensions of shapes with a grid so that the spacing and sizes are consistent. Using Visio 2007, after I resize a page using File, Page Setup, Page Size, Size to fit drawing contents, every shape's position is slightly modified. For example, if I position a shape at x = 40mm, y = 30mm, the shape's position becomes something like x = 40.0847mm, y = 30.1051mm.

My workaround is to manually resize the page size by holding the Ctrl key and using the pointer to move the horizontal or vertical edge of a page; then the shapes' positions aren't altered.

Vista Ctrl+Space IME Toggle Workaround

If you start using Windows Vista Input Method Editor (IME) for Chinese, you may find that you can no longer use Ctrl+Space in some applications (e.g. to select a column in Excel, to activate autocomplete in IDEs such as VBA, Netbeans or Visual Studio, or to activate Firefox Ubiquity). One workaround is to define a different hotkey, such as Ctrl+Shift+F12, to toggle the IME.

If you have only one keyboard service and find that Ctrl+Space mysteriously starts to act as a toggle for an unselected IME (in my case, Chinese (Traditional, Taiwan)), the workaround is to add a keyboard service and define a different IME hotkey. You must define a hotkey for toggling the IME, otherwise the IME is activated using the default Ctrl+Space.

Reference

• No CONTROL over the SPACE being filled with CTRL+SPACE

VBA Is*() Functions Only Apply To Variant Types

VBA has several informational functions prefixed with "Is", such as IsNull(), IsMissing() and IsEmpty(), that only work with Variant types. I didn't realise it until I tried to apply them to non-Variant variables and noticed that they didn't work as I expected or always returned the same result.

For instance, if you define optional parameters for a procedure, you can only test if they are used in the procedure call using IsMissing() if the parameter is a Variant type. Using any other type of parameter with IsMissing() always returns False because all other parameter types have a default value supplied by the compiler or by the programmer.

Similarly, only Variant variables can have a Null value so that they can be tested by the IsNull() function. Object types can have a Nothing value, not Null, and can only be tested using the Is operator.

In the same vein, only Variant variables can be uninitialised and tested with IsEmpty(). All other variable types are initialised to some value (e.g. Objects are initialised to Nothing, Integers are initialised to 0).