Tuesday, June 02, 2009

Outlook/Excel Success!

Today, June 2, 2009, I completed a working solution for getting information from incoming Outlook e-mail into an Excel spreadsheet. It isn't the solution I had hoped for, but it works.

My problem before was trying to get three new buttons on the Ribbon. I could do this with ease in Excel, Word, and PowerPoint, but Outlook isn't so nice. I never did figure out how to do it — the current solution has the three macro buttons on the Quick Access Toolbar. Maybe in another year I'll have it totally integrated.

So I got the Outlook side put together, and I got the Excel side put together. My next big problem was figuring out how to get the data from Outlook VBA code (the data is in an array, for those who understand what that means) into an Excel subroutine. Passing arguments from one subroutine to another is pretty easy from within a single application, and only slightly more involved if I was going between, say, Word and Excel. But as I said, Outlook isn't very nice.

My ultimate solution there was to create a new, hidden worksheet in the target Excel workbook and filling the first column with the array data from Outlook. Then, I could call the Excel subroutine without passing it any arguments. The Excel code then rebuilds the array from the information on the temporary, hidden worksheet and deletes the worksheet.

Sloppy? Yes. But usable? YES! Big sigh of relief.

Now I can go back to redoing my Web site, which is getting kicked off of geocities on July 13. I think I have the basic style sheet the way I want it!

No comments: