Questions similar to this appear on the Bentley Discussion Groups. This problem appeared in the Administration discussion group.

Q Our project directory structure includes folders for office documents, such as spreadsheets and text files, in addition to the usual MicroStation^® files.

• How can I open an office document using VBA?
• How can I open a Microsoft Word document using VBA?
• How can I open an Microsoft Excel spreadsheet using VBA?
• How can I open any file using VBA?

A  It makes sense to keep related project files in a consistent directory structure. The problem of keeping office documents in a project folder is that the owning application doesn't recognise your folder structure.

Windows and File Associations

Microsoft Office^® is almost ubiquitous. Unlike MicroStation's flexible configuration capabilities, Office applications recognise only one folder per Windows user account. That is to say, you can't configure Microsoft Word™ to look automatically in your project-specific directories. Each time a user wants to look at a spreadsheet, she has to navigate to your project folder and double-click the .xls file, or navigate using the spreadsheet's File Open dialog.

What if you could browse your office files while using MicroStation, and open a file without wasting time browsing? This article proposes a solution that does exactly that.

What if you could browse for any file using MicroStation, and open that file without wasting time browsing? This article proposes a solution that does exactly that.

Windows File Assocations

The solution provided in this article relies on Windows File Associations. When you install an application in Windows, that application registers its interest in a particular file extension. You're probably familiar with this, but as a reminder, here are some common assocations …

Common File Assocations

Application	Extension
Bentley MicroStation	.dgn
Microsoft Word	.doc
Microsoft Excel	.xls
Adobe Portable Document Format	.pdf

The consequence of those associations is simple and intuitive. If, while navigating your Windows folders with Windows Explorer, you double-click a .doc file, Windows starts Word and Word opens the file you indicated.

Because Windows connects any file extension to its application, the solution proposed here is not restricted to any particular file type. It works with any registered file extension. Suppose you have an AVI file, and that movie is associated with Windows Media Player. When you choose a .avi file, then we fire up the Media Player and tell it to play that movie.

Win32 API

As a programmer, you have access to Windows file associations through the Win32 API. That API provides hundreds of functions related to file and folder management. Many of those fall into the class of shell extensions. There's a shell extension whose sole duty is to open a file with its associated application. The link at the end of this article provides a freely downloadable VB project that wraps the Win32 ShellExecute function. Here's its function prototype …

HINSTANCE ShellExecute (HWND hwnd,
    LPCTSTR lpOperation,
    LPCTSTR lpFile,
    LPCTSTR lpParameters,
    LPCTSTR lpDirectory,
    INT nShowCmd
);

The number of arguments and their options provides a bewildering number of ways that you might invoke that function. We've simplified things for you, and wrapped it inside an ActiveX DLL ShellExecute.dll. The VBA example shows how to use ShellExecute.dll.

VBA, Files and Folders

Project Directory Structure

We assume that you have a standard directory structure for project files. You may have a specific folder per project where office documents are located.

Configuration Variables

It will be useful to define MicroStation configuration variables (CfgVars) that point to your project root and to specific folders in your project. For example …

PROJECT_ROOT  = V:/Projects/FlexiTable/
OFFICE_FOLDER = $(PROJECT_ROOT)Office Files/

As you probably know, MicroStation VBA has methods to get the value of a CfgVar from the ActiveWorkspace. We can find the office folder programmatically like this …

Dim path  As String
path = ActiveWorkspace.ConfigurationVariableValue("OFFICE_FOLDER")

Open Office Document

Now you need to be able to open a file in that Office Files folder. MicroStation doesn't know how, but Windows does. Windows knows that a given file extension is associated with an application. For example, a .dgn file is associated with MicroStation, a .doc file is associated with Microsoft Word, an .xls file is associated with Microsoft Excel.

The Windows API is a little tricky to use from VB/VBA, so we've wrapped the functionality in an ActiveX component in ShellExecute.dll. You can use a ActiveX component by creating a reference to it. Create a reference using the VB Project|References menu, or in VBA from the Tools|References menu.

The ShellExecute component has very few properties. You need to set its DefaultDirectory property, which is a folder path. You need to set its File property, which is the name of a file, including the extension, in the specified folder. When you invoke its Show method, ShellExecute asks Windows to find the application associated with the file, then open the file with the application …

Dim oViewer  As New ShellExecute.clsShellExecute
Dim path     As String
path = ActiveWorkspace.ConfigurationVariableValue("OFFICE_FOLDER")
oViewer.DefaultDirectory = path
oViewer.File = "spreadsheet.xls"
If (oViewer.Show()) Then
    '  Success: document is opened by its associated application
Else
    Dim msg  As String
	msg = "File 'spreadsheet.xls' not found"
	ShowMessage msg, msg, msdMessageCenterPriorityWarning, True
End If
Set oViewer = Nothing

Example Application to View Office Document

Putting it all together, here's an example VBA application to show an office document. The document is in a folder specified by the MicroStation CfgVar FLEXITABLE_DATA. The document name is Excel Workbook.xls, and when your user presses the View button, Excel starts and opens the file.

Download the Example

The Open Office Document Example is provided in a ZIP archive. There's a VBA project, a VB project, and a DLL. Copy the VBA project to a well-known folder (i.e. one specified in your MS_VBASEARCHDIRECTORIES CfgVar). Copy the DLL anywhere, then register it. You need to register an ActiveX DLL with Windows: often, an installation program takes care of this for you, but in this case you're unpacking a ZIP archive and must register the DLL manually.

To register ShellExecute.dll, open a Windows command prompt and take the following steps …

• Navigate to the folder that contains the DLL
• Invoke the Windows registrar with the keyin regsvr32 ShellExecute.dll
• Windows pops a dialog to confirm registration

Once you've copied the files and registered the DLL, you can open or run the example VBA project. Use this keyin …

vba run [ShellExecute]modMain.Main

You'll see the example dialog appear. Type a valid CfgVar into the upper text box, and a file name including its extention into the lower text box. Press the View button, and watch an office application magically start with your document.