: ) wonderful world ( :

This should be run in Word’s VBA project…

Sub WordFromExcel()
   ' one single Excel is open with the workbook we want to read from
   Set ex = GetObject(,"Excel.Application")
   Set book = ex.ActiveWorkbook
   Set sheet = book.Sheets(1)

   With Selection
      ' insert some heading
      .ParagraphFormat.Style = Word.WdBuiltinStyle.wdStyleHeading1
      .TypeText Text:=sheet.Name
      .TypeParagraph

      ' insert text from the cells
      .ParagraphFormat.Style = Word.WdBuiltinStyle.wdStyleNormal
      .TypeText Text:=sheet.Cells(1, 1).Value
      .TypeParagraph
   End With

   Set sheet = Nothing
   Set book = Nothing
   Set ex = Nothing
End Sub

First of all, here’s the lisp I’ve used:

% clisp --version | grep -v ^Machine
GNU CLISP 2.48 (2009-07-28) (built on stnt067 [192.168.0.1])
Software: GNU C 3.4.5 (mingw-vista special r3)
gcc -mno-cygwin -O2 -W -Wswitch -Wcomment -Wpointer-arith -Wimplicit -Wreturn-ty
pe -Wmissing-declarations -Wno-sign-compare -Wno-format-nonliteral -O2 -fexpensi
ve-optimizations -falign-functions=4 -D_WIN32 -DUNICODE -DDYNAMIC_FFI -I.  -lint
l -lreadline -ltermcap -lavcall -lcallback -luser32 -lws2_32 -lole32 -loleaut32
-luuid -liconv -lsigsegv
SAFETY=0 HEAPCODES STANDARD_HEAPCODES GENERATIONAL_GC SPVW_BLOCKS SPVW_MIXED TRI
VIALMAP_MEMORY
libsigsegv 2.6
libiconv 1.11
libreadline 5.2
Features:
(READLINE REGEXP SYSCALLS I18N LOOP COMPILER CLOS MOP CLISP ANSI-CL COMMON-LISP
LISP=CL INTERPRETER SOCKETS GENERIC-STREAMS
 LOGICAL-PATHNAMES SCREEN FFI GETTEXT UNICODE BASE-CHAR=CHARACTER PC386 WIN32)
C Modules: (clisp i18n syscalls regexp readline)
Installation directory: C:\Program Files\clisp-2.48\
User language: ENGLISH

%

I have also MingW (5.1.6) and MSYS (1.0.1) installed, Witchs Hat icon downloaded, CLisp dlls copied from the base subdirectory under the installation path. This is the development tree in clean form:

% find
.
./bootstrapper.cc
./bootstrapper.rc
./clisp-dlls
./clisp-dlls/libiconv-2.dll
./clisp-dlls/libintl-8.dll
./clisp-dlls/readline5.dll
./Makefile
./message.lisp
./witchs-hat.ico
%

Under clisp-dlls, there are the mentioned dlls of CLisp. The file message.lisp contains the program you’d like to run (or ship or whatever). Finally, the bootstrapper files are up to provide an exe with company info, icon, copyright, etc. as an entry to the lisp program. The important bit here is that you don’t want a command prompt window popping up before startup.
Let’s see the files:

% cat message.lisp
(use-package "FFI")
(def-call-out messagebox
  (:name "MessageBoxA") (:library "user32.dll")
  (:arguments (hwnd int) (text c-string) (capt c-string) (type uint))
  (:return-type int)
  (:language :stdc))

(defun main ()
  (messagebox 0 "Your hacking starts... NOW!" "Demo MsgBox" 0)
  (quit))
% cat -A Makefile
all:^Imsgbox-app.exe msgbox-app.img$
^Imkdir -p shipment$
^Icp msgbox-app.exe shipment$
^Icp msgbox-app.img shipment$
^Icp clisp-dlls/* shipment$
$
msgbox-app.img:^Imsgbox-app.img.exe$
^Icp $< $@$
$
msgbox-app.img.exe:^Imessage.lisp$
^Iclisp -q -norc -x \$
"(load \"message.lisp\") \$
(ext:saveinitmem #P\"./msgbox-app.img.exe\" \$
                 :executable t \$
                 :norc t \$
                 :init-function #'main)"$
$
bootstrapper.res:^Ibootstrapper.rc witchs-hat.ico$
^Iwindres $< -O coff -o $@$
^I$
msgbox-app.exe:^Ibootstrapper.cc bootstrapper.res$
^Ig++ -mwindows $^ -o $@$
$
clean:$
^Irm -rf msgbox-app.exe bootstrapper.res \$
^Imsgbox-app.img msgbox-app.img.exe shipment$
% cat bootstrapper.rc
ID ICON "witchs-hat.ico"
1 VERSIONINFO
FILEVERSION     1,0,0,0
PRODUCTVERSION  1,0,0,0
BEGIN
  BLOCK "StringFileInfo"
  BEGIN
    BLOCK "080904E4"
    BEGIN
      VALUE "CompanyName", "MsgBox Products"
      VALUE "FileDescription", "Demo MsgBox"
      VALUE "FileVersion", "1.0"
      VALUE "InternalName", "msgbox-app"
      VALUE "LegalCopyright", "Grault"
      VALUE "OriginalFilename", "msgbox-app.exe"
      VALUE "ProductName", "Demo MsgBox"
      VALUE "ProductVersion", "1.0"
    END
  END

  BLOCK "VarFileInfo"
  BEGIN
    VALUE "Translation", 0x809, 1252
  END
END
% cat bootstrapper.cc
#include <windows.h>

int WinMain(HINSTANCE a0, HINSTANCE a1, LPSTR a2, int a3)
{
  STARTUPINFO si;
  PROCESS_INFORMATION pi;
  ZeroMemory(&si,sizeof(STARTUPINFO));
  si.cb = sizeof(STARTUPINFO);
  CreateProcess("msgbox-app.img", "",
     NULL,NULL,TRUE,CREATE_NO_WINDOW,NULL,
     NULL,&si,&pi);
  // version and company details are in the bootstrapper exe
  // it's kinda better to show these e.g. in process explorer
  // and image file as a subprocess
  // feel free to comment the following line to leave img only
  WaitForSingleObject(pi.hProcess, INFINITE);
  return 0;
}
%

These are the files, and here are the results of a make:

% make
windres bootstrapper.rc -O coff -o bootstrapper.res
g++ -mwindows bootstrapper.cc bootstrapper.res -o msgbox-app.exe
clisp -q -norc -x \
"(load \"message.lisp\") \
(ext:saveinitmem #P\"./msgbox-app.img.exe\" \
                 :executable t \
                 :norc t \
                 :init-function #'main)"
;; Loading file message.lisp ...
;; Loaded file message.lisp
T
;; Wrote the memory image into .\msgbox-app.img.exe (5,088,157 bytes)
Bytes permanently allocated:             92,512
Bytes currently in use:               2,188,704
Bytes available until next GC:          544,546
2188704 ;
544546 ;
92512 ;
1 ;
52160 ;
156001
cp msgbox-app.img.exe msgbox-app.img
mkdir -p shipment
cp msgbox-app.exe shipment
cp msgbox-app.img shipment
cp clisp-dlls/* shipment
% find
.
./bootstrapper.cc
./bootstrapper.rc
./bootstrapper.res
./clisp-dlls
./clisp-dlls/libiconv-2.dll
./clisp-dlls/libintl-8.dll
./clisp-dlls/readline5.dll
./Makefile
./message.lisp
./msgbox-app.exe
./msgbox-app.img
./msgbox-app.img.exe
./shipment
./shipment/libiconv-2.dll
./shipment/libintl-8.dll
./shipment/msgbox-app.exe
./shipment/msgbox-app.img
./shipment/readline5.dll
./witchs-hat.ico

Here’s a picture how this all looks like on a Windows Server 2003 Standard Edition without any kind of lisp installed. And the entries in Process Explorer.

Some sort of shipment : )

show up in Process Explorer

Obviously one can develop a much more sophisticated bootstrapper. This one represents only the possibility of releasing software written in Lisp. Assembling these output files together into an installer could be the next step : ))

And finally, you can get either the binaries or the source files by sending a mail to:

% echo gra.rtebsrpehbf.ferfh@gyhnet | rev | tr '[a-z]' '[n-za-m]'

Grouping with sed:

$ cat nrs.txt
01
02
03
04
05
06
07
08
09
10
11
12
$ cat nrs.txt | sed -n -e 'N;s/\n/ /g;p'
01 02
03 04
05 06
07 08
09 10
11 12
$ cat nrs.txt | sed -n -e 'N;N;s/\n/ /g;p'
01 02 03
04 05 06
07 08 09
10 11 12
$ cat nrs.txt | sed -n -e 'N;N;N;s/\n/ /g;p'
01 02 03 04
05 06 07 08
09 10 11 12
$ cat nrs.txt | sed -n -e 'N;N;N;N;N;s/\n/ /g;p'
01 02 03 04 05 06
07 08 09 10 11 12
$

Good for the following task:

$ cat entries.txt
entry-1-data-1
entry-1-data-2
entry-2-data-1
entry-2-data-2
$ cat entries.txt | sed -n -e 'N;s/\n/ /;p'
entry-1-data-1 entry-1-data-2
entry-2-data-1 entry-2-data-2
$

Two long groups, removing elements (1st, 2nd):

$ cat nrs.txt | sed -n -e 'p;n'
01
03
05
07
09
11
$ cat nrs.txt | sed -n -e 'n;p'
02
04
06
08
10
12
$

In general case you have groups of length _k_ and the starting pattern is ‘n;n;…;n’. There’s k-1 number of letter n here. You can place letter p around the n-s, which is exactly k possibility. If there’s a p in position k0 then the k0-th element will be printed out.
So if you have 6 long groups and you want every fifth element:

$ cat nrs.txt | sed -n -e 'n;n;n;n;p;n'
05
11
$

$ cat seconds.txt
120
123
$ cat seconds.txt | sed 's/$/ 60 ~ 0 !=r +/' | sed '1i[1+] sr 0' | sed '$ap' | dc
5
$

Today is a scripting day : )

If you have to maintain a large MS Access database (manipulate data in it) with complex relationship matrix, the built-in relations diagram is quite useless. This is because Access won’t compute a proper layout. It just displays the nodes (the tables) next to each other and draw all the edges. Period. An idea is to dump these relations into a graphviz .dot file and let graphviz do the layout. This solution is very useful if you want to understand a complex database represented in Access.

C:\Users\grault\Desktop>dir/b test.accdb
test.accdb

C:\Users\grault\Desktop>type dumpRelationship.vbs
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objAcc = CreateObject("Access.Application")
fileName = objFSO.GetAbsolutePathName(WScript.Arguments.Item(0))
outName = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

objAcc.OpenCurrentDatabase(fileName)
Set outFile = objFSO.CreateTextFile(outName)

outFile.WriteLine ("digraph G { graph [rankdir=""LR""];")
For Each tbl In objAcc.CurrentDb.Tabledefs
   If LCase(Left(tbl.Name, 4)) <> "msys" Then
     entry = """" & tbl.Name & """ [label=""<" & tbl.Name & "> " & tbl.Name
     For Each fi In tbl.Fields
       entry = entry & "| <" & fi.Name & "> " & fi.Name
     Next
     entry = entry & """ shape=""record""];"
     outFile.WriteLine(entry)
   End If
Next

For Each rel In objAcc.CurrentDb.Relations
  For Each fld In rel.Fields
    arrow = """" & rel.Table & """:" & fld.Name & " -> " & _
            """" & rel.ForeignTable & """:" & fld.ForeignName & _
            " [arrowhead=""odotodot"" arrowtail=""orboxrtee""];"
    outFile.WriteLine(arrow)
  Next
Next

outFile.WriteLine("}")
outFile.Close
objAcc.CloseCurrentDataBase
objAcc.Quit

C:\Users\grault\Desktop>dumpRelationship.vbs test.accdb test.dot

C:\Users\grault\Desktop>type test.dot
digraph G { graph [rankdir="LR"];
"Table1" [label="<Table1> Table1| <ID> ID| <Desc> Desc" shape="record"];
"Table2" [label="<Table2> Table2| <ID> ID| <Ref1> Ref1| <OtherDesc> OtherDesc"
shape="record"];
"Table1":ID -> "Table2":Ref1 [arrowhead="odotodot" arrowtail="orboxrtee"];
}

C:\Users\grault\Desktop>dot -Tpng -o test.png test.dot

(dot.exe:6480): Pango-WARNING **: couldn't load font
"Times Roman Not-Rotated 14 ",
falling back to "Sans Not-Rotated 14", expect ugly output.

C:\Users\grault\Desktop>

the result

Update: at the bottom, I’ve inserted some screenshots, how to really see the merged document (and probably print it). The technology used here is master documents..

In case you have generated Word files which are parts of a future “master” document and also you want to automate the process of assembling them together by writing some sort of build script, the following snippet is probably useful. Generating documents is good to avoid redundancy. In case you have to have a document in which parts can be computed based on other parts (with also the miserable contraint on document format and production environment) , it’s good to consider splitting the document, generating the parts and assembling them at the end. And now, the code:

C:\Users\grault\Desktop\a>dir/b
a1.docx
a2.docx
build.vbs

C:\Users\grault\Desktop\a>type build.vbs
Set objWord = CreateObject("Word.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objDoc = objWord.Documents.Add()

objDoc.ActiveWindow.View.Type = 2 'wdOutlineView
For I = 1 To WScript.Arguments.Count - 1
   objDoc.Subdocuments.AddFromFile _
     (objFSO.GetAbsolutePathName(WScript.Arguments.Item(I)))
Next

objDoc.SaveAs(objFSO.GetAbsolutePathName(WScript.Arguments.Item(0)))
objWord.Quit
C:\Users\grault\Desktop\a>build.vbs out.docx a1.docx a2.docx

C:\Users\grault\Desktop\a>dir/b
a1.docx
a2.docx
build.vbs
out.docx

C:\Users\grault\Desktop\a>

Entry at (2010/01/07):

a1.docx

a2.docx

out.docx opened fresh

change to outline view

click on the "Show Document" button

you'll get this view (or similar : )

click on the "Expand Subdocuments" button

finally you'll get the contents visible

now you can change the view (or print the document)

Just a few words about my attempt to use Gnus with msmtp. The first step was to launch msmtp without Gnus from command line. There are three ports related to smtp protocol: 25, 465, 587. Usually 25 is the plain smtp without encryption, the difference between the other two is that 587 uses TLS encryption and 465 uses SSL encryption. 587 is filtered in some way on my network (telnet to that port times out), so 465 is the one I use. The majority of examples and tutorials use 587, so I tried using those config files and steps but the port. The most important difference is in the .msmtprc file:

account gmail
host smtp.gmail.com
tls on
tls_certcheck off
tls_starttls off # this is the most important difference
port 465 # not using 587
auth on
user username # without the 'at gmail dot com'
from username@gmail.com
password your_password_connected_to_username

Without this most important change msmtp just hangs after printing out “reading recipients from the command line” which is really annoying because there’s no good search results on google what to do in such situations. With previous config file I was able to run msmtp as follows:

$ cat mail.txt
To: grault@do-not-send-mail-here.com
Subject: test msmtp
From: XY <no-such-user@gmail.com>
User-Agent: The command line MSMTP

test MSMTP

--
BRs,
Grault
$ msmtp -d -C .msmtprc -a gmail grault@do-not-send-mail-here.com < mail.txt
ignoring system configuration file C:\ProgramData\msmtprc.txt: No such file or d
irectory
loaded user configuration file .msmtprc
using account gmail from .msmtprc
host                  = smtp.gmail.com
port                  = 465
timeout               = off
protocol              = smtp
domain                = localhost
auth                  = choose
user                  = no-such-user
password              = *
ntlmdomain            = (not set)
tls                   = on
tls_starttls          = off
tls_trust_file        = (not set)
tls_crl_file          = (not set)
tls_key_file          = (not set)
tls_cert_file         = (not set)
tls_certcheck         = off
tls_force_sslv3       = off
tls_min_dh_prime_bits = (not set)
tls_priorities        = (not set)
auto_from             = off
maildomain            = (not set)
from                  = no-such-user@gmail.com
dsn_notify            = (not set)
dsn_return            = (not set)
keepbcc               = off
logfile               = (not set)
syslog                = (not set)
reading recipients from the command line
TLS certificate information:
    Owner:
        Common Name: smtp.gmail.com
        Organization: Google Inc
        Locality: Mountain View
        State or Province: California
        Country: US
    Issuer:
        Common Name: Thawte Premium Server CA
        Organization: Thawte Consulting cc
        Organizational unit: Certification Services Division
        Locality: Cape Town
        State or Province: Western Cape
        Country: ZA
    Validity:
        Activation time: Mon Jul 30 02:00:00 2007
        Expiration time: Fri Jul 30 01:59:59 2010
    Fingerprints:
        SHA1: 5E:F7:E8:CE:1A:BE:D8:94:F2:77:45:5D:ED:38:46:4F:5D:D1:97:61
        MD5:  F1:D3:DE:59:9D:9C:E2:31:EA:AA:2C:A0:FC:AD:9A:61
<-- 220 mx.google.com ESMTP 37sm7124747yxl.35
--> EHLO localhost
<-- 250-mx.google.com at your service, [187.97.152.29]
<-- 250-SIZE 35651584
<-- 250-8BITMIME
<-- 250-AUTH LOGIN PLAIN
<-- 250-ENHANCEDSTATUSCODES
<-- 250 PIPELINING
--> AUTH PLAIN AVL4YWLvbGNuLnN6sWQzAGWkc2Z8eHN6rg==
<-- 235 2.7.0 Accepted
--> MAIL FROM:<no-such-user@gmail.com>
--> RCPT TO:<grault@do-not-send-mail-here.com>
--> DATA
<-- 250 2.1.0 OK 37sm7124747yxl.35
<-- 250 2.1.5 OK 37sm7124747yxl.35
<-- 354  Go ahead 37sm7124747yxl.35
--> To: grault@do-not-send-mail-here.com
--> Subject: test msmtp
--> From: XY <no-such-user@gmail.com>
--> User-Agent: The command line MSMTP
-->
--> test MSMTP
-->
--> --
--> BRs,
--> Grault
--> .
<-- 250 2.0.0 OK 1240471557 37sm7124747yxl.35
--> QUIT
<-- 221 2.0.0 closing connection 37sm7124747yxl.35
$ msmtp --version
msmtp version 1.4.17
TLS/SSL library: GnuTLS
Authentication library: GNU SASL
Supported authentication methods:
plain cram-md5 digest-md5 external login ntlm
IDN support: enabled
NLS: disabled
Keyring support: none
System configuration file name: C:\ProgramData\msmtprc.txt
User configuration file name: c:\users\grault\msmtprc.txt

Copyright (C) 2008 Martin Lambers and others.
This is free software.  You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.
$ pwd
/cygdrive/c/users/grault
$

Central development simulated with darcs.

cd /path/to/project
cd ..
pushd /path/to/repository
mv /path/to/project .
cd project
darcs init
darcs add -r *
darcs record -am "init"
popd
darcs get /path/to/repository/project
cd project
 [develop]
darcs whatsnew
darcs record -am "log message"
darcs push -a /path/to/repository/project

Actually I’m up to design a business card for myself (due to hopefully financial reasons). One idea was to create the back page as a collection of words related to my professional experience, typeset in different sizes. For this purpose I wrote a small ooo script which inserts strings as curves into the document making me able to place and rotate them after.

Sub InsertWord (oFSize As Single, oColor As Long, oText As String)
  Dim oDoc As Variant
  oDoc = ThisComponent
  If oDoc.SupportsService("com.sun.star.drawing.DrawingDocument") Then
    Dim oPage As Object
    Dim oPoint As New com.sun.star.awt.Point
    Dim oSize As New com.sun.star.awt.Size
    Dim oTextShape As Object
    oPage = oDoc.drawPages.getByName("back")
    oPoint.x = 0
    oPoint.y = 0
    oSize.Width = 9000
    oSize.Height = 5000

    oTextShape = oDoc.createInstance("com.sun.star.drawing.TextShape")
    oTextShape.Size = oSize
    oTextShape.Position = oPoint
 
    oPage.add(oTextShape)
 
    oTextShape.String = oText
    oTextShape.CharWeight = com.sun.star.awt.FontWeight.BOLD
    oTextShape.CharFontName = "Arial"
    oTextShape.CharHeight = oFSize
    oTextShape.CharColor = oColor
  
    oDocCtrl = oDoc.getCurrentController()
    oDocCtrl.select( oTextShape ) 
    oDocFrame = oDocCtrl.getFrame()
    oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" )
    oDispatchHelper.executeDispatch( oDocFrame, ".uno:ChangeBezier", "", 0, Array() )
  End If
End Sub

Related (and probably useful) links are:

• wiki
• the Draw part of wiki
• color scheme editor

Recently I had to do the following. I had a file full of object lifecycles. One line contains an y coordinate, which corresponds to the address the object was placed at, and two x coordinates, the time of allocation and the time of relocation/deallocation. With ImageMagick I was able to generate an image which is useful to see memory fragmentation and garbage collection performance. I had use only the following two syntaxes:

% convert -size 300x200 xc:white foo.pbm   
% mogrify -draw 'line 4,30 102,30' foo.pbm
% mogrify -draw 'line 11,31 80,31' foo.pbm
% mogrify -draw 'line 29,32 180,32' foo.pbm
% mogrify -draw 'line 90,33 280,33' foo.pbm

Obviously I computed the min and max for all the coordinates. Commands above produced the following:

pbm converted to gif