XML and Excel

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

www.jkp-ads.com Anniversary and Website Update

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

On-the-fly data entry form

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:

dataentrysettings1.gif

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

Fixing Links To UDF’s in Addins

Hi All,

Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF).

UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s work as expected. You get into trouble when the location of the addin changes, for example because you have distributed the Addin to your co-workers and they have installed it to their local user addin folder (which is different for each user!).

As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula:
=’C:dataFixLink2UDF.xla’!UDFDemo(A1,A2).

There are a couple of ways to avoid/resolve this situation:

Use fixed location

Of course the simplest way to avoid the problem is by fixing the location of
your addin. Tell all your users where the addin should be installed (or even
better: create a setup tool that doesn’t allow it to be installed elsewhere).
Your #Name! errors will not resurface.

Don’t use an addin

Well, not exactly so, you could still have an addin. But instead of keeping your
UDF code inside the addin, you create a facility that copies the UDF routine
into each workbook that uses it.

This is a neat solution, but it requires that your user has the security option
“Trust Access to Visual Basic Project” set.
John
Walkenbach’s Power Utility Pack
uses this trick.

Redirect the UDF’s to the new location

This is the technique I’ll describe extensively in this article. The addin
checks each workbook the user opens whether this new workbook contains a link to
the addin. If so, it ensures the link points to the proper location.

Read on here!

Enjoy!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox

Hi all,

For a project I needed a quick way to display the content of an array to the user. I didn’t want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:

Description Before After
Cell Errors 100 10
Corrupt Names 1000 0
Unused styles 232 0

So I figured I’d put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown.
I wanted it to look like this:
autosizelistbox02.gif
That proved far from easy…

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Two popular tools updated.

Hi All,

As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

So far, Name Manager has been downloaded about 50,000 times since I posted it on my site.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.

Flexfind is less popular than Name Manager, the download count is at about 23,000.

Enjoy!

Of course I am open to any comments, suggestions and -most importantly- lots of praise :-)

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Bogus Compile Errors

Hi all,

Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.

Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.

Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.

Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):

exd-files.gif

These files are typically stored in this location:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms

So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.

So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Catching Paste Operations

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com