Daily Dose of Excel

Hi everyone!

As you may have seen on some blogs, Microsoft now enables you to include any Excel file -or parts thereof- in your web pages and blog posts.

For example (yes you can make changes to the cells, they are not retained):

I have written an article that explains how this is done.

Even more: I have also created a demo where you can enter information in a web form (a textbox), which in turn updates information in the embedded Excel web app file.

Enjoy the read: Embedding Excel files on your website

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi everyone!

Yesterday I experienced the tedious task of figuring out what the differences are between the VBA projects of two different versions of a customer project I built a long time ago. Luckily there are tools to compare differences in text files, such as ExamDiff.
But the VB editor doesn’t give you an easy way to export all of your code into a single textfile. So I figured I’d roll my own. At first I simply let the routine run through all VBComponents of the VBA project.

To my surprise, the order of the components in the generated textfiles wasn’t the same for both workbooks, even with the exact same components in there.
So I decided I’d better first make a list of all VBComponents, sort that list and then export the content to a textfile.

Download ExportVBAProject here.

Enjoy!

Jan Karel Pieterse

Hi All,

I’ve just updated Charles Williams and my Name Manager utility.
New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name.

I’m now off to remove one feature request from the list.

Any wishes? For Name Manager that is

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi all,

I had a bit of time to spare the other day (got snowed in during travel by train) and wrote a couple of pages about a new Excel 2010 feature: Pivottable slicers

I show how to customise them and also included some VBA examples on how to work with them.

Enjoy the read!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi folks,

Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites.

Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that!

Anyway, as I was looking at my web stats today I discovered a nice feat: We’ve just passed the 100,000 download count on the tool (this excludes the downloads from Charles’ site, so we can safely assume the true number is at least 50% more than that). Time for a celebration:

Hurray!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

In Preventing auto_open and Workbook_Open events from running I descibed how to do exactly that. Well, it appears that the methods I gave do not always work with Excel 2007.
A visitor of my website complained he could not prevent his Auto_Open macro from running and during a BeamYourScreen session I took over his desktop and confirmed he was right (of course he was).
The situation where we couldn’t prevent an Excel automacro from running was like this:

- You have set up a trusted folder
- The folder is on a network share
- You’re using the UNC path to that folder.

Since I don’t have a network, I can’t easily test this.

Can anyone confirm whether the shift key trick works in these cases:

- Trusted folder on network (but with an assigned drive letter)

Same as above, but for Office 2010?

Thanks!
Jan Karel Pieterse
www.jkp-ads.com

I frequently use the camera tool to create pictures linked to cell ranges.
For example to be able to have different column widths beneath each other on one sheet.

They have one major drawback however: they can slowdown VBA performance (when updating cells) enormously (update time may go up from .2 secs to as much as 8 seconds for the same code).

My workaround:

Each camera tool object uses a defined name, defined such as:

Then I use these two tiny subs to turn the picture updating on and off:

Works a treat.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi there!

Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time.

Opening the file in Excel directly gave unwanted results, similar to this:

I have added an article to my website which details out how to set things up properly to save you some work:

Importing text files in an Excel sheet

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

Hi everyone,

The release date of Office 2010 is closing in and with this new version we’ll have a new programming challenge. Office 2010 comes in a 32 bit and a 64 bit version.
Especially API function declarations need to be adjusted for the 64 bit environment.
We’ll have to change a declaration like this one:

To:

So how do we know whether a Long needs to be changed to LongPtr, or to LongLong, or maybe even can be left unchanged? I decided it would be useful to gather a list of declarations on my website so we have a one-stop place where we can find the proper syntax for these things.

See:

Declaring API functions in 64 bit Office

If you have some additional API functions I could include, please let me know!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi All,

Many users (including customers of mine) have experienced trouble with two recent security updates by Microsoft, see:

http://support.microsoft.com/kb/973475

and

http://support.microsoft.com/kb/973593

There seem to be two hotfixes to this problem:

KB973475 Excel 2003 hotfix package :

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=978908&kbln=en-us

KB973593 Excel 2007 hotfix package :

http://support.microsoft.com/default.aspx?scid=kb;EN-US;978522

If you experience problems with your Excel, check if you have this update and if so, download the hotfix.
For other problems related to starting and stopping Excel, see:

http://www.jkp-ads.com/Articles/StartupProblems.asp

Regards,

Jan Karel Pieterse
Excel MVP

http://www.jkp-ads.com