applescript code snippets
Office:mac Auto Color Cells
I recently switched to a Mac and really miss my auto color cells VBA script for Excel. After quite a bit of digging (and trial-and-error) I managed to recreate the functionality using AppleScript. It is pretty slow, but it works!
[ more ]Align Center
Rather than merging cells to center headers, I prefer to have text centered
across selection. This avoids problems with deleting and filling columns that
are cosed by merged cells. The following sets up a command to toggle centering
across columns.
Backup Current File
Here is another AppleScript version of a
[excel.html#backup](prior vba) script. This one creates a back of the current
workbook. It copies the last saved version of the current workbook to
a Backup subdirectory below the directory in which that file was saved.
It adds a counter (ie, .001) before the .xls(x) extension.
cygwin code snippets
Perl Tk on Cygwin
Getting Perl/Tk running under cygwin seems to be a constant pain. Until the recent perl release which went to a site/5.8 directory for modules, this was something that I went through every time I upgraded perl. I think I found the secret though. Apparently they dropped cygwin support in Tk804. You need to get Tk800.025 from cpan. Make test still reports a few errors, but the installation works for me.
[ more ]Installing CRM114 on Cygwin
As is typical with installing anything in cygwin, CRM114 took some tweaking to get it to install properly. The CRM114 Discriminator is a "Controllable Regex Mutilator" according to their website. In engilish it is an complex and interesting tool to "examine incoming e-mail, system log streams, data files or other data streams, and to sort, filter, or alter the incoming files or data streams according to the user's wildest desires." Mostly it is used to sort spam.
[ more ]Create File Associations in Windows
This is cool. I just came across a commandline tool for creating and
listing file associations in windows. the assoc command will list
all the associations and assoc .=txtfile will make all files
without extensions text files (ie, README). Cygwin doesn't seem to be able
to find assoc so it must be run from a cmd window.
excel code snippets
Office:mac Auto Color Cells
I recently switched to a Mac and really miss my auto color cells VBA script for Excel. After quite a bit of digging (and trial-and-error) I managed to recreate the functionality using AppleScript. It is pretty slow, but it works!
[ more ]Align Center
Rather than merging cells to center headers, I prefer to have text centered
across selection. This avoids problems with deleting and filling columns that
are cosed by merged cells. The following sets up a command to toggle centering
across columns.
Backup Current File
Here is another AppleScript version of a
[excel.html#backup](prior vba) script. This one creates a back of the current
workbook. It copies the last saved version of the current workbook to
a Backup subdirectory below the directory in which that file was saved.
It adds a counter (ie, .001) before the .xls(x) extension.
Auto Color Cells
Many users of Excel have made it common practice to color code cells to help identify inputs, formulas, etc. For example, it is common to color all cells act as hard coded inputs (i.e. not a formula) blue, all formulas black. This Excel macro looks at the contents of each selected cell and sets the color appropriately. Further I have added the green coloring for all external references.
[ more ]Toggle Under/Overlines
When formatting a Excel sheet underlining or overlining (which appears as if you underlined the cell above) a cell often looks much better than just underlining the contents of the cell (ctrl-u). This macro will toggle the under/overlines for all the selected sells.
[ more ]Create Spacing Rows
I often want to have some space between row to call attention to a particular row, but rather than having a full row, a small row would work better. This macro will adjust the height of all the select cells if they are empty.
[ more ]Align Center
I hate merged cells. They create all sorts of problems adding/deleting
columns, filling down, etc. But it can look nice to have text centered across a
range not just a single cell. Luckily, Excel provides the rarely used
Align Center formatting option. This macro provides easy access to
toggling the alignment formatting across all selected cells... but that's not
all... :-) it also centers the contents of a single cell if that is all that is
selected.
Toggle Bullet and Sub-Bullet
When I feel like getting fancy, it can be nice to include a bulleted list in an Excel sheet to describe assumptions, etc. This is actually pretty easy to do, but requires adding some odd characters. This macro will add a character and change the font of a cell to create a bullet. If you run this macro on a cell which already contains a bullet, an arrow shaped sub-bullet is inserted instead.
[ more ]Backup Current File
This is one of my favorites. It saves a copy of the current file in the 'Backup' directory if one exists under the directory in which the file is currently saved. It saves the files with an incrementing two digit number after the filename (before the .xls extension). A cap of 50 backups is imposed just to keep from taking up too much disk space (my models tend to be BIG).
[ more ]Select Alternate Columns
I often like to have narrow empty columns between data columns just to make things look nice (cell underlining looks better that way in my opinion). This macro will prompt you for a number of columns per group and then it selects one column per group for the currently selected range (i.e. selecting A5:G5, running the macro and entering 2 would result in columns B, D, and F being selected). Then you can quickly resize those columns to make everything look real pretty.
[ more ]Combine Cells
This routine combines the selected cells into one long string in the current cell.
[ more ]Swap Note and Formula
Here are two routines that pull the formula from a note and put the formula in a note. I had a very specific need for this, but I can't recall why now.
[ more ]mac code snippets
Office:mac Auto Color Cells
I recently switched to a Mac and really miss my auto color cells VBA script for Excel. After quite a bit of digging (and trial-and-error) I managed to recreate the functionality using AppleScript. It is pretty slow, but it works!
[ more ]Align Center
Rather than merging cells to center headers, I prefer to have text centered
across selection. This avoids problems with deleting and filling columns that
are cosed by merged cells. The following sets up a command to toggle centering
across columns.
Backup Current File
Here is another AppleScript version of a
[excel.html#backup](prior vba) script. This one creates a back of the current
workbook. It copies the last saved version of the current workbook to
a Backup subdirectory below the directory in which that file was saved.
It adds a counter (ie, .001) before the .xls(x) extension.
outlook code snippets
Automatically Create Contacts
I often find myself creating a Outlook contact from the signature in an email or some text in a work document. Rather than do it by hand each time, I have put together a few vba commands and a new vba class to parse the text on the clipboard and create a new contact from what it gathers.
[ more ]Export All Contacts as vCards
Outlook has the built in ability to export contacts as vCards, but it will only do it one at a time. With the following vba script and a few bash commands, you can batch export each contact as a vCard and then combine the individual files into one vcard file.
[ more ]Forward E-Mails as They Are Sorted
I often find myself creating a folder to store all the messages relating to a
particular project, and then wanting to forward any message placed in that
folder to one of my colleagues. This code, when placed in the
ThisOutlookSession module, takes care of the forwarding for me.
Outlook Folder List
For the previous hack, I often had a hard time finding the correct folder to monitor. This bit of code will list all the top level folders for you.
[ more ]Outlook Folder List (Updated)
Again, I needed to find the path to particular folder. This one was deep and not under my Inbox. So, updated the folder list function. It is now recursive and (very simply) shows the structure.
[ more ]Process All Outlook Events
I recently needed to walk through all the events in an Outlook calendar and make a change. Here is the simple code:
[ more ]Outlooks Spam Handler
The spam filters that we use at work, process all the messages in a particular folder to train the filter. Rather than drag and drop messages, I use the following code to move the selected or active message into the target folder. For each of the public subs, I have a toolbar button which runs the code.
[ more ]Outlook Junk Mail - Old
The following code worked for older versions of Outlook (2000 I believe), but does not work for newer versions. There used to be a junk button on the toolbar. The code effectively activated that button. I'm not sure how to do it in newer version of Outlook. I have actually given up on Outlook's spam filtering and use SpamAssassian now. You might check out Wininspector to track down the right object.
[ more ]perl code snippets
Using Par::Packer (pp) with Gtk2 on Windows
Well I haven't completely got it working, but it is darn close. I ended up switching to WxWidgets, which will work with some coaxing. Most of this kludge is based on bug reports from Marc Lehmann. Thanks Marc!
[ more ]Local CPAN (mini-cpan)
Found a nice module/script to keep a local copy of the cpan repository for those times when we aren't connected to the internet.
[ more ]CPANPLUS with Local CPAN
In followup to the not about how to create a local cpan repository, I figured out how to get cpanplus to use this respository. Not nearly as simple as with cpan, but once it is setup, it is a breeze. The following script does all the work.
[ more ]Perl Tk on Cygwin
Getting Perl/Tk running under cygwin seems to be a constant pain. Until the recent perl release which went to a site/5.8 directory for modules, this was something that I went through every time I upgraded perl. I think I found the secret though. Apparently they dropped cygwin support in Tk804. You need to get Tk800.025 from cpan. Make test still reports a few errors, but the installation works for me.
[ more ]spam code snippets
Outlooks Spam Handler
The spam filters that we use at work, process all the messages in a particular folder to train the filter. Rather than drag and drop messages, I use the following code to move the selected or active message into the target folder. For each of the public subs, I have a toolbar button which runs the code.
[ more ]Outlook Junk Mail - Old
The following code worked for older versions of Outlook (2000 I believe), but does not work for newer versions. There used to be a junk button on the toolbar. The code effectively activated that button. I'm not sure how to do it in newer version of Outlook. I have actually given up on Outlook's spam filtering and use SpamAssassian now. You might check out Wininspector to track down the right object.
[ more ]Installing CRM114 on Cygwin
As is typical with installing anything in cygwin, CRM114 took some tweaking to get it to install properly. The CRM114 Discriminator is a "Controllable Regex Mutilator" according to their website. In engilish it is an complex and interesting tool to "examine incoming e-mail, system log streams, data files or other data streams, and to sort, filter, or alter the incoming files or data streams according to the user's wildest desires." Mostly it is used to sort spam.
[ more ]vba code snippets
Automatically Create Contacts
I often find myself creating a Outlook contact from the signature in an email or some text in a work document. Rather than do it by hand each time, I have put together a few vba commands and a new vba class to parse the text on the clipboard and create a new contact from what it gathers.
[ more ]Export All Contacts as vCards
Outlook has the built in ability to export contacts as vCards, but it will only do it one at a time. With the following vba script and a few bash commands, you can batch export each contact as a vCard and then combine the individual files into one vcard file.
[ more ]Auto Color Cells
Many users of Excel have made it common practice to color code cells to help identify inputs, formulas, etc. For example, it is common to color all cells act as hard coded inputs (i.e. not a formula) blue, all formulas black. This Excel macro looks at the contents of each selected cell and sets the color appropriately. Further I have added the green coloring for all external references.
[ more ]Forward E-Mails as They Are Sorted
I often find myself creating a folder to store all the messages relating to a
particular project, and then wanting to forward any message placed in that
folder to one of my colleagues. This code, when placed in the
ThisOutlookSession module, takes care of the forwarding for me.
Toggle Under/Overlines
When formatting a Excel sheet underlining or overlining (which appears as if you underlined the cell above) a cell often looks much better than just underlining the contents of the cell (ctrl-u). This macro will toggle the under/overlines for all the selected sells.
[ more ]Outlook Folder List
For the previous hack, I often had a hard time finding the correct folder to monitor. This bit of code will list all the top level folders for you.
[ more ]Create Spacing Rows
I often want to have some space between row to call attention to a particular row, but rather than having a full row, a small row would work better. This macro will adjust the height of all the select cells if they are empty.
[ more ]Outlook Folder List (Updated)
Again, I needed to find the path to particular folder. This one was deep and not under my Inbox. So, updated the folder list function. It is now recursive and (very simply) shows the structure.
[ more ]Align Center
I hate merged cells. They create all sorts of problems adding/deleting
columns, filling down, etc. But it can look nice to have text centered across a
range not just a single cell. Luckily, Excel provides the rarely used
Align Center formatting option. This macro provides easy access to
toggling the alignment formatting across all selected cells... but that's not
all... :-) it also centers the contents of a single cell if that is all that is
selected.
Process All Outlook Events
I recently needed to walk through all the events in an Outlook calendar and make a change. Here is the simple code:
[ more ]Toggle Bullet and Sub-Bullet
When I feel like getting fancy, it can be nice to include a bulleted list in an Excel sheet to describe assumptions, etc. This is actually pretty easy to do, but requires adding some odd characters. This macro will add a character and change the font of a cell to create a bullet. If you run this macro on a cell which already contains a bullet, an arrow shaped sub-bullet is inserted instead.
[ more ]Backup Current File
This is one of my favorites. It saves a copy of the current file in the 'Backup' directory if one exists under the directory in which the file is currently saved. It saves the files with an incrementing two digit number after the filename (before the .xls extension). A cap of 50 backups is imposed just to keep from taking up too much disk space (my models tend to be BIG).
[ more ]Select Alternate Columns
I often like to have narrow empty columns between data columns just to make things look nice (cell underlining looks better that way in my opinion). This macro will prompt you for a number of columns per group and then it selects one column per group for the currently selected range (i.e. selecting A5:G5, running the macro and entering 2 would result in columns B, D, and F being selected). Then you can quickly resize those columns to make everything look real pretty.
[ more ]Outlooks Spam Handler
The spam filters that we use at work, process all the messages in a particular folder to train the filter. Rather than drag and drop messages, I use the following code to move the selected or active message into the target folder. For each of the public subs, I have a toolbar button which runs the code.
[ more ]Outlook Junk Mail - Old
The following code worked for older versions of Outlook (2000 I believe), but does not work for newer versions. There used to be a junk button on the toolbar. The code effectively activated that button. I'm not sure how to do it in newer version of Outlook. I have actually given up on Outlook's spam filtering and use SpamAssassian now. You might check out Wininspector to track down the right object.
[ more ]Combine Cells
This routine combines the selected cells into one long string in the current cell.
[ more ]Swap Note and Formula
Here are two routines that pull the formula from a note and put the formula in a note. I had a very specific need for this, but I can't recall why now.
[ more ]