Update of "Excel hacks"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.


Artifact ID: 3044ba0b8e940d414e7f6453941fbae2ecb170bb
Page Name:Excel hacks
Date: 2011-09-24 18:24:54
Original User: adric
Parent: d630ddae722fe2041109109860a8bef331dccca6

Seriously, Excel??

(not the O'Reilly book by that title, at least not yet) Yes, because to misquote jdillinger, "...that's where the data is!"

DIM helloWorld as long

Resave an XLS (etc) as an XLSM and enable the Developer tools in Office 2010. That MSDN howto will get you through MsgBox "Hello Excel!" and making your own functions. Using xlsm you can not only record and edit macros and functions in VBA** but you can save them into an .xlsm file (project workspace) and reuse them.

Use and reuse VBA macros safely

Take the data you need to munge in the CSV,XLS,ODT,whatever and paste the contents of the bits you need into the XLSM workspace where you can use your custom functions and macros to process the data, make charts, go nuts. Then copy and paste by Value the results of your work into the sheet they gave you or a new xls/xlsx file without the macros, send it out, and get back to work. You'll save time and work with macros and don't have to risk passing macros around (dangerous and against the rules most places).

Handy VBA macros

Forward and Reverse DNS hack :

Or if you aren't afraid of CPAN

... then hopefully you have some fear response left for Win32::OLE.

Of course these Office APIs can be used by other languages, including Perl. There are plenty of web resources for calling VBA macros in Excel from Perl in Windows, scripting Excel with Powershell on Windows 7+, digging data out of Excel files with Perl on Linux, and tearing your hair out trying to figure why it just doesn't work right sometimes. Other languages can use the rickety ol' OLE32 bridge and Perl and all of the others have some native libraries that are much better for modifying XLS/Office files if that's more like what you need, so just look around.

** Yes, @#$^#^$ BASIC. MS supports VBA with Office Pro and says you can use C# with Office if you subscribe*** to Visual Studio Pro ($$+). Lucky for the world there is also Perl.

*** The Developer_Programs page could use a update, huh?