Making MZTools for VBA work on Vista

I was fooling around writing some code tonight, and realized that I hadn’t set up my custom error handler text in MZTools. I’ve had MZTools on here since I last re-formatted my laptop back in September, and use it all the time, but I never bothered to go in and tweak my own settings. Tonight I was working on a larger project, and wanted some customized error handlers, procedure headers and the like. I jumped into the settings, set it up the way I wanted, and clicked the OK button to save it… and Boom! An error popped up:

mzerror.png

Now, I am using the 64 bit edition of Vista on this machine, which is why it’s giving “C:\Program Files (x86)\MZTools3VBA\MZTools3VBA.ini” as the path. I took a second to browse through my machine, and discovered that the path does exist. So of the “Path/File access error”, that pretty much meant that something was gumming up the access to the file itself.

Basically, this is a permissions issue. I’m not sure if it is the same on the 32 bit version of Vista, but I’d bet it is. Vista is a *little* more security conscious that XP, but what was funny to me about this is that I am an administrator on my machine. Regardless, I decided to take a look at the file permissions. To do this:

  • Right click the file
  • Choose Properties
  • Click the Security tab

Now, on my machine there are 3 account classes listed in the box:

  • SYSTEM
  • Administrators
  • Users

When I click on SYSTEM or Administrators, I can see that they have full permissions to modify this file, as shown below:

mzpermissionssys.png

But look at the settings for Users:

mzpermissionsusers.png:

Fortunately, this is relatively easy to toggle.

  • Click on the Edit button (and say yes to the Vista warning).
  • Click on Users
  • Check the Allow box for Full Control

At this point, as shown below, all the checkboxes under “Allow” will be checked. (Except for “Special Permissions” which doesn’t appear on screen at the moment.)

mzpermissionschange.png

Click Apply, then OK, then OK again to get out of the permissions screen.

And that’s it. You’ll now be able to save your customizations to MZTools.

To be clear, this issue doesn’t actually have anything to do with MZTools, but rather is a permissions issue with Windows Vista. What really makes me shake my head, though, is that my account is an administrator account. I should not need to do this.

At any rate… I’m not sure if you can set permissions on files during installation, as I’ve never deployed a full program of my own. (I’m guessing that it is possible, and expect that someone will confirm that for me shortly. ;) )

To be very clear, the rest of the functionality of MZTools3 works flawlessly. It’s only the ability to customize your settings that runs into trouble. Given that it was written long before Vista came on board, I think it’s pretty impressive that this is the only change you need to pull off to keep it fully functional.
Oh, and if you do any programming in Office and you’re not using MZTools yet, you should be. It’s a free download that you can get right here (you want the MZ-Tools 3.0 for VB6, VB5 and VBA.) It adds a load of useful functionality, and works in Excel 97-2007, and on Windows XP and Vista.

An Excerpt from RibbonX – Customizing the Office 2007 Ribbon

I’m very pleased that Wiley Publishing has encouraged me to post an excerpt from our new book RibbonX – Customizing the Office 2007 Ribbon, which can now be found at Amazon.com and other online retailers.

Chapter 2 of the book focuses on how to access the XML layer of the new Office 2007 file formats. We cover the hard way (renaming the file to a zip format), using the free CustomUI Editor tool, and also discuss XML Notepad. The excerpt that I have picked to share, from pages 41-43 of Chapter 2, actually contains two parts.

  • The first part discusses how to create an XML template in the CustomUI Editor, which can then be applied to other files later.
  • The second part provides some notes about using the CustomUI Editor, specifically exposing the “Gotchas” that are evident in this tool.

I chose this section because it was referred to throughout the book, rather than reproduce the framing XML code. I also chose this section because Wiley has generously allowed me to post the entire Chapter 7, focusing on the comboBox and dropDown elements, on my site. By reproducing this section of Chapter 2 here it means that you’ll be able to follow the entire Chapter 7, which will be up in a day or two.

The Chapter Excerpt

Storing Customization Templates in the CustomUI Editor

You may encounter scenarios in which you want to refer to a custom UI that you previously developed. Again, you can use the CustomUI Editor to store and access custom templates, as shown in Figure 2-14.

fg0214.png

Figure 2-14: CustomUI code templatesTo demonstrate the ease and benefits of setting up your own templates, use Notepad to create a new text file on your computer and enter the following code into it:

  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon
  3. startFromScratch="false">
  4. <tabs>
  5. <!-- Enter your first tab here -->
  6.  
  7. </tabs>
  8. </ribbon>
  9. </customUI>

Save the file in the folder Program Files\CustomUI Editor\Samples with the filename RibbonBase.xml.

note.png The Program Files\CustomUI Editor\Samples path assumes that you used the default installation path for the Microsoft Office 2007 Custom UI Editor. If you installed the program to a different path, you will need to modify the preceding instruction accordingly.

tip.png If you cannot access this file in Windows Vista, it is due to the User Access Control feature restricting your permissions. You will need administrative rights in order to store these templates in the specified folder.

Finally, open the CustomUI Editor and select the Samples menu. If your new file is in the right place, you should now see the RibbonBase entry on the list. Click it and voilà! There is your XML template, which you can use to start all future Ribbon customizations!

Now that you have this on your menu, how do you use it? It’s quite simple, actually. Just create your new file, open it in the CustomUI Editor, and select your RibbonBase from the menu. It copies all of the code into your file for you, and you’re good to go! What a great way to get a head start.

caution.png If you open a file that has customizations in place already and select your template, all of your current customizations will be overwritten with the template’s code. You can, of course, close the file without saving and not lose your current work.

Some Notes About Using the CustomUI Editor

The CustomUI Editor is a fantastic utility that makes editing XML code much easier. As with all programs, however, it is not perfect. You should be aware of some CustomUI Editor “gotchas” before you dive right in:

  • The CustomUI Editor does check the form of your XML tags, as well as make sure that you only use attributes that are defined in the XML schema. What it does not do, however, is check that you have provided valid attributes within the quotes. (You’ll learn about attributes later, but be aware that you can still receive errors even if your XML is well formed.)
  • While writing and debugging your RibbonX code, it is very tempting to open your file in both the application and the CustomUI Editor at the same time. Trying to save the file in the CustomUI Editor while the file is open in the Office application will result in an error. In addition, even if you close the document that you are editing in the Office application and then save it in the CustomUI Editor, the editor will overwrite any changes that you made while editing your document in the application! It is much safer to close the file in each application before making changes in the other.

note.png If you forget and make changes in the application, you can preserve those changes by saving the file to a new filename. At least then it can be a reference if you want to incorporate the changes into the original document.)

  • The CustomUI Editor does not have a Find/Replace utility, so if you’re planning to do large-scale editing on your XML, you may want to copy your information to another program, edit it, and then copy it back.
  • When working on files that have more lines of XML than will fit on the screen, the CustomUI Editor has an annoying habit of refreshing the screen so that your cursor is always on the last line of the screen. If you are trying to make an edit and want to read the information three lines below, this can become a major irritation. Here, again, you may want to copy your XML data into another editor to work on it and then paste the updated copy back into the CustomUI Editor.

Ways to irriatate an accountant – #1

This drives me crazy…

I’m working on my year end at work, and we have a pretty tight deadline to get it out the door.  Naturally, time is of the essence, and anything that slows me down gets me a little frustrated.  This particular problem has been one of my pet peeves since we got this software in a few years ago.  So here goes… if you are building a system, and want to irritate the accountant, here’s one way:

Build a General Ledger program that doesn’t hold a true mapped chart of accounts, and suppress printing of zero transaction accounts.
I’ve used at least 4 different G/L packages where you could specify both the account and valid departments in the chart of accounts.  Try and run a G/L listing for an invalid combination, and it comes back with an error that says that you can’t.  Kind of what you’d expect really.

With this program, though, rather than put in a true mapping, they just decided that you couldn’t post a transaction to an invalid account/dept combination, but they never linked the reporting to that logic.  Instead, they just search the database for any transaction that matches.  Finding none, it won’t print a report.

Normally, that would be okay.  The problem is that this becomes an issue for valid accounts.

I am an accountant, and I need to know show a report that tells me what the balance of the account is.  I NEED to be able to print a report that shows the value is zero.  These idiots wrote their program with the following logic:

If the opening balance is zero AND the count of transactions for the period is zero AND the ending balance is zero THEN suppress the printing.

Great.  I actually do have accounts where this happens.  I still want the report, and I can’t get one.

As if that weren’t bad enough, it gives you no warning when this happens.  No error message, no nothing.  As far as you can see, it prints just like any other report… until you run down the hall to the printer… and wait… and wait… and ask everyone if they took your report… and try it again…  It usually manages to waste at least a couple of minutes before you actually figure out what happened.  :(

I put this one down to stupid programming logic, and ignorance of the true business need, personally.

Office PDF/XPS Addin

One of the big things that Office 2007 was supposed to bring was the ability to save to a PDF file, without getting extra software. Somewhere along the line, they decided to make this feature an Add-in, which can be downloaded from Microsoft. Funny enough, they have three different ways you can download it:

For my sake, I downloaded the PDF and XPS add-in to try the PDF features in Office 2007. (It won’t work in 2003.) As an aside, I have never seen an XPS file in the wild, so may revisit that format when I need to.

A few of you may wonder why I bothered with this at all, since I have a few pages dedicated to PDFCreator on my main site. The answer, sadly, is Vista. PDFCreator works great on Windows XP, (I have some revised articles to post about it, actually,) but it doesn’t work on Vista. They are actively fighting the issue, but so far it’s eluded the development team. I really hope they nail it, as I was less than impressed by the half baked PDF add-in that Microsoft has supplied.  (Update… the Vista issue has been fixed.)

Okay, maybe I’m being a bit unfair by calling it half baked. Here’s the benefits of the Microsoft Add-in:

  • It’s free
  • I will allow a user to save to a PDF file easily
  • It is found under “Save As”, rather than being treated as a printer, which could be less confusing to new PDF users
  • You can code a routine to use the PDF add-in

Honestly, the biggest thing that made me dig into PDFCreator in the first place was that it was the only PDF writer which exposed it’s object model for free. I’m rather cheap, so I didn’t want to pay for the license to do so with CutePDF, Adobe, or any other engine. Had I come upon the Microsoft version at that time, I probably would have been quite satisfied.

So what’s missing?

For starters, security. I find this pretty ironic, considering the huge push that Microsoft has been on to create secure environments everywhere. The PDF/XPS add-in does not allow you any method of setting security on your PDF documents. Maybe it’s not important to most users, but it should be an available option, IMO.

The logic behind using this add-in is also different. The logic for printing multiple sheets to a single PDF would be as follows:

PDFCreator:

  • Loop through each sheet checking if we want to print it
  • “Print” the job to temporary queue
  • Combine all the jobs in the temporary queue into a single job
  • Print the job to the PDF file

Using the MS PDF Add-in:

  • Loop through each sheet checking if we want to print it
  • Select all the sheets we want to print
  • Export all the active sheets to the PDF file

Some sample code of printing multiple specific sheets to a PDF using the MS PDF Add-in:

  1. Sheets(Array("Sheet1", "Sheet3")).Select
  2. With ActiveSheet
  3. .ExportAsFixedFormat _
  4. Type:=xlTypePDF, _
  5. Filename:="C:\Test\Test.pdf", _
  6. Quality:=xlQualityStandard, _
  7. IncludeDocProperties:=True, _
  8. IgnorePrintAreas:=False, _
  9. OpenAfterPublish:=True
  10. End With

So here’s the question… why do we have to select the array of sheets first? I hate selecting anything, as it’s slow and, IMO, unreliable. I also despise dealing with ActiveSheet, as I never trust my users to take their hands off the keyboard when the macro is running.

Now, you can print a single sheet by referring to it directly, but not multiple sheets unless you print the entire workbook. This is where PDFCreator is superior. With PDFCreator, I can loop through the sheets, referring to them directly, printing the ones I want, and combine them into one job.

Another really big thing… while I have not done it yet, I can see no reason why you wouldn’t be able to print reports from multiple applications to a single PDF using PDFCreator. That could absolutely not be done with the MS version, as you’re stuck with the current application by nature of the “Activesheet” issue. Come to think of it, maybe I should write a routine to demonstrate the process using PDFCreator. Being able to print Word, Excel & Powerpoint documents to a single PDF could make for some really nice reports…

The final thing that I can say in PDFCreator’s favour over the MS add-in is that PDFCreator can be used manually from any program, as it’s just a printer. Not so with the MS add-in which is specific to the Office suite.

In Microsoft’s defense, I will have to admit that there is much less code involved using their Add-in. A large part of this is due to the fact that you don’t need to bind to the PDFCreator class to get going, clean up, etc…

At any rate, I’d be remiss in not showing a couple of other MS PDF examples as well before I end the post, so here goes:Here’s an example of printing the active sheet to PDF, which does not automatically open the completed PDF.

  1. With ActiveSheet
  2. .ExportAsFixedFormat _
  3. Type:=xlTypePDF, _
  4. Filename:="C:\Test\Test.pdf", _
  5. OpenAfterPublish:=False
  6. End With

And here’s an example of printing the entire workbook to a PDF file. This one DOES open the completed PDF in Acrobat once it is complete.

  1. ActiveWorkbook.ExportAsFixedFormat _
  2. Type:=xlTypePDF, _
  3. Filename:="C:\Test\Test.pdf", _
  4. OpenAfterPublish:=True

Naturally, you can also have it print to a variable sheet location such as:

  1. ActiveWorkbook.ExportAsFixedFormat _
  2. Type:=xlTypePDF, _
  3. Filename:=Worksheets("FilePath").Range("A1").Value, _
  4. OpenAfterPublish:=True

I’ll admit that despite the fact that the code for PDFCreator appears more complicated, I’ve got a lot of time invested in it, and know it fairly well. That may make me a little biased, but I really enjoy the robustness of what PDFCreator has to offer. I feel that it gives me more control over things, which is very important to me. I just wish that the team could get their Vista issue sorted.