Questions similar to this appear on the Be Communities Discussion Groups. These problems appeared in the MicroStation Programming Forum.

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 PDF 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 C language 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 VBA wrapper. The VBA example code shows how to use ShellExecute.

The example project ShellExecute2 provides two ways to open a document …

1. Using a keyin command
   e.g. vba run [FileViewer2]modMain.OpenDoc "C:\My Docs\Example.docx"
2. Through a UserForm that searches project folders

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 a VBA wrapper function OpenDocWithApplication. The example VBA project lets you open any document using a keyin …

vba run [FileViewer2]modMain.OpenDoc C:\MyDocs\Example.docx

Beware spaces! If a document path contains one or more spaces then you must enclose the path in double-quotes …

vba run [FileViewer2]modMain.OpenDoc "C:\My Documents\Example.docx"

Here's the procedure that interprets your keyin and invokes ShellExecute …

Public Sub OpenDoc()
    Dim docName As String
    ' Get the user keyin argument string and remove quotes if necessary
    docName = RemoveQuotes(KeyinArguments)
    If OpenDocWithApplication(docName) Then
        Debug.Print "Opened '" & docName & "' successfully"
    Else
        Debug.Print "Failed to open '" & docName & "'"
    End If
End Sub

Here's the procedure that wraps ShellExecute …

Public Function OpenDocWithApplication(ByVal docName As String) As Boolean
    OpenDocWithApplication = False
    Const VERB_OPEN                         As String = "open"
    Const SW_SHOWNORMAL                     As Long = 1
    Dim r                                   As Long
    r = ShellExecuteWin32(GetDesktopWindow(), VERB_OPEN, docName, 0, 0, SW_SHOWNORMAL)
    OpenDocWithApplication = ExplainShellExecuteReturnCode(r)
End Function

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) such as ..\Standards\macros.

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

vba run [FileViewer2]modMain.Main

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

Questions

Post questions about MicroStation programming to the MicroStation Programming Forum.