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.
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:
Sub TurnOnPictures()
ThisWorkbook.Names(“PicsOn”).RefersTo = “1”
End Sub
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
Hi all,
I decided I need a bit more content on my site for the average to intermediate Excel users:
Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your sheet to offer choices to the user. Moreover, you can add scroll bars and spinners to quickly control values in cells. These elements are called controls because they enable your user to control the content of your model. This article shows how to put controls to use in your model.
Enjoy the read and let me know what you think!
Regards,
Jan Karel Pieterse
www.jkp-ads.com