January 10, 2008, 11:19 am by Dick Kusleika
I received the following error message when trying to create a new database query in Excel 2003:
Can’t access table C:Documents and Settings…My DocumentsTester.mdb.qryTest.
I found a few knowledge base articles, but nothing relevant to my situation. I almost never use the wizard to create external data queries, but I thought I’d give it a shot. Using the wizard, I get this error:
Undefined function ‘Nz’ in expression.
Well that’s a little more helpful. In fact, I do use the Nz function in the query I’m trying to bring over. I changed from:
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Nz([MyNumber],100) AS Expr1 FROM tblTest;
to
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1 FROM tblTest;
and it all works fine. Oh, except for this persistent little problem.
The moral is that MSQuery doesn’t like the Nz function.
January 9, 2008, 7:35 am by Ron de Bruin
Hi all
If you have the problem above in Excel 2007 read this page
http://www.rondebruin.nl/password2007.htm
It seems that not all machines have this problem.
I have two Vista machines that will run the code without adding the DWORD.
Very strange and I really like to know why.
Ron de Bruin
http://www.rondebruin.nl/tips.htm
January 8, 2008, 11:19 pm by Dick Kusleika
Fellow MVP Nick Hodge has a new Excel site at ExcelUserGroup.org. Whenever Nick does something, it’s always first class. Check it out.
Look how skinny he is.
January 8, 2008, 4:17 pm by Tushar Mehta
Intrigued by the new Google Chart API, I decided to explore how to integrate Excel with it. My approach was to add a WebBrowser ActiveX control to a worksheet and then manipulate the HTML elements of the document in the browser. A couple of samples of how multiple charts float in the web browser window:
and
Another interesting consequence of this approach is that one can click-and-drag any of the charts from the browser window to the worksheet. This results in a static image of the chart.
The preliminary work is at
Using the Google Chart API with Microsoft Excel
http://www.tushar-mehta.com/publish_train/xl_vba_cases/excel_google_chart_api/index.htm
January 4, 2008, 12:53 pm by Dick Kusleika
Recently I got a workbook with this formula:
=F57+F52+F43+F35+F25+F16+F10+F8+F59+F61+F60
I’m sure you’ve seen a few formulas like this. I know I have. I may have even made one or two. There is a better way, however. Usually a formula like this means there are several lists in the column with subtotals and this formula returns a grand total. The problem is when you want to add a new list or item and include it in the total. It’s not hard to add a +F62 to the above formula, but if you forget to do it, it’s not always obvious that the formula is wrong.
I changed the above using the SUBTOTAL function (with 9 as the first argument because I’m summing). One really nice feature of SUBTOTAL is that it ignores any cells that have a SUBTOTAL function in them. I can SUBTOTAL the whole range, and as long as I’ve used SUBTOTALs within the range, I don’t have to worry about double counting. If I add a new item in the middle, the SUBTOTAL range will adjust to accommodate.
Here’s an example of the old way:
Here’s the same example using SUBTOTAL:
