Daily Dose of Excel

My previous post went a bit unnoticed, but I bet this one might interest some of the Office developers…

In that post, I showed how to access and modify existing parts of an Office OpenXML package. This opens up the path for us to add ribbon customisation code to an Office file. For this to happen, a couple of modifications were needed to the code in the class module I showed earlier. Fellow Excel MVP Ken Puls was kind enough to make some modifications to the class module, which I refined a little. The results are on this page

Enjoy!
Regards,
Jan Karel Pieterse

Hi Everyone!

Today I finally found time to actually post a brand new article on my website.

Wouldn’t it be useful to be able to edit the contents of an Office 2007 OpenXML file from within VBA? Well, now you can, using the sample code and explanation in this article.

Enjoy!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi all,

The word is out!

We now have an official name for Office 14 and some sort of a date too.

Regards,

Jan Karel Pieterse

Hi everyone,

With all these Euler posts I thought, why not post my own?
Suppose the following:
I have a products table set up like this:

Code ProdName Price
0001 Product1 556.68
0002 Product2 977.41
0003 Product3 350.62
0004 Product4 509.16
0005 Product5 748.4
0006 Product6 802.96

(list goes on to as much as 5000 products)
And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two columns, which may be unsorted).
Currently I load the list into a variant and add the items one at the time into a three column listbox.
As the user enters a search string, I have an If statement inside the loop to detect if an item needs to be added to the listbox or not. This (as you can imagine) is a slow process.
My task for you: how would you solve this problem so the filtering is as fast as possible?

Regards,

Jan Karel pieterse
www.jkp-ads.com

Hi all,

I’ve just published a new article on my website. Here is the introduction:

“This article explains how you can use styles to ease maintenance of your spreadsheet models.

Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.

By consistently using cell styles (instead of changing parts of the cell’s formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.

I therefore consider Styles as being underused, underestimated and under exposed.”

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi everyone,

I have had a Dutch article on XML and Excel on my site for quite some time now, but never got round to translating the thing into English.

Well, yesterday I found a bit of time during a long train-ride, so here it is:

XML And Excel

From the intro:

Microsoft Office 2003 Professional was the first Office version that took the XML standard seriously. The XML standard has been devised to ease the markup of data (especially on the web). A well known example of the use of XML are RSS feeds with which one can gather news from web pages. In reality, these so-called RSS-feeds are nothing less than XML files. This article introduces XML and shows some things that can be done with it (specifically in Excel).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi everyone,

Already 5 years have passed since I founded my company. And I must say those years passed in the blink of an eye.

I’ve never enjoyed my work as much as in these past years. Even if at times things were hectic and I worked crazy hours. I’ll never start working for a boss again (if I can help it)!

My conclusion after my first half-a-decade: If you think you’re good at something (even if it is outside of your current field of work), strongly consider becoming self-employed. It’ll take you a year or two to get up and running, but if you’re an independent kind of person you’ll love the “being in control” feeling it will give you. Very rewarding.

I felt a 5 year anniversary needed more than just posting about it here. My website hadn’t been redone in the same amount of time (except maybe for some bells ‘n whistles) and I had already thought I might give this new-fangled Microsoft Expression Web (EW) a whirl.

So I downloaded and installed the thing and imported my current Frontpage web into EW. Sheesh, what a huge difference in UI. EW seems much more aimed at the web developer than at the casual let-me-build-me-a-site-for-my-club kind of user.

Luckily I bumped into a course which seemed tailored at what I was about to do:
Migrating from FrontPage to Expression Web
I subscribed, followed the course and here is the result:

www.jkp-ads.com

Kudos to Tina Clarke and Patricia Geary (both Frontpage MVP’s) who were the course writers and -instructors. Excellent job.

So, have a look at my new site and let me know what you think.

Regards,

Jan Karel Pieterse
JKP Application Development Services

Hi all,

So after Dicks (nice to read) Alive and Well , let’s do some Excel/VBA stuff again.

I intend to get a bit of discussion on this one, so bear with me.

I’m currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of multiple data tables, each in its own worksheet. The customer needs to be able to edit the data in these tables.

One of these might contain these fields:

empId
empCompanyId
empRegNo
empFirstName
empLastName
empDeptId
empFunctionId
empDOB
empGender
empTitle
empFunctionGroup
….

The way I would normally have done this is by creating a userform with a control for each field and all the coding that is needed to handle record selection and stuff. (and yes, I do know there is MS-Access :-))

But since I have an odd 5 worksheets to handle I decided it would be nice to have a generic data entry form that would build itself using a companion worksheet for each data sheet.

For each worksheet that requires data entry I inserted a companion sheet with this information:

My VBA code inside the userform’s code module reads this sheet and builds the controls accordingly.
The form has a couple of properties I can set to control appearance. All it now takes to show the data entry form for worksheet “oSh” is this bit of code:

Set frmDataEntry = New ufDataEntry
With frmDataEntry
Set .Source = oSh
Set .SourceSettings = ThisWorkbook.Worksheets(oSh.Name & “_Fields”)
.Title = sTitle
.RowCount = 14
.FieldWidth = 120
.LabelWidth = 150
.Label2FieldMargin = 12
.VertMargin = 3
.HorMargin = 6
.Initialise
.CurrentDataRow = 1
.Show

I’ve got this all up and running, including Validation and all (and yes: I’ll be writing this all up in detail some day).

Now to the questions of the day:

What do you think about the method I chose?
What alternative solutions have you come up with in the past?

Regards,

Jan Karel Pieterse
www.jkp-ads.com