Excel Add-In in Perl?

[loris]

Dear All,

I don't know much about Excel, but I wondered whether anyone was aware of a way of writing a an Add-In for Excel using Perl (VB seems to be the standard approach). Googling gave me this, but maybe one of you knows of something else.

Thanks,

loris

[Corion]

I'm not aware of ways to write Excel Add-Ins using Perl directly, but I can think of some ways to accomplish that:

• Write a VB add-in that calls your Perl program. Transferring the data between your Perl program and the VB program is tricky, I recommend using ODBC to stuff the data directly into Excel or into a (Access) database which the VB program then reads
• Write an Excel macro that loads a resource via HTTP (the Open() command can open URLs too, as can some cell function) and provide your results via HTTP::Server::Simple. The best way is to encode the cell/column info in the HTTP query so you avoid encoding/decoding your results across the process barrier
• Using some ActiveState development kit, write an OLE object in Perl and call it from a VB wrapper. This is the most convenient way but requires buying the ActiveState kit.

[Albannach]

There is one other option Corion didn't cover, one which I tend to use because it is very simple, but then it's also fairly limited. I monitor the Windows clipboard using Win32::Clipboard and then I select and copy cells in Excel, munge them as needed in Perl, and past them back to the clipboard. From there I can paste the results in Excel anywhere I like. I have even used the first cell as a "command" to the perl script. This works well for data cleaning tasks at least, maybe it will be suitable for your needs.