JohnAllen.us

Recently I decided that I needed a copy of an ‘fport’ program for Windows that would let me see what program was making connections out of my computer. Unfortunately, my Anti-Virus software warned me that the website where it is posted was on the known virus websites list, so I just decided to make my own.

I started looking around for a programatic way to do what the normal ‘netstat’ program does, but everything I found was rather involved…and since I didn’t want to spend a whole lot of time on it, I cheated and just used the output from the ‘netstat -ano’ command and then did a quick lookup of the returned PID to find the program.

The result is the following code: Continue reading »

If you try to “compile” a Ruby script that has the Watir gem in it with OCRA, you will find that running the compiled .exe file on a computer without the Watir gem previously being installed may result in this error:

c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie.rb:113:in `initialize': unknown OLE server: `AutoItX3.Control' (WIN32OLERuntimeError)
    HRESULT error code:0x800401f3
      Invalid class string      from c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie.rb:113:in `new'
        from c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie.rb:113:in `autoit'
        from c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie-class.rb:425:in `autoit'
        from c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie-class.rb:422:in `set_window_state'
        from c:/ruby/lib/ruby/gems/1.8/gems/watir-1.6.2/lib/watir/ie-class.rb:398:in `maximize'
        ...

Huh? Why am I getting a WIN32OLE error? This all runs fine on my computer when I tested it! … Well, it seems that Watir uses its own version of a win32ole gem, and not the one that you already have installed. In fact, when you compile a Ruby script that has both win32ole and watir gems, you will need to comment out the “require ‘win32ole’” line in order for it to work. Anyway… as part of the win32ole gem install, it seems that it registers the AutoItX3.dll file into the registry. OCRA will, however, *NOT* copy this file over and register it for you, so you may see the error above.

So…the trick is add the AutoItX3.dll file to your OCRA compile, and to temporarily register the DLL before calling watir or win32ole commands. I simply copied the DLL from the win32ole gem directory to my Ruby script’s working directory, and then added it to my OCRA compile command:

C:\Server4\Dev\MyProg>ocra --console --icon c:/Server4/Dev/icons/exonets.ico myprog.rb AutoItX3.dll

OCRA will add the DLL to the EXE and when run will place it in the current temporary directory. After that you need to run the DLL register command to make it an OLE server, then when done, be sure to unregister it before your program completes.
Here a sample of code that I use to accomplish all this: Continue reading »

Ever wanted to have a spell check function in your Ruby Program? As long as you have MS Word installed, you can use the Spellcheck function from your program. Here’s the code:

require 'exo/iswindows'

if not RUBY_PLATFORM.isWindows?
   puts "This program only runs under Windows!"
   exit
end

require 'win32ole'

def spellcheck(scstring)
   word = WIN32OLE.new('Word.Application')
   doc = word.Documents.Add     ## Blank document

   word.Selection.Text = scstring
   word.Dialogs(828).Show

   ### return the corrected text
   if not scstring[/ /]      ## only one word with no spaces in the string
     # highlight the word first,
     word.Selection.MoveLeft( 'Unit'=>2,
            'Count'=> 1,
            'Extend'=>2)
   end
   ## multiple words end up already selected after the spell check
   # then retrieve.
   correct = word.Selection.Text
   doc.close(0)
   word.Quit
   return correct
end

if __FILE__ == $0
   puts "Corrected => #{spellcheck(ARGV[0])}"
end

The program opens a new document, pastes the text to check into the document, and then brings up the spellcheck dialog box with any words it can’t find in the dictionary and prompts you to correct the mistakes. Here’s an sample output:

C:\Server6\Dev\Ruby>ruby spellcheck.rb "this is a test srting to seee how the slpell check is working"
Corrected => This is a test string to see how the spell check is working

C:\Server6\Dev\Ruby>ruby spellcheck.rb  antidisestablishmenttarianism
Corrected => antidisestablishmentarianism

For some reason, if you check multiple words, the dialog auto-closes after the last ‘fix’ …but if its just one word, it stays open until you click the close button. Conversely, multiple words stay selected in the Word doc after the dialog closes, but with only one word, it does not stay selected, and you have to select it back in order to read it off the page of the document.

I found it a bit odd that there doesn’t seem to be any tutorials on how to automate Microsoft Visio using Ruby. Most of the other Office apps have a bunch of pages on automation, but none on Visio….that or my Google Fu is leaving me

So…. Here’s a brief tutorial on how to automate Visio using Ruby. First thing you need to do is bring in the WIN32OLE module and create a new instance of the Visio application

require 'win32ole'
visio = WIN32OLE.new('Visio.Application')

If you already have Visio running, you can connect to it with this line instead

visio = WIN32OLE.connect('Visio.Application')

Once you have your instance, its usually best to load in all the constants from the application. Visio has tons and tons, so if you are going to try and convert any VBA or C++ code over, you will need these.

class VisioConst
   # Empty class to hold constants
end
...
WIN32OLE.const_load(visio, VisioConst)

Be sure to put the class declaration below the ‘require’ statements so your code will read better. Now we will load in a Visio template just to make things easier on us. Continue reading »

I’ve been working with Visio lately and noticed that when my programs start it up, Visio was not coming up maximized. Since I wanted to be able to see my work full screen, I started looking for a way to force it to maximized mode. I quickly learned that there is no convenient ‘visio.Maximize’ method to use. Bummer. Other Office apps have a ‘.WindowState’ parameter that can be set to minimize, maxamize, restore, or hide the application window, but not Visio. Guess that would make it too easy

For Visio, you can use the ‘ShowWindow’ function from the user32.dll to do the work:

require 'Win32API'

##Possible cmd values:
# Hidden => 0
# Restored => 1
# Minimized => 2
# Maximized => 3

def ShowWindow(wndHandle, cmd)
   wndShowWindow = Win32API.new("user32", "ShowWindow", ["p","i"], "i")
   wndShowWindow.call(wndHandle, cmd)
end

‘wndHandle‘ is the infamous Window Handle used in the lower-level Windows APIs. At least Visio has a way to return the handle. So in your program:
Continue reading »

With all those neat Wingdings characters available, its nice to be able to insert them into an Excel spreadsheet programatically. The easiest way to go about it is to Record a Macro of the character being inserted, then use the resulting VBA code as a reference for your Ruby code.

Working on a checklist application for a customer, I needed to insert checkmarks, and X-out characters into a column of the list. First step was to record the macro in Excel, then look at the resulting macro (To look at the macro in Excell 2003, click on Tools > Macro > Macros… then highlight the macro and click the ‘Edit’ button). For my checkmark, the code looked like this:

ActiveCell.FormulaR1C1 = "P"
    With ActiveCell.Characters(Start:=1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("I23").Select

So for my insertCheckmark function, my code looks like this:
Continue reading »

Over at the Ruby on Windows blog, David posted an article about how the latest versions of Office allow for saving files as PDF. Which prompted me to ask how you can find out what the versions are, since Office 2003 apps don’t have this option. So in between watching the AFC Championship game, I figured it out and came up with the following program:

require 'win32ole'

xl = WIN32OLE.new('Excel.Application')
xl.visible = false

ver = nil
case xl.Version
   when "12.0"
     ver = "Excel 2007"
   when "11.0"
     ver = "Excel 2003"
   else
     ver = "{Unknown Version => #{xl.Version}}"
end
puts "     Excel Version: #{ver}"
xl.Quit

word = WIN32OLE.new('Word.Application')
word.visible = false
ver = nil
case word.Version
   when "12.0"
     ver = "Word 2007"
   when "11.0"
     ver = "Word 2003"
   else
     ver = "{Unknown Version => #{word.Version}}"
end
puts "      Word Version: #{ver}"
word.Quit

ppt = WIN32OLE.new('Powerpoint.Application')
#ppt.visible = false    ## Powerpoint 2007 does not allow itself to be hidden?!?
ver = nil
case ppt.Version
   when "12.0"
     ver = "Powerpoint 2007"
   when "11.0"
     ver = "Powerpoint 2003"
   else
     ver = "{Unknown Version => #{ppt.Version}}"
end
puts "Powerpoint Version: #{ver}"
ppt.Quit

ol = WIN32OLE.new('Outlook.Application')
#ol.visible = false
ver = nil
## Outlook seems to tack on a build number
tt = ol.Version.slice(0,4)
case tt
   when "12.0"
     ver = "Outlook 2007"
   when "11.0"
     ver = "Outlook 2003"
   else
     ver = "{Unknown Version => #{ol.Version}}"
end
puts "   Outlook Version: #{ver}"
ol.Quit

One project that I’m currently working on calls for taking tasks out of a MS Project document and moving them to a Powerpoint presentation with pretty formatting. Maybe I’ll blog about the whole thing later.  Here’s a quick program I wrote to dump out all the tasks from a test project in a somewhat formatted output to verify I could actually get the tasks:

#!C:\ruby\bin\ruby.exe

require 'exo/iswindows'

if !RUBY_PLATFORM.isWindows?
   puts "This program only runs under Windows!"
   exit
 end

require 'win32ole'

def fmtMSProjDate(msdate)
   yr = msdate[/^(\d\d\d\d)\/\d\d/, 1]
   mth = msdate[/^\d\d\d\d\/(\d\d)\/\d\d/, 1]
   day = msdate[/^\d\d\d\d\/\d\d\/(\d\d) /, 1]
   return "#{mth}/#{day}/#{yr}"
end

def getAbsolutePathName(file)
   fso = WIN32OLE.new('Scripting.FileSystemObject')               ## VBA File System commands
   return fso.GetAbsolutePathName(file)
end

if !ARGV[0][/\:/]
  file = "#{Dir.pwd}/#{ARGV[0]}"
else
  file = ARGV[0]
end

app = WIN32OLE.new('MSProject.Application')
app.FileOpen(getAbsolutePathName(file))
pj = app.ActiveProject
#app.Visible = true

puts "\nProject: #{pj.Title.ljust(60)}" + fmtMSProjDate(pj.Start.to_s) + "  " + fmtMSProjDate(pj.Finish.to_s) + "\n\n"

pj.Tasks.each do |t|
   puts t.ID.to_s.rjust(5) + " " + "| "*t.OutlineLevel + t.Name.ljust(63-(t.OutlineLevel*2)) + fmtMSProjDate(t.Start.to_s) + "  " + fmtMSProjDate(t.Finish.to_s)
end

app.Quit

Here’s my test run:
Continue reading »

While running a simple enum of my Outlook inbox, I came across an error:

irb(main):005:0>require 'win32ole'
irb(main):006:0>ol = WIN32OLE.new('Outlook.Application')
irb(main):023:0>mapi = ol.GetNameSpace('MAPI')
irb(main):024:0>inbox = mapi.GetDefaultFolder(OutlookConst::OlFolderInbox)
irb(main):031:0>inbox.Items.each { |m| p.To }
ALLEN, JOHN
ALLEN, JOHN; xxxx
...
WIN32OLERuntimeError: unknown property or method `To'
    HRESULT error code:0x80020006
      Unknown name.
        from (irb):83:in `method_missing'
        from (irb):83
        from (irb):83:in `each'
        from (irb):83

