Excel Pragma

Even more impressed with Wordpress 1.5. It supports pages and is truly becoming a one-stop shop for blogging. (shop ? don’t think so… it’s still for free!)

So ladies and gentlement, my first page : software I use, inspired by Daily Dose of Excel’s About page.

I saw the Goodbye CSV and Text Files – Hello XML post on AutomateExcel.com . This only confirmed the possibilities that XML opens for Excel Developers and  gives the reassurance that the time that I’ve spent so far learning and playing around with XML has been well spent ! Over the past month, I have been using XML extensively on a specific project to separate the data collection workbooks from the processing workbooks.

Practically, the project consists of collecting a huge amount of data from the client (data-entry, error-trapping, consistency checks) and then processing this data (generating database scripts for insert into Oracle).

These two operations could have been combined into a single workbook that would contain the data-entry forms, the error-checking and then the script generation process but this would result in a massive file that would probably not go through any mail servers ! In addition, some of the data needs to be re-used in other workbooks so this would also mean some serious workbook linking and good luck debugging !

Enters XML ! and here’s how it’s helped me so far:

• the project is now two layers : one for data-entry (with plenty of error-checking) and one for processing.
• the data is shared between the two layers in XML. (export to a file from one side, and then refresk from the other side)
• the data is also stored separately from the worksheets in a central location (for back-up and for re-use in other workbooks)
• the data is independent from the workbooks so upgrades on the workbooks can happen without any loss of data.
• the data can also be used in other worksheets as needed. Other worksheets can also load only the specific fields that are needed instead of loading and processing the full file.

I am sure there’s still plenty to explore in Excel’s XML . I’d like to know how you’re using it too.

Not even trying to match Colo’s recent make-over. His new site has just set the bar too high ! Excel Pragma’s modest contribution to the aesthetics space is an icon.

I hope I’m not violating any Property Rights for the icon but I don’t think I’ve seen it anywhere else. If any of you has, please let me know.

A new addition to the software I use : Iconomaker

<Updated> the Favicon shows in Firefox but not in the Rss reader. Please let me know if you can see the icon in your browser and your RSS reader.

<Updated 2> now it is working on both.

Daily Dose of Excel » JKP Application Development Services Gets Syndicated

Jan Karel recently added RSS to his excellent Excel site, ADS. I’ve subscribed via Bloglines. Clearly RSS is a terrific technology for websites that are updated frequently, like Excel Overdose Daily Dose of Excel. But it may be even more useful for static websites that are updated infrequently. ADS is not a very good example of that because Jan Karel is updating like a bat out of hell, but there are plenty of Excel sites out there that only add pages once per month or once per year. I think RSS would be invaluable on those kinds of sites.

I use FeedDemon (Bloglines and NewsGator Online Services integrated)

Finally ! it was more stressful to plan than difficult to implement. Like all other to do’s , the tough part is finding the time to do the tasks than actually doing them. So today, I upgraded WordPress to version 1.5 – Strayhorn . It is very hard to believe that this beautiful and powerful engine is free !

The upgrade is amazingly smooth but it was the smaller details that took all the time and attention. For example, WordPress now supports themes, so out goes myuglycolors.css. BUT (and there’s always a BUT) deciding on a new theme is not so easy, so I hope you enjoy the out-of-the-box WordPress Classic Theme. wasn’t that hard, was it ?

There will be a few posts shortly with new string UDFs, mostly the result of the previous weeks’ silence. This was all part of the effort to create a toolkit to process client static data and automatically generate the Oracle insert/update scripts.

The Browse For Folder is particularly useful when you’re running a file analysis routine on a specified folder or trying to specify an output folder for a list of files (e.g. SWIFT files).

On a personal note, I will try to keep this blog from turning into a glorified “Favorites” folder. but there were so many times that I needed the “browse for folder” functionality and had to go search for it again and again.

I’ve posted on Digital Certificates a couple of weeks ago. This solution works but it’s basic and personal.

I recently learned through this post on the Google Groups that Digital Certificates can also be created using the MakeCert.exe tool.

This blog has been silent… very silent… but there has been a lot of activity too.

The first and most important justification : I finally got my copy of Professional Excel Developement… (shipping to Dubai takes time, you know ?). By far, this is the best book on Excel Development that I’ve seen… if you’re a beginner, other books might be more helpful. But if you’ve already been down the Excel developement road, then this book is a must-have. It’ll help you tie all your skills together. To me, the book sets a Best Practice standard for Excel and VBA developement… Highly recommended.

Buy it at Amazon or at Amazon.co.uk.

I read a nice post about Extending Formulas on Daily Dose of Excel. The comments are full of practical tips. This got me trying out the shortcuts and I accidentally discovered that Ctrl+Alt+Tab adds left-indentation to a cell.

I am a keyboard addict so it puzzles me that I haven’t seen this shortcut before. If you’re new to shortcuts, you’ll find an excellent list at Chip Pearson’s. (The Ctrl+Alt+Tab shortcut is not in the list).

I guess there’s no point in keeping the comments off. The good comments were a big motivation to keep posting so I guess I am more than happy to suffer some comment spam.

So comments are back, keep them coming !

Thanks Rob for the note.