Saturday March 22, 2008
The curse of Facebook
Tuesday March 18, 2008
VBA: Generating Word files from Excel data
I had data in an Excel file that I wanted to get into Word in a very nice layout, with some fancy introduction text. I came up with the following VBA code:
Public Sub GenDoc()
Dim WordDocument As Word.Document
Dim TableTemplate As Word.Document
Dim DocumentTemplate As Word.Document
Dim WordApplication As Word.Application
Dim DocumentRange As Word.Range
Dim ExcelWorksheet As Excel.Worksheet
Dim i As Integer
Set WordApplication = CreateObject("Word.Application")
' Template for fancy introduction
' Open as read-only
Set DocumentTemplate = WordApplication.Documents.Open("template1.doc", False, True)
' Template for fancy layout per Excel row
' Open as read-only
Set TableTemplate = WordApplication.Documents.Open("template2.doc", False, True)
WordApplication.Visible = False
Set WordDocument = WordApplication.Documents.Add
Set DocumentRange = WordDocument.Content
' This bit apparently guarantees that pasted blocks are appended, not overwriting the selected block
' Paste the fancy header into the newly created document
Set ExcelWorksheet = ThisWorkbook.Worksheets.Item(1)
' Assume 100 columns
For i = 1 To 100
' Paste the formatted table stuff
' In the fancy template, I had some markers..
' Replace counter - NUMBERMARKER
.Text = "NUMBERMARKER"
' Replace NUMBERMARKER with i
.Replacement.Text = i
.Wrap = wdFindStop
.Format = False
.MatchWholeWord = True
.MatchSoundsLike = False
.MatchAllWordForms = False
' Wrap it up
WordApplication.Visible = True
I also had some fancy progress bar in there somewhere.. I'm getting quite good at this drive-by VBA coding.
Wednesday March 12, 2008
Saturday March 08, 2008
Upgrading Trac #2
After the migration, Trac started throwing No changeset X in repository
. It took me a few minutes to figure out. The solution: resync Trac with the SVN db:
Friday March 07, 2008
My webserver crashed a couple of weeks ago so I took some time to install a new disk, install Etch from scratch and move all my old stuff to the new installation. On my old machine, I manually compiled Trac
but Etch comes with a package. It turns out there are some differences
:) TagsWikiModule was giving me problems (as described here
). I fixed it by making sure I had
default_handler = WikiModule
again and also had
trac.wiki.web_ui.wikimodule = enabled
I also had the following error:
TracError: Database newer than Trac version
I fixed this by installing SQLite3 and running it on trac.db and applying the following statement:
update system set value = 19 where name = "database_version";
(depending on the current database version.. mine was 20)
Perl script as Windows service
I needed this stuff a couple of times but never took the time to document it properly.. Summarized: Use a binary from Microsofts Resource Kit as a called to perl. KB article here
Copy the executable files to the system path (or wherever you see fit within the path)
copy INSTSRV.EXE C:\Windows\System32
copy SRVANY.EXE C:\Windows\System32
Create a service called SERVICENAME and point it to SRVANY.EXE that you copied to the system directory.
INSTSRV.EXE SERVICENAME C:\Windows\System32\SRVANY.EXE
Open the Registry Editor (regedit) and look for
Right click on the SERVICENAME in the left column and add a new string value named Description. Double-click on Description and set the value to SERVICENAME DESCRIPTION.
Right click on the SERVICENAME in the left column and add a new key named Parameters. Click on Parameters and add a new String Value called Application. Double-click on Application and set the value to
Close the Registry Editor.
The service show now show up in your Services panel in the Control Panel > Administrative Tools. Change the startup mode from Manual to Automatic if needed.
SERVICENAME is whatever you want your service to be called, SERVICENAME DESCRIPTION is whatever you want listed as a description for this service and SCRIPTLOCATION is the path to your script.
Leopard + mod_perl: solution.. definitly!
It turns out the error I mentioned earlier
was a human error.. my error that is. In my code, I was trying to load Apache2::Cookie through an Apache2::Request object (which obviously I misassigned). Rebuilding Apache2 and mod_perl (and all dependencies) from scratch made everything work just fine.
Database challenge: upgrading data [update]
I decided to KISS (Keep It Simple, Stupid). I'll split up the SQL dumps into data that definitely needs to be upgraded, data that might be upgraded and data that the user probably does not want to change unless he's reinstalling everything.
Value of degrees and certifications
I work in a specialized environment. The 'company' pays a decent chunk of money to get experts in their respective field. Pretty much every one has a masters degree in something, together with a whole bunch of certifications. The other day, I provided one of my co-workers (masters in CompSci, X years of IT experience, CCIE certified,...) with a perl one-liner he could use to change some IP addresses in router config dumps. A few minutes after that, I saw him copy the configs from the router terminal to MS Word, and saving it as a MS Word file.. I asked him if he magically had come across a perl module that parsed MS Word files.. He gave me this very confused look. I explained to him that the one-liner only worked on text files, as MS Word probably didn't allow changing text strings in THAT way. I even tried explaining some of the checksum and end-of-line issues that might occur. He still gave me this confused look and insisted that we'd try it anyway. We did. FAIL!
Sometimes work is fun!
Saturday March 01, 2008
Corporate Mind Control
One of my old Uni buddies posted something on his blog recently that got me thinking. Apparently he became a Microsoft Regional Director.. To me that sounds like something to do with management stuff. It is in fact far worse. Apparently, Microsoft is giving up these fancy name tags to people in the community to get them to work for Microsoft without actually having them work for Microsoft. Giving out these kind of labels seems a bit like saying "Thank you for your effort in the community. You are now our bitch!". The blog is ofcourse biased towards everything Microsoft.