Huh?? A message without a ‘To’ line? What gives? Poking around a bit more I get the item into an object to look at, and find out its an AppointmentItem, and not a normal email message. I’m thinking there is some property of the Item object that will tell me what kind of message it is. After some more digging around on the Internet, I find a reference to a MessageClass property. So if I modify my code a bit, I can list all the ‘To’ lines from all the messages I’m interested in looking at:

inbox.Items.each do |msg|
    puts "Unread:#{msg.To}" if msg.Unread && !msg.MessageClass[/Meeting/]
    puts "Meeting:#{msg.Subject}" if msg.MessageClass[/Meeting/]
end

The MessageClass properties of all the emails in my inbox are:
“IPM.Note” => Regular EMail
“IPM.Schedule.Meeting.Request” => Meeting Request
“IPM.Schedule.Meeting.Resp.Pos” => Meeting Accept
“IPM.Schedule.Meeting.Canceled” => Meeting Canceled
I’m sure there’s a Meeting Decline message class….I just don’t happen to have it right now.
“IPM.Note.Rules.OofTemplate.Microsoft” => Out of Office message.
I’m sure there are more types…I’ll add to this post as I find more.

Once the values are all loaded in, its time to make the spreadsheet look pretty. Here’s how you draw a single, continuous, thin line around each cell in a range of cells. This function can be used an any number of cells…but only in one row. If you give it a range of more than one row, then it will draw a box around the rows, and add the vertical lines. As I am adding one row at a time in my applications, my function works fine for single rows. The ExcelConst:: constants were address in a previous post.

