frice's WebLog

SP1 Fix Affecting 64 bit Solutions in Access

frice072050 — Mon, 23 May 2011 16:48:25 GMT

We are on-track to deliver SP1 in mid-summer 2011 (here is the official announcement). We definitely recommend this update for all Office 2010 users. If you’re one of the few 64-bit Access users working with compiled Access databases (ACCDE, MDE, and ADE files), be sure to check out this KB article that details an update process you’ll need to go through to have your files work properly with SP1.

Add-in that lists the IDs (idMSOs) of context menu controls is now available

frice072050 — Wed, 01 Sep 2010 16:24:11 GMT

As part of Office 2010 extensibility, it is now possible to extend Context Menus exists, e.g. adding new buttons to a particular context menu in Word/Excel/etc.. To extend a particular Context Menu developers need to know the id name of that particular Context Menu; for that purpose UEX publishes every release a list with all the names of all the extensible controls in Office. However, is not easy for developers to look at the huge list and find the context menu he/she wants to extend because sometimes the names are not meaningful. To help developers with this task, Julián Argil Torres has developed a small add-in that adds at the end of the context menus a button with its name, also if the developer clicks the button, the name is stored in the clipboard.

You can find a link to the add-in at http://msdn.microsoft.com/en-us/office/aa905530.aspx

What's New for Office 2010 Developers white paper is live

frice072050 — Fri, 27 Aug 2010 21:03:34 GMT

Learn about some of the new features and technologies for developers in Office 2010. View the paper at http://msdn.microsoft.com/en-us/library/ff955606.aspx.

Introducing a new type of help for developers

frice072050 — Fri, 02 Apr 2010 21:57:00 GMT

The Microsoft Developer Center introduces a new type of help documentation for developers. They are called Quick Notes and are intended to give you just the information you need to get common tasks accomplished.

They are very task oriented with no lengthy explanations. Just the steps necessary to get things done. There will be Quick Notes focused on Office tasks and SharePoint tasks. So check these out, with more on the way.

Quick Note: Getting Started with Web Part Development in SharePoint 2010

Quick Note: Connect Web Parts in SharePoint 2010

Check out the new article from Excel MVP Ron de Bruin

frice072050 — Thu, 11 Mar 2010 21:19:00 GMT

The article titled Using VBA in Excel to Send Workbooks and Ranges through E-Mail with Outlook (Part 1 of 2) has been publish. In this, the first of a two-part series, Ron describes ways of sending Excel workbooks, worksheets, and so forth to Outlook by using the Outlook object model. Ron also provides links to a cool add-in that he has created. Check it out!

New Office Talk column on using Office Open XML is now available

frice072050 — Thu, 07 Jan 2010 21:01:00 GMT

I've written a new Office Talk column on creating form letters in Word 2007 and Word 2010 by using bookmarks and Office Open XML. The column, titled Creating Form Letters in Word by Using Bookmarks and Office Open XML Files is available at:

http://msdn.microsoft.com/en-us/library/ee956565(office.11).aspx

In the column I describe how to create a Visual Studio console application in both C# and Visual Basic .NET. The project uses automation to retrieve addresses from an Excel workbook, and populates a sequence of bookmarks in a Word document (created from a template) which has been opened as an Office Open XML package. One form letter is created for each address retrieved. Check it out.

Lots of new resources for the Office 2010 Backstage View UI and 64-bit Office

frice072050 — Fri, 20 Nov 2009 21:54:00 GMT

Be sure and take a look at the following articles and downloads for both the Office 2010 Backstage view and the 64-bit version of Office. Also stay tuned for additional articles, code samples, and videos related to the Office 2010 UI.

Download: Office 2010 Schema: Fluent User Interface Schema (http://download.microsoft.com/download/F/F/3/FF36D32F-4080-494B-83B8-49DADC68A999/Office2010FluentUISchema.exe)

Download: Office 2010 Help Files: Office Fluent User Interface Control Identifiers (http://download.microsoft.com/download/0/7/B/07BF35CE-4FA1-4DED-B4AA-3801D4E49F89/Office2010ControlIDs.exe)

Introduction to the Office 2010 Backstage View for Developers (http://msdn.microsoft.com/en-us/library/ee691833(office.14).aspx)

Customizing the Office 2010 Backstage View for Developers (http://msdn.microsoft.com/en-us/library/ee815851(office.14).aspx)

Deploying a Customized Ribbon and Quick Access Toolbar in Office 2010 (http://msdn.microsoft.com/en-us/library/ee704589(office.14).aspx)

Targeting User Interface Solutions to the 2007 and 2010 Releases of Microsoft Office (http://msdn.microsoft.com/en-us/library/ee704588(office.14).aspx)

Customizing Context Menus in Office 2010 (http://msdn.microsoft.com/en-us/library/ee691832(office.14).aspx)

Ribbon Extensibility in Office 2010: Tab Activation and Auto-Scaling (http://msdn.microsoft.com/en-us/library/ee691834(office.14).aspx)

Compatibility Between the 32-bit and 64-bit Versions of Office 2010 (http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx)

Enjoy!

The Office Developer Resources Ribbon UI Add-in

frice072050 — Fri, 17 Jul 2009 01:27:00 GMT

The Microsoft Office Developer Center (ODC) (http://msdn.microsoft.com/office) contains a tremendous number of how-to articles, videos, code samples, and so forth to help you create Office solutions. The menus and links on the ODC are pretty handy for navigating the labyrinth of helpful information. However, I thought it might be helpful to create a tab on the Ribbon UI that enables you to get to this information from inside of Word 2007.

I created a Visual Basic .NET shared add-in in Visual Studio that creates a tab on the Word 2007 Ribbon. The tab is divided into groups that somewhat mirror the tabs found on the ODC. The groups contain buttons and gallery controls that contain links to different pages in the ODC. As with any custom Ribbon customization, the add-in contains an XML file that defines the structure and components that make up the tab. The add-in also contains callbacks that give the controls their functionality.

Note The add-in is available at: http://code.msdn.microsoft.com/OfficeDevResourceTab/

Figure 01. The Office Developer Resources tab in Word 2007

Figure 02. The Office Developer Resources tab in Word 2007 - continued

In this posting, I won’t go into the steps to create the add-in. You can find detailed procedures for creating a shared add-in to customize the Ribbon UI in the three-part series of articles titled Customizing the 2007 Office Fluent Ribbon for Developers (http://msdn.microsoft.com/en-us/library/aa338202.aspx). I will provide the XML and the VB .NET code for the callbacks which you can substitute for those found in the articles.

The XML is relatively simple in that it consists of a number of repetitive groups containing buttons and a couple of gallery controls. The buttons provide links to the different page on the ODC. The galleries provide a selection of related links to the developer centers for the Office 2007 products and the technologies that use or are used by the Office system such as Office Business Applications (OBA) and the Open XML file format.

<tabs>

</group>

</gallery>

</gallery>

</group>

</group>

</group>

</group>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

In looking at the XML, I handled the links to the Web pages in two ways, depending on whether I was using a button or a gallery item. For buttons, I added the link to the tag attribute inside the button definition XML. Then, I specified a callback in the onAction attribute where all I did was run Internet Explorer and pass the button’s control.tag value, as shown here.

Private Const ie As String = "c:\program files\internet explorer\iexplore "

Sub openPage(ByVal control As IRibbonControl)

Dim RetVal As Object

RetVal = Shell(ie & control.Tag, vbMaximizedFocus)

End Sub

In the gallery control, there is no tag attribute so I had to use a different technique. When you click an item in the gallery, a callback, specified in the onAction attribute of the gallery control, is called passing in an IRibbonControl object, which represents the control that made the call. The IRibbonControl object includes the Id property. In the callback, I test the control’s Id property in a Select Case statement and assign the Web page address value for that control to a String variable. Following the Select Case statement, just as in the previous code sample, I run Internet Explorer, passing the constant for the Web address and page value of the specific Web page.

Private Const address As String = "http://msdn.microsoft.com/office/"

Sub galOpenPage(ByVal control As IRibbonControl, ByVal id As String, ByVal index As Integer)

Dim RetVal As Object = Nothing

Dim page As String = ""

Select Case id

Case "Access"

page = "aa905400.aspx"

Case "Excel"

page = "aa905411.aspx"

Case "InfoPath"

page = "aa905434.aspx"

Case "Outlook"

page = "aa905455.aspx"

Case "PowerPoint"

page = "aa905465.aspx"

Case "Project"

page = "aa905469.aspx"

Case "SharePoint"

page = "aa905503.aspx"

Case "SPDesigner"

page = "bb421303.aspx"

Case "Visio"

page = "aa905478.aspx"

Case "Word"

page = "aa905482.aspx"

Case "WSS"

RetVal = Shell(ie & "http://msdn.microsoft.com/en-us/sharepoint/default.aspx", vbMaximizedFocus)

Case "OBA"

page = "aa905528.aspx"

Case "FluentUI"

page = "aa905530.aspx"

Case "OpenXML"

page = "aa905545.aspx"

Case "VBA"

RetVal = Shell(ie & "http://msdn.microsoft.com/en-us/isv/aa905357.aspx", vbMaximizedFocus)

Case "VSTO"

RetVal = Shell(ie & "http://msdn.microsoft.com/en-us/vsto/default.aspx", vbMaximizedFocus)

End Select

' You have already run the shell on these cases.

If (id <> "VBA" And id <> "WSS" And id <> "VSTO") Then

RetVal = Shell(ie & address & page, vbMaximizedFocus)

End If

End Sub

You will notice that I handled the Case statements for three of the controls slightly differently. The addresses for those sites were different than those for the other controls, so I couldn’t use the same address constant.

The next task concerns the images shown for each button and gallery item. As you can see in the XML, the majority of the buttons and gallery items use images built into Office as denoted by the imageMso attribute. However, for the remaining Bitmap images, I first added them to the add-in as project resources. How to do this is also described in Part One of the three-part series of articles I mentioned previously.

There are a couple of ways to add custom images to the Ribbon and I use both here. The most common way is to use the loadImage attribute of the <customUI> element and point to a callback that loads the images. By using this method, all of the images are loaded once when the Ribbon is displayed. Then, all you need to do to use a particular image for a control is use the image attribute and assign the name of one of the images to it. The following callback is used to load the image for the VSTO item in the Technologies gallery.

Function getVSTOImage(ByVal imageId As String) As IPictureDisp

Return PictureConverter.IconToPictureDisp(My.Resources.VSTO)

End Function

I used this callback for just the VSTO item because the gallery item attribute does not include a getImage attribute (I will use that attribute in the next section). So to summarize, the image is loaded when the Ribbon is displayed and the image attribute specifies which image to display for the control. Note that the image has to be returned to Office as an IPictureDisp object. I’ll show how the image is converted shortly.

The other way to load custom images is by using the getImage attribute of the button to point to a callback that individually loads the images. Similar to the way I assign Web page links to the various buttons, in the getImages callback pointed to in the getImage attribute, I use a Select Case structure to test the Id property of the IRibbonControl object and return the image appropriate to the control.

Function getImages(ByVal control As IRibbonControl) As IPictureDisp

Select Case control.Id

Case "devCenterBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.MSDN)

Case "spBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.MSDN)

Case "officeSysBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.officesystem)

Case "codePlexBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.CodePlex)

Case "codeGalleryBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.CodeGallery)

Case "channel9Btn"

Return PictureConverter.IconToPictureDisp(My.Resources.Channel9)

Case "twitterBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.twitter)

Case "facebookBtn"

Return PictureConverter.IconToPictureDisp(My.Resources.facebook)

Case Else

Return PictureConverter.IconToPictureDisp(My.Resources.officesystem)

End Select

End Function

To convert the image to the IPictureDisp object required by the Ribbon, I added the following class to the add-in project. More information about this class is also included in Part One of the article series.

Friend Class PictureConverter

Inherits AxHost

Private Sub New()

MyBase.New(String.Empty)

End Sub

Public Shared Function IconToPictureDisp(ByVal icon As Bitmap) As stdole.IPictureDisp

Return CType(GetIPictureDispFromPicture(icon), stdole.IPictureDisp)

End Function

End Class

And so that is all there is to it. Hopefully you will find this add-in helpful.

Excel's Worksheets and Sheets Collection - What's the Difference?

frice072050 — Wed, 05 Dec 2007 19:07:00 GMT

Among the top four objects most searched for on the Microsoft Developer Network by Excel programmers are the Sheets collection and the Worksheets collection (the other two are the Range object and the Application object). As an aside, I've written new articles on programming the Range and Application objects which will be published on the Office Developer Center in January 2008.

For new Excel programmers, there might be some confusion as to the difference between the Sheets and Worksheets collections. The Worksheets collection contains the items you typically think of on an Excel worksheet: rows, columns, cells, and formulas. The Sheets collection, on the other hand, consist of not only a collection of worksheets but also other types of sheets to include Chart sheets, Excel 4.0 macro sheets (also known as XLM files) and Excel 5.0 dialog sheets (allows you to create custom dialog boxes). Chart sheets are charts that take up an entire worksheet, but not charts that are inserted as part of a worksheet. The Excel 4 and Excel 5 sheets are legacy items used to maintain backwards compatibility and ease the transition from older versions of Excel to new. And just to further muddy the waters, there is also a Charts collection that is made up of chart sheets.

The Count property of the Worksheets collection contains the number of worksheets in a workbook. The Count property of the Sheets collection contains the number of all sheets in a workbook to include chart sheets and worksheets. For example, you can add a specific number of worksheets to a workbook with the following:

Do While Worksheets.Count < 5
ThisWorkbook.Sheets.Add
Loop

The worksheets you add may then become either a chart sheet or left as a worksheet, at which time, they become part of the Charts or Worksheets collection, respectively, or collectively become members of the Sheets collection. In the following example, you change the name of the last sheet in a workbook:

Dim wrkSheetName As String
wrkSheetName = "Projected Sales Chart"
Sheets(Sheets.Count).Name = wrkSheetName

Note that because you are using the Sheets collection, the last sheet in this workbook could be either a worksheet or a chart sheet. Given the name, it is likely a chart sheet.

As you might expect, the Sheets collection contains more methods than the Worksheets collection since it is home to more types of sheets. However, in both collections, there are a variety of methods to do such things as add, delete, copy, and move a sheet. There is ample documentation on these and other properties, methods, and events of the Worksheets and Sheets collections from the Excel portal on Office Developer Center on MSDN (http://msdn.microsoft.com/office).

Check out these new articles on MSDN

frice072050 — Thu, 08 Nov 2007 20:23:00 GMT

The article Calling Excel 2007 Custom Wizards from the Office Fluent Ribbon by Using VBA (http://msdn2.microsoft.com/en-us/library/bb927654.aspx) demonstrates how to create a custom wizard in Excel 2007 using VBA and then modify the Ribbon to call the wizard.

Albert Raiani does a great job in explaining how to trigger custom code when you drag data from an external source onto a Excel 2007 worksheet in his newly released article Adding Drag-and-Drop Functionality using the .NET Framework and Visual Studio 2005 Tools for Office Second Edition (http://msdn2.microsoft.com/en-us/library/bb840032.aspx). Lots of examples also.

Also, take a look at the six-part Office Talk columns titled Bringing Improvements to the Excel 2007 Table (Part 1 through 6) (http://msdn2.microsoft.com/en-us/library/bb693324(office.11).aspx) on enhancements to data tables in Excel 2007 and how working with tables is much easier than in previous versions.

Enjoy!

Cool translation feature in Microsoft Office

frice072050 — Wed, 11 Apr 2007 00:35:00 GMT

One of the coolest features in Office that I think many people (well, at least me) overlook is the translation capability. You can type in a word and get a translation immediately in any language you select. I'm currently trying to teach myself Spanish and find this feature really useful.

http://frice.officeisp.net/Miscellaneous%20pictures/OfficeTranslationFeature.gif

In most Office 2007 applications, you can find the translation feature on the Research pane in the Proofing group on the Review tab. In Outlook, right-click anywhere in the message body of an e-mail, and then click Translate on the shortcut menu.

Using Code Snippets in Visual Studio 2005 is Incredible

frice072050 — Tue, 03 Apr 2007 19:10:00 GMT

Visual Studio 2005 code snippets are a neat way to package pieces of code that you want to keep close at hand. These can be code samples that you've created or samples that you find during searches. And creating them is pretty easy with some of the free editors that are available. Such as:

Visual Basic 2005: http://searchvb.techtarget.com/tip/0,289483,sid8_gci1173726,00.html

C#: http://blogs.msdn.com/gusperez/articles/93681.aspx

I recently created and posted over 100 code snippets on the Microsoft Office Developer Center (http:\msdn2.microsoft.com\Office) to customize the Office Fluent Ribbon in Visual Basic and C# for Excel, PowerPoint, and Word using both editors and it was relatively painless to create them. You can find a link to the Ribbon code snippets and some of the Ribbon customization how-to videos I'm doing on Erika Ehrli's blog at:

http://blogs.msdn.com/erikaehrli/archive/2007/03/26/officeFluentUI_5F00_RibbonCodeSnippetsAndOfficeVisualHowToSeries.aspx

Since a lot of the header information of the Ribbon code snippets was the same, I found it easier to create a couple of the basic Ribbon customization snippets using the editors and copy and modify them in NotePad for the majority of the other samples.

Up until I started using code snippets, when I ran across pieces of code that I thought I might use at some later time, I keep them in a folder on my hard drive as a text file. Then in the heat of battle, I'd usually forget that I had the samples and when I did, I had to stop what I was doing in Visual Studio and search through them to find the right one (assuming I had given them a descriptive name).

Code snippets allow you to group similar samples together in a series of folders and then make those folders available right in Visual Studio without having to break your workflow. Just create a link to the folder(s) by clicking Code Snippet Manager on the Tools menu. Then to use, place the curosr in the desired location in the code window, right-click, click Insert Snippet, and the code is dropped into the code. Pretty sweet.

You can find more information about using code snippets in Visual Studio by searching in MSDN and also by searching the Internet.

New Visual How-to content on the Microsoft Office Developer Center

frice072050 — Fri, 23 Mar 2007 16:07:00 GMT

Just a quick note to say that there is new developer content on the Office Developer Center (http://msdn2.microsoft.com/en-us/office/default.aspx). These include over one hundred Ribbon code snippets that can be dropped into Visual Studio solutions. About half have been written in Visual Basic .NET and the other half werer written in C#. There are samples that target Excel, PowerPoint, and Word so check them out. We are also creating a ton of how-to videos on the Office Developer Center to include videos on the Ribbon by your truly, Open XML Format files by Ken Getz, and on Content Controls by Erika Ehrli Cabral. New video how-to are coming out almost every day so you should check the site frequently.

Customizing the Office 2007 Ribbon UI - Part 6

frice072050 — Wed, 28 Jun 2006 18:00:00 GMT

In this topic on customizing the Ribbon UI, we'll look at different scenarios for customizing the Ribbon at the application-level by using COM add-ins. These modifications could just as easily be applied to a specific document with document-level customizations using Office 2007 Open XML Formats files as described in Part 3 of this series.

Note: I would also highly recommend that you check out the three-part series of articles on customizing the Ribbon at: http://msdn.microsoft.com/en-us/library/aa338202.aspx

First, you'll create the project in Visual Studio using C#. Next, you'll create different customization XML markup files and then embed those into the project. As some of the customizations involve using bitmap icons, you'll need to include three of your favorite 16 x 16 pixel icons. Custom icons for add-ins must be 16 x 16 pixels and be either 16-color or True Color. To get started:

First, create the Visual C# project:
1. Start Visual Studio .NET 2005.
2. On the File menu, click New Project.
3. In the New Project dialog box under Project Types, expand Other Projects, click Extensibility Projects, and then double-click Shared Addin.
4. Type a name for the project. In this sample, type RibbonUISamplesCS.
5. In the first screen of the Shared Add-in Wizard, click Next.
6. In the next screen, select "Create an Add-in using Visual C#", and then click Next.
7. In the next screen, clear all of the selections except Microsoft Word and then click Next.
8. Type a Name and Description for the add-in, and then click Next.
9. In the Choose Add-in Options screen, select "I would like my Add-in to load when the host application loads" and then click Next.
10. Click Finish to complete the wizard.

Now add a reference to Word:
1. In the Solution Explorer, right-click References, and then click Add Reference.

Note: If you don't see the References folder, click the Project menu and then Show All Files.

2. Scroll downward on the .NET tab, press the Ctrl key, and then select Microsoft.Office.Interop.Word.
3. On the COM tab, scroll downward, select Microsoft Office 12.0 Object Library, and then click OK.
4. Next add the following namespace references to the project if they don't already exist. Do this just below the namespace line:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.Office.Interop;
using System.Drawing;
using System.Windows.Forms;

Implement the IRibbonExtensibility interface.
1. In the Solution Explorer, right-click Connect.cs, and click View Code.
2. At the end of the public class Connect statement, add a comma, and then type IRibbonExtensibility.

Tip: You can use Microsoft IntelliSense to insert interface methods for you. For example, at the end of the public class Connect : statement, type IRibbonExtensibility, right-click, point to Implement Interface, and then click Implement Interface Explicitly. This adds a stub for the only IRibbonExtensibility interface member: GetCustomUI. The implemented method looks like this:

string IRibbonExtensibility.GetCustomUI(string RibbonID)
{
}

3. Insert the following statement into the GetCustomUI method, overwriting the existing code:

return resource.GetString("customui-1.xml");

4. Above the GetCustomUI method, add this statement that gives you access to the XML markup files which you'll add later:

private ResourceManager resource = new ResourceManager();

5. Add the callback methods that will be used in the different customization files:

#region callbacks-1

public stdole.IPictureDisp ButtonImage(IRibbonControl control)
{
return resource.GetImage("Icon2.bmp");
}

public void ButtonAction(IRibbonControl control)
{
Random r = new Random();
string s = r.Next(100000, 200000).ToString();

Microsoft.Office.Interop.Word.Application wordApp = (Microsoft.Office.Interop.Word.Application)(applicationObject);
wordApp.Selection.InsertAfter("\nContoso Case ID " + s);
}

#endregion callbacks-1

#region callbacks-2

private Boolean randomize = false;
private IRibbonUI myRibbon;

public void ribbonLoaded(IRibbonUI ribbon)
{
myRibbon = ribbon;
}

public stdole.IPictureDisp getSButton(IRibbonControl control)
{
return resource.GetImage("Icon3.bmp");
}

public int bankTellers_getItemCount(IRibbonControl control)
{
return 6;
}

public stdole.IPictureDisp bankTellers_getItemImage(IRibbonControl control, int index)
{
Random r = new Random();

   if (!randomize)
   {
      return resource.GetImage(index + ".bmp");
   }
   else
   {
      return resource.GetImage(r.Next(0, 7).ToString() + ".bmp");
   }
}

public void bankTellers_OnAction(IRibbonControl control, int index)
{
   myRibbon.Invalidate();
   Microsoft.Office.Interop.Word.Application wordApp = (Microsoft.Office.Interop.Word.Application)(applicationObject);
   wordApp.Selection.InsertAfter("Officer #" + (index+1) + " ");
   // MessageBox.Show("You just clicked on Bank Teller #" + (index + 1) + "!");
}

public void toggle_randomize(IRibbonControl control, bool press)
{
randomize = press;
myRibbon.Invalidate();
}

public string getRandomizeLabel(IRibbonControl control)
{
   if (randomize)
   {
      return ("Randomize ON");
   }
   else
   {
      return ("Randomize OFF");
   }
}

#endregion callbacks-2

#region callbacks-3
public stdole.IPictureDisp dinoImage(IRibbonControl control)
{
return resource.GetImage("Icon1.bmp");
}

public stdole.IPictureDisp OnLoadImage(string image)
{
return resource.GetImage(image);
}

#endregion callbacks-3

Now, we'll start creating different customization files, embedding them as resources in the project, and then observing how they impact the Ribbon UI:

1. In a text editor, add the following XML markup:

<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
    <tabs>
      <tab idMso="TabInsert">
        <group id="MyContosoGroup" label="Contoso!" insertAfterMso="GroupHeaderFooter">
          <button id="CInsertCaseID"
                 size="large"
                  label="Case ID Number"
                  screentip="Insert Contoso Case ID Number."
                  onAction="ButtonAction"
                  getImage="ButtonImage" />
        
      </group>
      </tab>
    </tabs>
</ribbon>
</customUI>

2. Close and save the file as customUI-1.xml.
3. In the Solution Explorer in the project, right-click RibbonUISamplesCS, point to Add, and then click Existing Item.
4. Navigate to the customUI-1.xml file you created, select the file, and then click Add.

For each of the customization files you create, you will repeat these steps. After adding each file and before building and installing the project, you'll need to ensure that the GetCustomUI method points to the correct file. So in this instance, the statement in the GetCustomUI method will read:

return resource.GetString("customui-1.xml");

And finally, use the same procedures to add each of the three 16 x 16 bitmaps as Icon1.bmp. Icon2.bmp, and Icon3.bmp, respectively.

Compile both the add-in and its setup project. Before beginning, make sure that Word is closed.
1. In the Project menu, click Build Solution. You will see a message when the build is complete in the system tray in the lower left corner of the window.
2. In the Solution Explorer, right-click RibbonSamplesCSSetup and click Build.
3. Right-click RibbonSamplesCSSetup and click Install. This launches the RibbonSamplesCSSetup Setup Wizard screen.
4. Click Next on all of the screens and then Close on the final screen.
5. Start Word. You should see the "My Tab" tab to the right of the other tabs.

If you have problems with any of this, recheck that the procedures in the project match those in the steps or go back to part 4 in this series of articles for additional troubleshooting steps.

Customizing the Office 2007 Ribbon UI - Part 5

frice072050 — Wed, 21 Jun 2006 18:00:00 GMT

In the previous blog, I created a COM add-in in managed C# code that customized the Ribbon UI regardless of which document was open. In this topic, I use the same customization XML and create the add-in in Visual Basic 6.0. The existing Ribbon UI in Word 2007 is modified by adding a custom tab, custom group, and button. When you click the button, a company name is inserted into the document. To get started, follow these steps:

1. Start Microsoft Visual Basic 6.0 and select Addin as the project type. This will add a designer class to the project (Connect) and a form (frmAddin).
2. In the Project Explorer window, open the Designer window by right-clicking Connect and then select View Object. Select Microsoft Word from the Application drop-down list.
3. In the Initial Load Behavior drop-down list, select Startup.
4. In the Project Explorer, right-click MyAdd and in the Property window change the name of the add-in to RibbonSampleVB.
5. Remove frmAddin from the project.
6. From the Project window, right-click the Connect item and select view code.
7. Remove all of the code in the designer's code window. This code works for Visual Basic add-ins but not Microsoft Office add-ins.
8. Add the following statement to the OnConnection method to obtain a reference to Word when the add-in loads:

Set oWD = Application

9. This step is optional however if you want to be notified that your add-in is actually loading when you start Word, you can add the following statement in the OnConnection procedure. Once you're satisfied that the add-is being loaded, you can remove the line and rebuild the project:

MsgBox "My Addin started in " & Application.Name

10. Next, at the top of the code window, add the following statements to set aside memory for the application object we added earlier and to create a reference to the Ribbon UI interface:

   Dim oWD As Object

   Implements IRibbonExtensibility

11. Next, you'll implement the IRibbonExtensibilty interface's only member: GetCustomUI

   Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
      IRibbonExtensibility_GetCustomUI = GetRibbonXML()
   End Function

This procedure calls the GetRibbonXML method that, as it's name implies, returns the customization XML to the GetCustomUI method which then gives it to the Ribbon UI to implement when the add-in loads.

12. Add the GetRibbonXML function. In this instance, the customization code is stored in a string variable that is returned to the GetCustomUI method:

Public Function GetRibbonXML() As String
Dim sRibbonXML As String

    sRibbonXML = "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"" >" & _
                "<ribbon>" & _
                "<tabs>" & _
                "<tab id=""CustomTab"" label=""My Tab"">" & _
                "<group id=""SampleGroup"" label=""Sample Group"">" & _
                "<button id=""Button"" label=""Insert Company Name"" size=""large"" onAction=""InsertCompanyName"" />" & _
                "</group >" & _
                "</tab>" & _
                "</tabs>" & _
                "</ribbon>" & _
                "</customUI>"

   GetRibbonXML = sRibbonXML

   End Function

13. Now, add the procedure that gets called when you click the button in the custom tab:

Public Sub InsertCompanyName(ByVal control As IRibbonControl)
   ' Inserts the specified text at the beginning of a range or selection.
   Dim MyText As String
   Dim MyRange As Object
   Set MyRange = oWD.ActiveDocument.Range
   MyText = "Microsoft Corporation"
   ' Selection Example:
   'Selection.InsertBefore (MyText)
   ' Range Example: Inserts text at the beginning
   ' of the active document.
   MyRange.InsertBefore (MyText)
End Sub

14. To make sure the code compiles without error, on the Run menu, click Start with Full Compile.
15. Once the code compiles without error, save the project and create the RibbonSampleVB.dll by clicking the File menu and then clicking Make RibbonSampleVB.dll. The designer will register the add-in for you.
16. Start Word. You should see the "My Tab" tab to the right of the other tabs.
17. Click on the tab and then click the Insert Company Name button. The company name is inserted into the document.

If you have trouble loading the add-in or if the button doesn't work, you may need to do one of the following:

1. Go to the Trust Center to enable macros. Click the Microsoft Office Button (this is the round button in the upper left of the screen).
2. Click Trust Center, click Macro Settings, and then select the "Disable all macros with notification" and the "Trust access to the VBA project object model" options.
3. Close and reopen the document.

If you don’t see the My Tab tab when you start Word, you may need to add an entry to the registry. To do this, perform the following steps:

Caution: The next few steps contain information about modifying the registry. Before you modify the registry, be sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, search for the following article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry.

1. On the desktop click Start, click Run, and type regedit.
2. From the Registry tab, navigate to the following registry key for the add-in:

HKCU\Software\Microsoft\Office\Word\AddIns\RibbonXSampleVB.Connect

Note: If the RibbonXSampleVB.Connect key does not exist, you can create it. To do so, right-click the Addins folder, point to New, and then click Key. Name the key RibbonXSampleVB.Connect. Add a LoadBehavior DWord and set its value to 3.

And that's all there is to creating a COM Add-in to customize the Ribbon UI. In the next blog in this series, we'll look at additional properties of the Ribbon UI and XML markup you use to further customize the Ribbon UI.

Customizing the Office 2007 Ribbon UI - Part 4

frice072050 — Wed, 14 Jun 2006 18:00:00 GMT

In this installment in the series on customizing the Ribbon UI, we'll look at adding application-level customization. In the previous blog, we detailed the steps to create document-level Ribbon by modifying an Open XML Formats macro-enabled file in Word 2007. In this blog, we will create application-level customizations using a managed COM add-in which we'll create in Microsoft Visual Studio 2005 using C#. For consistency, we'll use the same customization that we used in the previous blog; namely adding a custom tab, a custom group, and button that will insert a company name into a Word 2007 document at the beginning of the document. This is the procedure:

1. In a text editor, add the following XML markup:

2. Close and save the file as customUI.xml.

Now create the Visual C# project to modify the Ribbon UI:
1. Start Visual Studio .NET 2005.
2. On the File menu, click New Project.
3. In the New Project dialog box under Project Types, expand Other Projects, click Extensibility Projects, and then double-click Shared Addin.
4. Type a name for the project. In this sample, type RibbonXSampleCS.
5. In the first screen of the Shared Add-in Wizard, click Next.
6. In the next screen, select "Create an Add-in using Visual C#", and then click Next.
7. In the next screen, clear all of the selections except Microsoft Word and then click Next.
8. Type a Name and Description for the add-in, and then click Next.
9. In the Choose Add-in Options screen, select "I would like my Add-in to load when the host application loads" and then click Next.
10. Click Finish to complete the wizard.

Now add a reference to Word:
1. In the Solution Explorer, right-click References, and then click Add Reference.

using System.Reflection;
using Microsoft.Office.Core;
using System.IO;
using System.Xml;
using Extensibility;
using System.Runtime.InteropServices;
using MSword = Microsoft.Office.Interop.Word;

Add the XML customization file as an embedded resource in the project by following these steps:
1. In the Solution Explorer, right-click RibbonXSampleCS, point to Add, and click Existing Item.
2. Navigate to the customUI.xml file you created, select the file, and then click Add.
3. In the Solution Explorer, right-click customUI.xml, and then select Properties.
4. In the properties window select Build Action, and then scroll down to Embedded Resource.

Next, to get access to its members, you need to implement the IRibbonExtensibility interface.
1. In the Solution Explorer, right-click Connect.cs, and click View Code.
2. Just below the Connect method, add the following declaration which creates a reference to the Word application object:

private MSword.Application applicationObject;

3. Add the following line to the OnConnection method. This statement creates an instance of the Application object:

applicationObject =(MSword.Application)application;

4. At the end of the public class Connect statement, add a comma, and then type IRibbonExtensibility.

string IRibbonExtensibility.GetCustomUI(string RibbonID)
{
}

5. Insert the following statement into the GetCustomUI method, overwriting the existing code:
return GetResource("customUI.xml");

6. Insert the following method below the GetCustommUI method:

        private string GetResource(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            foreach (string name in asm.GetManifestResourceNames())
            {
                if (name.EndsWith(resourceName))
                {
                    System.IO.TextReader tr = new System.IO.StreamReader(asm.GetManifestResourceStream(name));
                    //Debug.Assert(tr != null);
                    string resource = tr.ReadToEnd();

                    tr.Close();
                    return resource;
                }
            }
            return null;
        }

Note: The GetCustomUI method calls the GetResource method. The GetResource method sets a reference to this assembly during runtime and then loops through the embedded resource until it finds the one named "customUI.xml." It then creates an instance of the StreamReader object that reads the embedded file containing the XML markup. The procedure passes the XML back to the GetCustomUI method which returns the XML to the Ribbon. Alternately, you can also construct a string that contains the XML markup and read it directly in the GetCustomUI method.

7. Following the GetResource method, add this method. This method inserts the company name into the document at the beginning of the page:

        public void InsertCompanyName(IRibbonControl control)
        {
        // Inserts the specified text at the beginning of a range.
            string MyText;
            MyText = "Microsoft Corporation";

MSword.Document doc = applicationObject.ActiveDocument;

            //Inserts text at the beginning of the active document.
            object startPosition = 0;
            object endPosition = 0;
            MSword.Range r = (MSword.Range)doc.Range(
                   ref startPosition, ref endPosition);
            r.InsertAfter(MyText);
        }

Compile both the add-in and its setup project. Before beginning, make sure that Word is closed.
1. In the Project menu, click Build Solution. You will see a message when the build is complete in the system tray in the lower left corner of the window.
2. In the Solution Explorer, right-click RibbonXSampleCSSetup and click Build.
3. Right-click RibbonXSampleCSSetup and click Install. This launches the RibbonXSampleCSSetup Setup Wizard screen.
4. Click Next on all of the screens and then Close on the final screen.
5. Start Word. You should see the "My Tab" tab to the right of the other tabs.

To test the project:
Click the "My tab" tab and then click the "Insert Company Name" button. The company name is inserted into the document at the cursor location.

If you don’t see the customized Ribbon UI, you may need to add an entry to the registry. To do this, perform the following steps:

1. In the Solution Explorer, right click on the setup project, RibbonXSampleCSSetup, point to View, and then click Registry.
2. From the Registry tab, navigate to the following registry key for the add-in:

HKCU\Software\Microsoft\Office\Word\AddIns\RibbonXSampleCS.Connect

Note: If the RibbonXSampleCS.Connect key does not exist, you can create it. To do so, right-click the Addins folder, point to New, and then click Key. Name the key RibbonXSampleCS.Connect. Add a LoadBehavior DWord and set its value to 3.

And that's all there is to creating a COM Add-in to customize the Ribbon UI. In the next blog in this series, we'll look at customizing the Ribbon UI by using a COM add-in created in Visual Basic.

Customizing the Office 2007 Ribbon UI - Part 3

frice072050 — Wed, 07 Jun 2006 18:00:00 GMT

In this blog, I will walk through an example of adding a custom tab, custom group, and a button with a simple callback procedure to the Ribbon in a Word 2007 document. To get started:

1. Create the customization file in any text editor by adding the following XML markup to the file. Save the file as customUI.xml.
2. Add the following XML markup to the file:

3. Create a folder on your desktop named customUI and copy the XML customization file to the folder.
4. Validate the XML markup with a custom UI schema.
Note: This step is optional. For an example of tools you can use to validate XML markup, see the XSD Schema Validator on this Web site: http://apps.gotdotnet.com/xmltools/xsdvalidator.

5. Create a document in Word 2007 and save it with the name RibbonSample.docm.
6. Open the Visual Basic Editor and add the following procedure to the ThisDocument code module.

Sub InsertCompanyName(ByVal control As IRibbonControl)
   ' Inserts the specified text at the beginning of a range or selection.
   Dim MyText As String
   Dim MyRange As Object
   Set MyRange = ActiveDocument.Range
   MyText = "Microsoft Corporation"
   ' Range Example: Inserts text at the beginning
   ' of the active document
   MyRange.InsertBefore (MyText)
   ' Selection Example:
   'Selection.InsertBefore (MyText)
End Sub

7. Close and save the document.
8. Add a .zip extension to the document file name and double-click to open the file.
9. Add the customization file to the container by dragging the customUI folder from the desktop to the Zip file.
10. Extract the .rels file to your desktop. A _rels folder containing the .rels file is copied to your desktop.
11. Open the .rels file and add the following line between the last <Relationship> tag and the <Relationships> tag. This creates a relationship between the document file and the customization file:

12. Close and save the file.
13. Add the _rels folder back to the container file by dragging it from the desktop, overwriting the existing file.
14. Rename the document file to its original name by removing the .zip extension.
15. Open the document and notice that the Ribbon UI now displays the My Tab tab.
16. Click the tab and notice the Sample Group group with a button control.
17. Click the button and the company name is inserted into the document.

As you can see, adding your own customizations to the Ribbon UI is very easy. In the next blog, we'll look at adding additional controls and working with existing components.

Customizing the Office 2007 Ribbon UI - Part 2

frice072050 — Wed, 31 May 2006 18:00:00 GMT

In the previous blog, I discussed reasons for the new Office Ribbon UI, ways to customize the Ribbon, parts of the Ribbon, and gave an example of an XML customization file. In this blog, I'll talk some about callback procedures that give the Ribbon's controls and components functionality.
A callback is a way of telling one procedure, say procA, where to send messages when procA has completed a task. It's just like giving someone a job to do and also giving them a contact number where they can reach you once the job has been completed. Consider the case of the onAction callback procedure for a button. When you click the button, the callback procedure specified for the onAction event is called and executed. When the task has completed, the procedure notifies and passes control back to the Ribbon.
In the example I gave in the previous blog, the Launcher1 button specified an onAction callback procedure named "AdditionalWidgetOptions." When you click the button, the callback procedure, which resides in your document, is called. The procedure performs whatever task it has been given, for example setting text to bold, and then notifies the Ribbon that the task has been completed and returns control back to the Ribbon.
Now lets briefly look at sample code that supports the XML markup. This particular sample is a portion of an add-in class created as managed C# code in Visual Studio but could just as easily be created in Visual Basic, Visual C++, any of the .NET languages, or VBA. The IDTExtensibility2 implementations have been left out in order to make the example less cluttered:

public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility
{
   public string GetCustomUI()
   {
      StreamReader customUIReader = new System.IO.StreamReader("C:\\Visual Studio Projects\\RibbonXSampleCS\\customUI.xml");
      string customUIData = customUIReader.ReadToEnd();
      return customUIData;

#region Ribbon callbacks

   public void ButtonOnAction(IRibbonControl control)
   {
      MessageBox.Show("Button clicked: " + control.Id);
   }

   public void ToggleButtonOnAction(IRibbonControl control, bool pressed)
   {
      if (pressed)
         MessageBox.Show("ToggleButton pressed.");
      else
         MessageBox.Show("ToggleButton un-pressed.");
   }

   public void EditBoxOnChange(IRibbonControl control, string text)
   {
      ///Do something with 'text' here.
   }

#endregion
}

First, the class must implement the Extensibility.IDTExtensibility2 and IRibbonExtensibility interfaces. Anyone who has created add-ins is already familiar with implementing the IDTExtensibility2 interface. The IRibbonExtensibility interface exposes the GetCustomUI method. The GetCustomUI method is the only method defined in the IRibbonExtensibility interface. In this example, it creates an instance of the System.IO.StreamReader that reads a file containing the XML markup. The method stores the XML customization markup in a variable named customUIData. You could also construct a string containing the XML markup or open an embedded resource (a file contained in the project itself) to supply the XML markup. The sample also includes three callback procedures. The first, ButtonOnAction, receives an object representing the button pressed and displays a message showing the id of the control. The next procedure, ToggleButtonOnAction, receives the toggleButton object and a boolean indicating whether the togglebutton was pressed and then displays a message in a dialog box. The third procedure is a stub procedure that is called when the contents of an editBox control changes. An object representing the clicked control is passed in as well as the text in the editBox. You can see that the process is easily understood and easy to implement based on your needs.

In this topic, I've discussed the basic of implementing callbacks to add functionality to the Ribbon UI. In the next topic, I will dive into customizing the Ribbon in Word by adding a button control with a simple callback procedure.

Customizing the Office 2007 Ribbon UI - Part 1

frice072050 — Thu, 25 May 2006 00:36:00 GMT

This is the first in a series of topics focusing on the new Office 2007 user interface (UI) and Ribbon extensibility, also called RibbonX. By now, you've probably seen blogs, press releases, and other information on the virtues and benefits of the new Ribbon UI. In previous versions of Office, adding custom commands using COM add-in and command bars could be a daunting task for novice Office developers. RibbonX is a breeze. The basic structure of the Ribbon is a hierarchical model, consisting of tabs containing groups of commands and controls displayed in logical, easy-to-find groupings using familiar controls such as drop down dialog boxes, buttons, and context (right-click) menus. The Ribbon also includes rich, interactive new controls called galleries that allows you to see what a change will look like in your document before you actually commit the change. Solutions created in previous versions of Office continue to work and are added to an "Add-Ins" tab. Context menus provide rich, commands and controls relative to the section of the document you are working in.

Note: Some of these features, such as context (right-click) menus, the mini-Toolbar, the Status Bar and live preview using galleries, are not available when customizing the Ribbon UI with add-ins.

Customizing the Ribbon UI is really easy. You can customize the Ribbon through combination of XML markup and any .NET language supported in Microsoft Visual Studio such as Visual Basic .NET, Visual C++, and C#. You can also customize the Ribbon UI using Microsoft Visual Basic for Applications (VBA) and Visual Basic 6.0. There are essentially two ways to customize the Ribbon, both of which at their core use XML markup: directly through a valid Office XML Open File Format file and COM add-ins either containing XML markup or reading XML from a file. Using Office XML Open File Format files allows you to add customizations by using templates (such as .xlam, .dotm files). In this topic, I'll just talk a little about the XML. I'll go into more detail on the XML and code used to create specific UIs.

Before looking at RibbonX, I'll describe a little about some of the parts of the new UI. The Office 2007 UI is more than just the Ribbon; it is a combination of new and traditional features. For example, there is the Office Menu, available from the round Microsoft Office Button located in the upper left-corner of the application window, similar to the File menu found in previous versions of Office. This menu contains the customary New, Open, Save, and Print commands as well as additional commands like Publish, Send, and Finalize. Also in the upper left-corner of the application window is a customizable feature called the Quick Access Toolbar (QAT). The QAT exposes those controls that you typically use frequently such as Save, Undo, Repeat, and Print. The Ribbon includes enhanced versions of the familiar tool tips, called screen tips, that let you display the name and purpose of the control or command. There is a status bar of status indicators (Caps lock, Num Lock), statistics (page number, line number, column) and functions (count, average, sum). Note that the status bar is not customizable. And just as in Office 2003, there are rich, context (right-click) menus. To make it easier for users to find the options they need, they can access legacy dialog boxes in some of the groups by clicking a launcher control in the lower-right corner of some of the groups.

The actual Ribbon customization is done in declarative XML markup. That is, adding a line or encapsulated section to the XML markup in a customization file adds or hides a control, command, or option on the Ribbon. Attributes are used to identify, define characteristics, and add functionality. Writing XML is typically much easier than writing traditional code for many people. And because it's just text, you can write it in any text editor. Additionally, you can take advantage of the many utilities available to help you write and validate XML and associated XSDs such as those provided by Visual Studio Tools for Office.

RibbonX uses callbacks to provide its functionality such as provide status, update properties, or perform some action. Some callbacks look and act like the event procedures you see in other languages. You specify the callback as an attribute of an XML tag in the customization file and then define the callback in code in either the add-in, in a template, or in macro-enabled Office 2007 Open XML Formats file. For example, the XML markup for a button control can specify an onAction attribute that points to a procedure in code which is executed when the button is clicked. That procedure then calls back to the Ribbon to either provide a status or modify the Ribbon.

The following XML markup hides a built-in tab, adds a custom tab containing a custom group containing a custom button, a togglebutton, and a combo box with options.

</group>
</tabs>
</ribbon>
</customUI>

One of the first things you see is a <Ribbon> tag with a startFromScratch="false" attribute. Setting this attribute to True hides the built-in Ribbon controls so that you can build your own UI from scratch. The default for this attribute is False so it is included here for illustration purposes. Next, you see a built-in tab, identified as such by the idMso identifier. There is also an id identifier. The id identifier indicates a custom component. The tab contains a custom group that contains two built-in controls: Paste and Copy.

Next comes a built-in tab containing a custom group. The group contains a number of controls. Notice that the group uses a fully qualified name prepended with the namespace alias. The group contains a button and toggleButton with various attributes defined such as label text and the size of the button. Also included is an event declaration that makes a method call when the user clicks the button. The callback method is implemented in a COM add-in or in the code part of an Open XML Formats file.

A combo box control is added to the tab with three options. The combo box defines different attributes such as its label and a screen tip. And finally, we included a dialog box launcher control for the group that can be used to display a dialog box with additional options.

Looking at this markup, you can see how simple it is to see what's going on here and how easy it would be to add you own controls or adjust the properties of existing components. If you wanted to hide a built-in control, all you would need to do is set its visible attribute to False. In the next blog, I'll discuss callbacks procedures and how they are used, and then get into some examples of creating and modifying different commands and controls on the UI.

Let Access do the work of writing SQL statements for you

frice072050 — Fri, 27 Jan 2006 18:41:00 GMT

One of the views associated with creating queries in the Query designer in Access is the SQL window. The SQL window displays SQL statements that are created behind the scenes when you create a query. However, the SQL window doesn't display just SQL statements. Once you see how it works with the Access Query Design window, you'll find that this window can help you in other ways. For example, you can use the SQL window to create SQL statements that you can then paste into Visual Basic for Applications (VBA) modules. Likewise, you can take SQL statements from VBA procedures and use the SQL window to troubleshoot them for you. Because the SQL windows is designed to work with SQL statements, it provides much more meaningful error messages than the generic messages that come from the VBE.

The query design window is a great visual way to create SQL queries that you can then copy and paste into your VBA procedures. You can use the visual aspect of the query design window to easily create your SQL statement. For example, you add the tables to the design surface of the query design window, drag the fields from the table(s) to the query grid, specify any filtering and sort criteria, execute the query, perform any troubleshooting needed, open the SQL window, and there you have a working SQL statement.

Just as a refresher, SQL statements are made up of the following components:

  |Keyword - Instructs the query to retrieve data|
         |*************Field List**************|
  SELECT Employees.[FirstName], Employees.[LastName]
  FROM Employees |**Source of the data**|
  WHERE Employees.Title = "Sales Representative" |**Filter Criteria**|
  ORDER BY Employees.LastName; (**semicolon is required**|
  |ORDERBY clause specifies the sort order|

#Access uses parentheses to enclose the various parts of the WHERE clause but these are optional.
#Whenever a field is specified you have the option to append the table name, separating the two with a dot.If your query refers to more than one table you must include the table name along with the field name.
#When supplying values in a SQL statement, such as in the query criteria, the data type must be correctly defined by a "qualifier". This is done by enclosing the value between a pair of appropriate characters. For example, single (') or double quotes (") for Strings, pound sign (#) for dates, and no qualifier for numbers.
#Square brackets are only required around field names when they contain spaces but it's good practice to include them.
#Dates in SQL must be written in the US date format (month/day/year).
#It is a good practice to write SQL statement in uppercase so you can distinguish SQL Statement from VBA statements. It's also good practice to write each SQL clause on a separate line.

Once you have a working SQL statement, you can then execute it in VBA. VBA and SQL are totally two different types of languages. VBA is a programming language that you can use to get Access (and other Microsoft Office programs) to do what you want. SQL is a language used exclusively to manipulate the data and structure of a database.
VBA has available a large number of objects, properties, methods, functions and constants to construct the sometimes complex statements used to control the program. SQL uses a limited range of "keywords" combined with information you supply, such as table names, field names, criteria, and sort order.

There are different ways to execute SQL in VBA code. The easiest way is by using the DoCmd object's RunSQL method. Something like this:
Dim strSQL As String
strSQL = "... THE SQL STATEMENT GOES HERE ...”
DoCmd.RunSQL strSQL

The RunSQL method takes two arguments, the SQL Statement itself which must be supplied as a string (i.e. enclosed in quotes) and Use Transaction which is optional and assumes True if you omit it. When transaction processing is applied to a query, Access first performs a "dry run" of the query during which it writes all the changes to a temporary log file but does not make any permanent changes to the database. If the query finishes its task without any problems, the changes noted in the log file are applied and the job (the transaction) is completed. If, however, Access encounters problems while executing the query, the transaction terminates and the log file is discarded without any changes being made. Transaction processing is a very useful safeguard for your data and should always be applied, unless you have a particular reason not to do so.

The SQL window in the Access Query Designer is also the easiest way to debug SQL statements. Most SQL errors are caused by missing keywords. SQL statements in Access are interpreted by the Jet database engine that runs in the background while Access is running. A missing keyword might generate a generic error:
"Syntax error (missing operator) in query expression SELECT...."
Or a more specific error as you narrow down to the faulty part:
"Syntax error in the ORDER BY clause."
If you misspell the name of a field or refer to one that doesn't exist:
"The Microsoft Jet database engine does not recognize LastNme as a valid field name."
Or you might get an 'Enter Parameter Value' dialog box asking you to entry a value for the misspelled name. This type of error almost always refers to a typo in a field name. General steps to troubleshoot this and most types of errors include:
* Open the query in design view and make sure that all references to fields are spelled correctly,
* Check any fields that you recently renamed in the table. This can cause a problem in criteria expressions.
* If you still can't find the problem, open the data source (usually one or more tables or other queries), and check all of the field names, and then recheck your query.

Errors generated by faulty SQL statements in VBA are usually not as helpful or specific as those generated in the Query Designer which is why it's best to create and troubleshoot statements in the Query Designer.

If you want to play around with creating and troubleshooting SQL statements in Access, a good way to do this is to use the Northwind sample database that comes with Access.

XBox - Life, What Life?

frice072050 — Wed, 25 Jan 2006 19:00:00 GMT

Just as Michael Corleone said in Godfather III, "Just when I thought I was out, they pull me back in." I'm not talking about organized crime. I'm talking about XBox. Until about two years ago, I was a major Quake affectionato. I couldn't wait to get home from work to jump back into whatever level I was fighting for my life to get out of. My wife still tells our friends of the physical gyrations I use to make in front of the computer, dodging and ducking to look around corners and avoid being creamed by the demons. And before there was Quake, I was equally hooked on Doom. Back when Doom could fit on a couple of diskettes. The grainy images and cheesy "monsters" were as appealing to me as any spooky movie. For more nights than I count, I'd look at the clock one minute it would be 7pm and look again in what seemed to be 30 minutes later and it would be 1:30am. And that was when I had to leave for work at 5:30am.
Then about two years ago, I started having this gnawing feeling that I was missing something. It turned out to be a life. I found myself day-dreaming while in meetings on new strategies I could use to get out of this or that situation I'd gotten myself into on the latest level. I even felt guilty because I went to one of those Web sites of Quake tricks and tips.It was like the teenage boy caught by his mother with a girly magazine under his mattress. I kept trying to motivate myself to write my own levels and become a published gamester. Then, one night while laying in bed in the middle of the night trying to figure out a way to conserve ammunition, I came to the realization that I needed to get a hold on myself and find out why my wife stayed mad at me and my children thought I was acting like 10 year old friends.
So I went cold turkey. Just stopped. Retired my joystick. Put it out of my mind. Oh it was hard for the first few days but gradually, it became easier. It's a shame there wasn't a patch that you could put on your arm to help. I even regain the use in my hand because it wasn't wrapped around the joystick each night. I had used the same technique when I quit smoking in the late 70's although I had some help from my kids then. Whenever I got the urge to smoke, I'd take my kids out into the backyard, put them into their toy wagon, and run around the yard in circles until the urge went away. I put a lot of miles on that wagon. Not to mention making my kids dizzy.
Now fast forward a couple of years to Christmas 2005. I opened all of my presents, the usual shirts, candy, and "stuff." However, in the corner of the room near the tree was a large box. I had no idea what it could be. I'd had already told my wife to not spend any money on me for Christmas as we had already spent our "Christmas " money on a trip out east to see my parents. So I opened the box and to my surprise (and horror) was, you guessed it, an XBox. Not the XBox 360 mind you. The old one but an XBox nonetheless. Seems that one of my son-in-laws had driven to New Mexico just after the release of XBox 360 because he couldn't find one where he lived in Denver. Somehow he lucked out and found one in Roswell, NM. Anyway, he and my daughter figured that I had too much time on my hands so I inherited his old box. And like the alcoholic that falls off the wagon, I've become a Halo addict. The old cramps in my hand have returned. I have to forcefully stop myself from strategizing throughout the day. Two Saturday's ago, I went into the guest room, where I've setup the machine (my wife had wisely forbid me from setting it up on our main TV) at 8:30am and didn't come up for air, except for bathroom and water breaks, until 1:30am the next morning. Needless to say, I was wound up like I'd had a bucket of caffenated coffee. Now I once again find myself thinking of strategy, strategy, strategy. This time quitting may not be an option. My kids are too old to ride in the wagon and I'm too old to run around the yard. My boss is even trying to get me to go multiple player online. In the end, the only alternative may be a 12-step program. I plan on looking for one as soon as I finish this level.

It's deja-vu all over again - recursion

frice072050 — Wed, 25 Jan 2006 17:47:00 GMT

One of the subjects that made me nervous when taking computer science classes in college was recursive programming. It was easy to accidentally write a recursive (i.e. never-ending} program but it was sometimes hard for me to determine when I should write one on purpose. Like any other tool, it is important to know which situations call for recursive programming and which don't. It seemed that as the assignments became more complicated, the more I had to rely on recursive programming.
A sure-fire way to accidentally end up with an endless loop is to forget to provide some way to terminate a process. A never-ending program eventually runs out of memory to store intermediate results which ends up with an "Out of stack space" error message. Fortunately, this error message in and of itself is a clear sign that you've created an endless loop because it's relatively hard to run out of stack space. Typically, stack space is used to store the arguments of a function when it is called as well as the state of the calling procedure. To run out of stack space, you'd need to have to call a large number of procedures, each within the other. This is kind of what recursion does, except that you can do the same thing with only one procedure if you don't have a way to exit the loop.
So when is recursion appropriate? Knowing isn't always obvious.If you have a procedure that is carrying out task A and needs to stop and carryout identical task B, then recursion might help. A classic example, is scanning files in folders and sub-folders. The procedure first iterates through the first folder and when it encounters another folder, has to repeat itself. Another example is sorting arrays. You compare two items and then swap one with the other And then you repeat this recursively. Another flag that recursion may help is if you find yourself nesting several loops within themselves.
One VB.NET program that I currently maintain iterates through the nodes in an XML schema (XSD) and creates a help topic for elements and complex types. The program uses recursion to look for parent elements for elements inside complex types. So the algorithm goes something like this: read through the nodes until you encounter complexType node A. Then continue reading through the nodes inside the type until you find element X. Now read each parent node of element X until you find an element with type A. This element is a parent of element X. If the element is not of type A, continue to read until you find another element and compare its type with type A. Without recursion. imagine how many Do..Until loops and If Then Else loops you would need to move though the element nodes comparing the element's types and then moving to the next element node and so on. With recursion, the amount of code to do this is relatively small. A simplified version is shown here:
    Private Function GetNamedParent(ByVal itemNode As XmlNode, itemType) As XmlNode
        Dim tempParent As XmlNode
        Dim tempTypeNode As XmlNode

        Try
            tempParent = itemNode.ParentNode
            tempTypeNode = tempParent.Attributes("type").Value

            ' Move up the chain until you get to a node with
            ' a name attribute, such as an element or type.
            If tempTypeNode = itemNode.Attributes("type").Value Then
                Return tempParent
            Else
                Return GetNamedParent(tempParent)
            End If
        Catch excpt As System.Exception
            '    MessageBox.Show("Error in GetNamedParent function: " & excpt.Message)
        End Try
    End Function 'GetNamedParent

The function receives the element in the complex type as an argument. The element's parent node is found and assigned to a temporary node variable. Then the parent node's type is compared to the type of the input element. If they match, the temporary element node must be a parent of the input element and its value is returned to the calling procedure. Otherwise the function is called again until a parent is found. Hopefully this explanation has given you a better understanding of recursion and when to use it in your own programs.

What happened to my button?

frice072050 — Mon, 14 Nov 2005 21:22:00 GMT

I recently had a customer ask how he could freeze objects, in this case a set of buttons, so that they didn't disappear as he scrolled through his Excel spreadsheet. This sounds like it would be a common request and have a topic in Excel help. However, that's not the case. Help does explain how you can freeze rows or columns when scrolling by using the Freeze Panes command on the Windows menu so we can put that feature to use in this case.

One method to anchor an object (button) is to insert a blank row at the top of the spreadsheet and move the button so that it covers the row. Next click Freeze Panes on the Windows menu. Then move your cursor along the left side of the screen and reduce the height of the pane to to the exact height of your object. You can also lock objects along the left side of the screen. Click a column letter, and then click Freeze Panes on the Window menu. Now as you scroll to the right, any objects along left side of the .spreadsheet remain fixed. To freeze objects in both a row and a column, click the cell just below and to the right of the row and column you want to freeze and then click Freeze Panes on the Window menu.

An alternative technique is to create a floating toolbar. For example, the following VBA code first checks to see if the named custom toolbar exists, and if not, creates a floating toolbar which stays visible when you scroll through the spreadsheet. Do this in the .Workbook_Open event and delete them in the .Workbook_BeforeClose event. This way you are sure you have full control over the menus and that they are pointing to the right code when they execute.:

Dim cbar As Office.CommandBar
Dim bExists As Boolean
bExists = False
For Each cbar In Application.CommandBars
If cbar.Name = "Custom Toolbar" Then bExists = True
Next
If Not bExists Then
Set cbar = Application.CommandBars.Add("Custom Toolbar", _
msoBarFloating, False, True)
cbar.Visible = True
End If

And yet another way is to use the Attach command in the the Tools=>Customize dialog in the Toolbars tab. This way, when your file is opened, if the toolbar does not exist, it is created from the toolbar attached to the file. It is still a good idea to remove the toolbar when you close the workbook by using the .Workbook_BeforeClose event.

You can add similar freeze pane functionality when working with large tables in Access. You can freeze columns in Datasheet view and continue to horizontally scroll through records. To freeze a column, click on the field's name in Datasheet view to select the column. Then, choose Format/Freeze Columns from the menu bar. Access moves the column all the way to the left of the view and freezes it. If you freeze additional columns, they are moved to the right of the last frozen column. To .return the view to normal, select Format/Unfreeze All Columns from the menu bar. Note that there is one catch to using this method. Although the columns are no longer frozen, Access doesn't return them to their original positions within the Datasheet view.

And speaking of working with alternate screen displays, in VBA, there are often times when it's helpful to view non-adjacent code in a module. For example, a calling routine and the called routine. However, continually scrolling back and forth through your code can be a real nuisance. Fortunately, you can easily split the module window into two scrollable panes. Simply drag the split box (the small rectangle that's directly above the vertical scroll bar on the right-hand side of the code window) to where you want the split made. To remove the split, just double-click on the split line.

Get "Immediate" results when creating or debugging code

frice072050 — Mon, 31 Oct 2005 20:19:00 GMT

In my last blog, I talked about debugging with the Watch window where you can do things such as look at the value of expressions at some point in the code execution and halt execution when a variable reaches a specific value. Other useful tools to help with debugging are the Immediate window and the Locals window.

The Immediate window is the most basic debugging area. Unlike the Watch (or Locals) window, the Immediate Window can be used regardless of whether your code is running or not. The Immediate is essentially a mini-VB interpreter that allows you to:

Evaluate expressions that don't appear in your code (e.g. math equations).
Run a procedure.
View the value of current variables or expressions.
Change the value of variables while the code is in break mode and see the results of the change.
Quickly see the methods and properties of an object.

For example, for items that return a value, use a question mark followed by the expression.
? 15 + 22 / 4
then press Enter to see the value.
If your code is currently running and paused at a breakpoint, you can evaluate the current value of a variable:
? strGreeting

You can also use it to launch a subroutine or function with the parameters, if applicable:
? MsgBox("Do you want to see the error message?", vbCritical+vbYesNo)
the Immediate Window runs the function, which displays the text string in a message box.

If you want to run a subroutine that doesn’t return a value, do not include the ? and just enter the subroutine name.

To test VBA statements in the Immediate window:
1. In the Visual Basic Editor, on the View menu, click Immediate Window.
2. In the Immediate window, type ?15 + 22 / 4
3. Press ENTER. 20.5 is displayed.
4. Add the following procedure to the code window. This procedure loops through a string and prints each character to the Immediate window using the Debug.Print statement:
Sub PrintChars()

Dim strTest As String
Dim strChar As String
Dim int1 as Integer

strTest = "Look at each character"

For int1 = 1 to Len(strTest)
strChar = Mid(strTest, int1, 1)
Debug.Print(strChar)
Next int1

End Sub

6. Place a breakpoint at the Debug statement by clicking the gray column to the left side of the statement.
7. In the Immediate window, type PrintChars. The code runs and then pauses at the Debug statement.
8. Type ?strChar in the Immediate window and press ENTER. The current setting of the variable (L) is displayed in the window.

You can change the value of a variable while the code is in break mode. Just type the line of code in the Immediate window and press Enter. Then step through the code to see the effect of the change. For example, using the subroutine described above:

1. Set a break point on the Debug.Print statement and press F5 to run the procedure. If the Macros dialog box pops up, select the macro name and click Run.
2. With the code paused, press F8 a few times to print the first couple of letter in the text string (L o o, for example).
3. Now below the PrintChars line in the Immediate window, type: strTest = "New message." and press Enter.
4. Press F8 several times and notice that now the characters being displayed have changed to the new string, starting from the current position in the string (m e s s a g e).

You can also take advantage of Intellisense in the Immediate window. For example, type Application. in the window and a drop-down box is displayed with the associated members of the object.

You may not be aware but instead of setting a breakpoint in your code, you can just highlight the code statement where you want to pause execution, click Run To Cursor on the Debug menu and program execution will immediately continue to the point where you are at. Then you can step through the procedure pressing F8. I like to use this technique because if keeps me from cluttering up my code with breakpoints. When debugging, I will invariably forget that I placed multiple breakpoints in the code and when I run the procedure, execution will stop when I don't want it to. Run To Cursor will allow me to pause execution without cluttering up my code. Another way around this problem is just before setting a breakpoint at the current location, click the Debug menu and then click Clear All Breakpoints.

Also, the Debug.Print statement lets you write output to the Immediate window. Insert this command into sections of your code where you’d like to know the value of certain variables, but don't want to stop the program to get it. For instance, if you’re moving through a recordset and would like to know the values of a few fields as the processing occurs, you may have code like the following before the other processing of the record occurs:

Debug.Print intCount & ": " & rst![ID] & ", " & rst![Name]
intCount = intCount + 1
It’s not as complete as stepping through each line, but will give you an indication of whether the code is running correctly
before narrowing your search further.

Although the Immediate window is a powerful tool when creating and debugging code, it does have limitations. For example, you can only execute one statement at a time in the window. However, there is a way around this limitation. Some control structures such as For...Next loops, which require multiple statements, can be put on a single line and executed. For example, on a blank line in the Immediate window, type for i = 1 to 5 : ? i : Next i and press Enter. The statement runs and displays the values of "i."

The Locals window is good for looking at the value of active variables when the code is in break mode. You can also see the value of properties and even some controls. The Locals windows displays the entire list of local variables and their current values. For example, again looking at the subroutine in the last example:

If you place a break statement on the Debug statement and run the code, the code pauses and you can look in the Locals window and see the value of the variables strTest, strChar, and int1. You can then press F8 to step through the loop and see the values of the variables change.

A variable that represents an object appears in the Locals window with a plus sign (+) to the left of its name. You can click the plus sign to expand the variable, displaying the properties of the object and their current values. If a property of the object contains another object, that can be expanded as well. The same holds true for variables that contain arrays or userdefined types.

Like the Immediate window, sometimes you can change local variable values by selecting the variable you want to change and clicking the Value box. If VB lets you to change the variable, the Value field will let you edit it. Some things can't be changed though. For example, you can't change objects, such as the properties of a control, or certain Variant variables. In cases where you can't change the values of a variable in the Locals window, you can use the Immediate window to test the change.

Before finishing this blog, another tip that I uncovered looking through VB help involves moving between Visual Basic procedures. Simply highlight the property or procedure name in the code and press SHIFT+F2; you'll be transported to the highlighted property or procedure. If the subroutine or property is located outside the project, you'll be taken to the Object Browser, where you can find additional information. You can also move back to the original location by pressing CTRL+SHIFT+F2.

VBA Beginner's Corner - Someone to Watch over you.

frice072050 — Thu, 27 Oct 2005 19:57:00 GMT

The Visual Basic for Applications (VBA) integrated development environment (IDE) seen in the Microsoft Office applications contains lots of tools to help you diagnose and fix errors in your code. However, many of us don't take advantage of the tools that are there. Personally, the technique I relied on most heavily when I was first learning VBA was the message box. If I wanted to see the value of a variable during execution of my code, I used the Msgbox function to display the value of an object, expression, or variable. It was simple and quick. However, this method has some serious disadvantages. It was static, meaning that it only gave me the value of the variable during the first few iterations of my code. The only way I could see the changes to a variable was to dismiss the message box manually during each iteration of my code. This is very tedious if, for example, you want to see the value of an expression on the 25th time through my code. It was also easy to forget about a message box I had added earlier and be interrupted when it popped up. I'd have to stop the code and then delete the msgbox box. It was also very limited in showing me the interaction between different parts of my code.

Another method I use is to halt execute and then step through it one statement at a time. This is more effective than the message box but was still somewhat tedious. Especially if you have to step through dozens of statements one step at a time.

Fortunately, the IDE has many tools that are much more dynamic than the message box. One of the most powerful are Watch expressions. A Watch expression is a VBA (or Visual Basic for that matter) variable, function value, property value, or other valid expression that shows you its value when the code pauses, for example, at a break point or when the value of another variable hits a certain value. Watch expressions help you track the values of items as your code executes. (A break point temporarily pauses your code so you can debug it.) Not only are Watch expressions great tools to use to inspect your objects and collections at runtime, but they are also a good teaching tool to help you when trying to understand objects and how they are constructed. What’s most powerful about adding watches is that in addition to variables, you can also add expressions, and options to break when the value changes. The latter is particularly powerful when you are having trouble determining why a particular situation arises in your application. Maybe a variable is set in multiple places and you can’t tell which instance is causing the value to change. By setting the Watch Type option, you can quickly stop when this occurs.

Watch expressions are displayed in the Watch window. To see an expression in the Watch window, just highlight the expression in the code window and drag it to the Watch window. You can display the Watch window by clicking the View menu and then clicking Watch Window.

There are three types of Watch expressions in VBA: the Watch expression, Break When Expression is True, and Break When Expression Has Changed. The Break When Expression is True and Break When Expression Has Changed options allow you to specify a logical condition or expression. When that condition is met or the expression hits a certain value, VBA halts execution and places you in Break mode.

You can add, edit, or delete a Watch expression at design time or during a break in the code. In the VBA IDE, to add Watch expressions, just click the Debug menu and then click Add Watch. This displays the Add Watch dialog box. Once you've defined an expression and it appears in the Watch window, you can edit it by clicking the Debug menu and then clicking Edit Watch. The Add Watch and Edit Watch windows have many of the same components. The main difference is that the Edit Watch dialog box has a Delete button. Looking at the parts of the Watch window: the Expression box contains the expression to be evaluated; the Value box is self explanatory; the Type box sets how VBA responds to the watch expression such as when you enter break mode or when the expression hits a true (non-zero) value or when the value changes the Context option sets the scope of the variables being evaluated, for example, if you are watching variables with the same name or you want to restrict scope to a particular subroutine.

To try an example in Office, do the following:
1. Start your favorite Office application such as Access, Excel, or Word and open new database, workbook, document, as appropriate.
2. On the Tools menu, click Macro, and then click Visual Basic Editor.
3. In the VBE, click Insert, and then Module. Double-click Module 1 in the Project pane to open the Code window.
4. Add the following code:

Public Function TestWatch()
Dim x, y, z As Integer

For x = 1 To 10
   For y = 1 To 20
      z = x + y
   Next y
Next x
End Function

5. Highlight just the x + y portion of the expression and on the Debug menu, click Add Watch.
6. The Add Watch dialog box is displayed with the expression in the Expression box.
7. Under Context, select the context in which the expression will be evaluated. In this case, leave the default which is the TestWatch function.
8. Click OK. The Watch window is displayed with the expression. Notice the glasses icon next to the expression.
9. Set a breakpoint in your code by highlighting the second Next statement in your code and then clicking Toggle Breakpoint on the Debug menu. You can also click the gray margin to the left of the statement to create a breakpoint.
9. Open the Immediate window by clicking the View menu, and then clicking Immediate Window.
10. In the Immediate window, type TestWatch and press Enter. Notice that the Value box beside the expression now equals 22. However, also notice that z (the result of the addition) equals 21. This difference results because by putting the break statement on the second Next statement, x has already incremented but z won't change until the next iteration through the loop. (Thanks to Tim Rohr for pointing out the error in my original post)
11. To get out of break mode, on the Run menu, click Reset. Click the Debug menu and then click Clear All Breakpoints.
12. Now, highlight the expression in the Watch window, right-click, and then click Edit Watch.
13. In the Edit Watch dialog box, change the expression to y = 9, select the Break When Value Is True option, and then click OK. Now the icon beside the expression has changed.
14. Click in the Immediate window and press Enter.
15. Code execution stops when the value of y reaches 9. Now hover the mouse over the z in the expression in the subroutine and notice that it is equal to 9. Then do the same for x and y. Notice they are equal to 1 and 9, respectively. The reason that z is equal to 9 is that although the code stopped when y equaled 9, the code hadn't executed the z = x + y expression yet.
16. To execute the expression, on the Debug menu, click Step Into. Notice that the yellow highlight moved one statement down to the Next statement.
17. Now hover the mouse over the z variable. The value is now 10.
18. Again, click Reset on the Run menu, and then highlight the expression in the Watch window, right-click, and then click Edit Watch.
19. In the Edit dialog box, change the expression to z, and then click Break When Value Changes. The icon changes again.
20. Run the code. Notice that in the Watch window, the Value box now equals 2 and the code is highlighted at the first Next statement. As we specified, the code changed when the value of z changed.
21. Now reset the code and try example expressions of your own.

As a note, if you don't need to continually monitor a value, you can use the Quick Watch dialog box, available from the Debug menu, to quickly check the value of a variable or expression. Also rather than examining variable values individually, you can see all the local variables by selecting Locals Window from the Views menu. This displays the entire list of local variables and their current values. Local variables are variables defined in the current procedure and module declaration section.

For you more advanced VBA'ers, if you have some tips and tricks when using the Watch window, send them to me and I'll include them in this post.