Archive for January 2008

Dealing with Ribbons and Menus - Avoiding Two Versions

Hi all

When you open your Excel 97-2003 file in Excel 2007, one that creates its own menus,
the first thing you notice is that you don’t see your menus. Where’d they go? Then you discover
them - shunted over on the Add-ins tab. Not exactly the ideal user interface.
How are your users going to find them there?

What you want is an application that runs as a first class citizen in Excel 2007, one that takes full advantage
of the ribbon, and yet runs as it always did in Excel 2003. Yet you do not want to maintain two versions of
your app. Two versions are always a nightmare, so difficult to keep in sync.

So, is this possible? Is it possible to have one version that’s at home in Excel 2007 and Excel 2003? Yes it is;
there are two approaches from me and Jim Rech of this problem on this new page on my website.

http://www.rondebruin.nl/compatiblemenu.htm

If you have suggestions or other feedback let me know.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Some Links

The blist beta opened this week and I got an invite. I think I signed up for it last November. I really like the things that I can put in fields, such as images, lists, URLs, and even other tables. There are three things that weren’t immediately discoverable by me: I don’t know how to relate two tables; I don’t know how to make a calculated field; I don’t know how to tell them what I think.

Blist launched at Demo ‘08. I watched some of the presentations yesterday. That site must use ActiveX because it wouldn’t work in Firefox. There were a few interesting ones and a lot that solved “problems” that I’ve never had and can hardly conceive.

I’m not sure if I’ve mentioned this before, but xldennis release .Net Co.

With the tool you can:

* Store created VBA code, code snippets / procedures / modules, and SQL Queries in a well organized way enabling you to easily reuse the code in all kind of Excel VBA solutions.
* Create connection strings to a various number of databases with two wizards, the .NET Wizard and the Data Link Wizard. Store the created connections strings in a structural way enabling you to easily reuse the connection strings in all kind of Excel VBA solutions.

.NET Co Library has been designed so it also can be shared within a group of VBA developers over a network.

I don’t use a code library, I just open a previous project and copy what I need. But I’ve often wanted to use one to be more organized about it. I’m going to try this one out and see if I like it.

Last night I downloaded agilegraph. It claims it will turn my spreadsheets into interactive graphs. I’m not much of a charting guy, but I’m interested to see how it works. I had to download .Net 2.0 to install it. I can’t believe I haven’t needed 2.0 for anything yet.

An Interesting Story

My friends and family often comment to me that they read my blog, but that they generally don’t understand it or find it interesting. From time-to-time I’ve thought of skewing some of the content toward the beginning to intermediate Excel user, but it’s obviously never happened. The fact is, if I don’t find it interesting it will be difficult to motivate myself to blog about it. And if my post count falls any lower, it will be negative. There’s a lot that goes into a blog post that makes even simple topics time consuming. I have to set up a scenario, test it (I know you’re watching and will call me out if I’m wrong), and upload some screen shots. And this witty prose just doesn’t flow out of me you know. But it’s a labor of love if the topic is at least a little interesting to me.

Anyway, DA asked that I post a funny photo or a joke. Pass. Instead I’ll tell a non-Excel related story.

In the early ’90s, while attending college, I found myself with a large quantity of aluminum cans. I don’t recall if I was collecting them for a purpose, like recycling, or if I was in one of my Sanford and Son stages where disposing of non-foodstuff trash wasn’t a priority. In any event, there I was with a lot of cans. I decided to do something with these cans.

Outside of my job, my primary exploits at the time were skipping school and drinking beer. I assume I was fully engaged in the latter, but I was on Christmas break from school so I couldn’t do the former. Instead, my friend Andy and I decided to build a Christmas tree out of the cans. I was working in a sheet metal shop so I had access to tools and supplies that the average college student didn’t have.

We started by cutting a three-foot diameter disc out of sheet metal, probably 24 gauge. We cut a small hole in the center and then made four cuts from the center to the edge at 90° angles to each other. They were cut at such a length that when I bent them up, it formed a 3″ diameter hole in the center of the disc. I used 3″ dia. steel pipe (principally used to vent bath fans before flex became more common) as the tree trunk and it extended 1 foot below the disc and four feet above. Next we strung wire from the edge of the disc to the top of the pipe. Before fastening it to the top, we punched holes in the bottom of the cans and strung them along the wire. Pre-can, it looked like this:

As pointless as that exercise was, we were pretty proud of our tree. So proud, in fact, that we decided to have a party to celebrate. We took the tree to Andy’s house and took the remaining cans to wherever you get paid to take cans. I think we made about $80 on the rest of the cans. Flush with cash, it only seemed right to go all out. So we rented tuxedos for our party. We changed into our tuxedos and awaited our guests.