def boxRange(ws, range)
   b = range.Borders(ExcelConst::XlEdgeBottom)
   b.LineStyle = ExcelConst::XlContinuous
   b.Weight = ExcelConst::XlThin
   b.ColorIndex = 1
   t = range.Borders(ExcelConst::XlEdgeTop)
   t.LineStyle = ExcelConst::XlContinuous
   t.Weight = ExcelConst::XlThin
   t.ColorIndex = 1
   l = range.Borders(ExcelConst::XlEdgeLeft)
   l.LineStyle = ExcelConst::XlContinuous
   l.Weight = ExcelConst::XlThin
   l.ColorIndex = 1
   r = range.Borders(ExcelConst::XlEdgeRight)
   r.LineStyle = ExcelConst::XlContinuous
   r.Weight = ExcelConst::XlThin
   r.ColorIndex = 1
   iv = range.Borders(ExcelConst::XlInsideVertical)
   iv.LineStyle = ExcelConst::XlContinuous
   iv.Weight = ExcelConst::XlThin
   iv.ColorIndex = 1
end
...
boxRange(ws, ws.Range("a3:q3"))

UPDATE!: The version above would only box in one row at the time. At the time it was not a huge issue for me, since my then current project was only working on one row at a time. if you try using the xlInsideHorizontal parameter on just one row, it will error out. Projects since then required multi-row borders.My new version handles single or multi-row ranges:

