Software Salariman

2011-02-12

Negate Numbers as String in Gawk

I had to negate numbers in a data file. My initial script simply negated the required field. Here a test:

gawk "{ print -$1 }"
123
-123
123.45678
-123.457

I wanted to preserve all the digits after the decimal point so I tried printf() (the multiple double-quotes is to escape double-quotes in Windows CMD):

gawk "{ printf("""%10lf\n""", -$1) }"
123
-123.000000
123.45678
-123.456780
123.456
-123.456000

Hm, now the result was being padded to the right depending on the number of digits specified. Since I only wanted to negate the values and not do any arithmetic, a trick is to add or delete the leading minus sign from the input string depending whether the input value was positive or negative, respectively:

gawk "{ print $1<0 ? substr($1, 2) : """-""" $1 }"
123
-123
123.45678
-123.45678
-123
123
-123.45678
123.45678

2011-01-01

NTFS Compression versus Compressed (zipped) Folders

A New Year posting!

While playing with WMI to compress folders, I realised that NTFS compression is different from ZIP files. At the start, I wondered why my script didn't seem to create any ZIP files even though the call to SWbemObjectEx.Compress procedure was successful. Turns out that my test folder was being compressed in NTFS but I wasn't looking for it correctly; in Explorer, the folder's name is displayed in a blue colour and its Size on disk is smaller than its Size but its icon and type is the same as a regular folder. On the other hand, Compressed (zipped) folders which are just files that have been compressed, have a zipper and padlock icon. A great way to publicise a fundamental file system feature, eh?

References

2010-12-11

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

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)

2010-11-27

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.