I don’t remember what we served to eat at this party, but I know we had a big bowl of egg nog, my favorite holiday tradition. We also had at least one bottle of champagne, which served as our door prize. Whomever brought the best ornament for our tree would win the bottle of $4 champagne. That person was our friend Matt who glued condoms onto a standard ornament. Creativity didn’t exactly run rampant in that crowd, but a winner’s a winner.

We awarded the alcohol to Matt who immediately opened it so that he wouldn’t have to drink it himself (remember it cost $4). He unwrapped the foil, untwisted the twisty thing, pointed for the ceiling and BAM! The plastic cork flew into Andy’s living room light fixture. Tiny glass fragments rained down into, among other things, the bowl of egg nog.

We had six unopened containers of egg nog still in the refrigerator, so we were covered there. In fact, at the end of the night we still had six containers. We could have washed that bowl in scalding water, then launched it into the vacuum of space, and still nobody would have drank egg nog out of it. We could have thrown that bowl away, went to the store, bought a brand new bowl, opened the packaging, and filled it with egg nog. And still nobody would have drank. I think it’s human nature that when you see shards of glass fall into a dish, you’re not going to eat or drink out of that dish or anything similar. Well I love egg nog, so it was no skin of my shin that I had to take three containers home with me.

I went back to school that Spring and continued to live in relative squalor. In April of that year, I was on my couch when I smelled something awful. I had a subscription to the Wall Street Journal that semester, so my floor was virtually carpeted in unread newspapers. The college student rate for the WSJ was so cheap I couldn’t pass it up. I have no idea how anyone could read that much news in one day. It would take me all day to read the whole thing. But I digress. It was apparent that my policy regarding disposing of food stuffs had lapsed at some point. My solution: Move to the other side of the couch.

I lived happily on the North side of the couch until about mid-May. The snow had long melted and things were starting to heat up here in Nebraska. I could no longer avoid the odor just by changing my proximity to it, wherever it was. Fortunately it was Spring, and as I was wont to do at that time of year, I engaged in some cleaning activities. Scooping up tons of Wall Street Journal’s and filling scads of plastic trash bags.

As I lifted a stack of WSJs next to the couch, I noted a duffel bag living underneath it. I had wondered where that bag went. I opened it up and quickly learned the source of the stench. In the bag was a pair of dungarees, a shirt, socks, and, you guessed it, three unopened containers of egg nog.

I’ll spare you the details of “pouring” out those containers. The jeans and other clothes are rotting in a landfill somewhere amidst a bunch of Wall Street Journals. The smell had permeated them so thoroughly that I doubt I could have convinced a bum to wear them. I had a lot of wacky adventures in that apartment. Although if you thought that story was disgusting, you might find the other stories more on the disturbing side than the wacky side.

Excel security flaws out in the open

You have to love this quote:

“IT guys should tell end users right off the bat that if they see an unrecognizable Excel document in their inbox, they should treat it like porn — it’s not something you should be opening up at work.”

Full article here.

I guess this could probably used as another excuse to kill VBA in future versions of Excel (and Office).

CommandButtons via Visual Basic 6.0

From Ashton. When I run this code in a VB6 program, it puts an OLEObject on an Excel sheet.

Private Sub Command1_Click()
   
    Dim xlApp As Excel.Application
   
    Set xlApp = Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Add
   
    xlApp.ActiveSheet.OLEObjects.Add "Forms.Commandbutton.1"
   
    Set xlApp = Nothing
   
End Sub

But in Excel, it doesn't act like an OLEObject. Look at these context menus.

The first one was put there by the VB6 program. I added the second one manually from the Control Toolbox in Excel.

Show Picture toolbar button? What the heck is that? Does anyone know why there's a difference?

Rows and Columns

gullible.info sez:

An average Microsoft Excel spreadsheet document has 1,102 rows and 18.2 columns.

I say:

That's 470 rows and 25 columns. I had 408 sheets whose last cell was A1, i.e. blank. I attribute that to two things: I probably have quite a few workbooks that are just code; Most of the workbooks I get from other people have a Sheet2 and a Sheet3 that are unused. The default number of sheets for a new workbook is three.

I also had 15 sheets whose last cell is IV65536, which is clearly wrong. That's a typical problem with using the SpecialCells(xlCellTypeLastCell) method, but I'll just exclude those from the average.

If I don't count the empty sheets and the "full" sheets, I get:

Mean: 103 rows and 28 columns
Median: 58 rows and 11 columns
Mode: 59 rows and 11 columns

That's over about 2,000 files. It's not all the Excel files I have, it's just all of them in the MyDocuments folder. If you'd like to see your average, I've posted the code I used below. It takes a few minutes to run and I had to click a few dialog boxes that asked me to edit links or start an external application.