def boxRange(ws, range)
   b = range.Borders(ExcelConst::XlEdgeBottom)
   b.LineStyle = ExcelConst::XlContinuous
   b.Weight = ExcelConst::XlThin
   b.ColorIndex = 1
   t = range.Borders(ExcelConst::XlEdgeTop)
   t.LineStyle = ExcelConst::XlContinuous
   t.Weight = ExcelConst::XlThin
   t.ColorIndex = 1
   l = range.Borders(ExcelConst::XlEdgeLeft)
   l.LineStyle = ExcelConst::XlContinuous
   l.Weight = ExcelConst::XlThin
   l.ColorIndex = 1
   r = range.Borders(ExcelConst::XlEdgeRight)
   r.LineStyle = ExcelConst::XlContinuous
   r.Weight = ExcelConst::XlThin
   r.ColorIndex = 1
   iv = range.Borders(ExcelConst::XlInsideVertical)
   iv.LineStyle = ExcelConst::XlContinuous
   iv.Weight = ExcelConst::XlThin
   iv.ColorIndex = 1
   ### xlInsideHorizontal will barf if the range is only one row!!
   if range.Rows.count > 1
     ih = range.Borders(ExcelConst::XlInsideHorizontal)
     ih.LineStyle = ExcelConst::XlContinuous
     ih.Weight = ExcelConst::XlThin
     ih.ColorIndex = 1
   end
end