Sub LastCells()
   
    Dim sro As Scripting.FileSystemObject
    Dim srFolder As Scripting.Folder
   
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    Set sro = New Scripting.FileSystemObject
   
    Set srFolder = sro.GetFolder("C:\Documents and Settings\dk\My Documents\")
   
    GetLastCells srFolder
   
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
   
End Sub
 
Sub GetLastCells(srFolder As Scripting.Folder)
   
    Dim srFile As Scripting.File
    Dim srSubFolder As Scripting.Folder
    Dim wb As Workbook, sh As Worksheet, rLast As Range
   
    For Each srFile In srFolder.Files
        If srFile.Type = "Microsoft Excel Worksheet" Then
            Set wb = Workbooks.Open(srFile.Path)
            For Each sh In wb.Worksheets
                If Not sh.ProtectContents Then
                    Set rLast = sh.Cells.SpecialCells(xlCellTypeLastCell)
                    With ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)
                        .Offset(1, 0).Value = wb.FullName
                        .Offset(1, 1).Value = rLast.Address
                        .Offset(1, 2).Value = rLast.Row
                        .Offset(1, 3).Value = rLast.Column
                    End With
                End If
            Next sh
            wb.Close False
        End If
    Next srFile
   
    For Each srSubFolder In srFolder.SubFolders
        GetLastCells srSubFolder
    Next srSubFolder
       
End Sub

Multiple Parameters in External Data Queries

Laura asks:

I am trying to use one query but with multiple paremeters depending on the cell. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc.. How do I do this in Excel?

I suppose you could have a bunch of different queries - one for each cell. I think that would be hard to maintain and probably lead to a huge Excel file. I would use a helper sheet that stored one query and use formula to bring over the data I need.

First, create a query on a sheet that contains all the records for the table you're interested in. For this example, I'm using the Invoices table from Northwind.mdb (actually mine is called Nwind.mdb, but I don't know what the difference is). Place the query in A3, or if you already have it in A1, insert a couple of rows above the query. We'll need them later. If you need help creating this query, see Querying External Data in Excel.

Next, create a new sheet and put your dates in column A. Back on the query sheet, put some formulas in B1 and B2.

B1: =TEXT(MIN(Summary!A2:A9),"mm/dd/yyyy")
B2: =TEXT(MAX(Summary!A2:A9),"mm/dd/yyyy")

These will serve as the parameters for this query. It will limit the rows that are brought in, but there will still be more rows than you need. I needed the TEXT function because the ODBC driver couldn't understand Excel's dates.

Now I'll make the query a parameter query. I do this in VBA, but you can use Query Manager, Query Editor, or you can edit the SQL statement directly in MSQuery. In the Immediate Window of the VBE, I type ?activecell.QueryTable.CommandText (with a cell in the query selected, or course), and see that the SQL statement is:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices]

To add the parameters, I type this into the Immediate Window:

activecell.QueryTable.CommandText = activecell.QueryTable.CommandText & " WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);"

In the end, the SQL statement should be:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices] WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);

Now when I refresh the query table, Excel will prompt me for parameter values. Just type something that looks like a date and don't worry about what it returns. If all has gone well, the Parameters button on the external data toolbar will now be enabled. Click it to open the Parameters dialog.

I change Parameter1 to point to cell B1 and Parameter2 to point to cell B2. Now the query is set to return a limited number of rows, but it will contain all the rows you need.

The last step is make the formula on the non-query sheet. With dates in column A, I put this formula in column B to return the first CustomerID for that date.

=VLOOKUP(A2,Data!Query_from_MS_Access_Database,2,FALSE)

and I get this:

Blog Stats

When I first started blogging, I used to look at the stats about four times an hour. Now I never do. I suppose that's the true for anyone who has a website. I thought I'd take a look to see what's up. I've been using reinvigorate.net since February. I use to use it back in the day, then they went away. Now they're back. I've been pretty inconsistent with stat tracking, so I don't have the long history.



After all these years, I'm still suckling on the j-walk.com teat. Time to send John some Omaha Steaks, I guess. Keep my sugar daddy happy.

Last week I signed up for crazyegg.com because I saw their heatmap and thought it was cool looking. I couldn't capture the whole thing, so here's a couple of shots.

This was over three days. What I learned was that nobody uses the monthly archives. Why would they? I should get rid of that and put something useful there. Maybe a book-of-the-month or something. Or maybe I'll list the most commented posts. If you have a suggestion, leave a comment. If you have a website, give crazzyegg a try. It seems pretty cool.

The Previous Entries link gets a lot of love. Should I move that to the top? Or is it being used because people get to the end and that's where they want to click that link? Or should I put in both places?

If you have any suggestions for the blog layout or content, my comment box is always open.