Questions similar to these appear in the Bentley Community Discussion Groups.

This page lists some solutions to common MicroStation VBA (MVBA) and Visual Basic (VB) problems. Tips are published as examples and are not necessarily working code.

Visual Basic for Applications does not provide one feature that most people would consider should be an inclusive part of any computer language: the ability to open and save files in a consistent manner. Curiously, Microsoft provides the common dialog DLL in all versions of Windows, but makes it hard to use from VBA. The section on file browsing provides a solution to this problem.

Absent from both VB and VBA is a way to browse for a folder. It's common, for example, to want to process all files of a given type in a particular folder. There is no graphic item that provides a folder browser. The section on folder browsing provides a solution to this problem.

Answers to those questions are provided here, in the sections captioned Browse for a Folder and Browse for a File. Before we cover those topics, we introduce a more general Microsoft tool for managing files & folders in your VB or VBA code: Windows Scripting.

Windows Scripting

Both VB and VBA have archaic tools for examining file attributes and getting a list of files from a folder. For example, if you want to get a list of all the writable DGN files in folder X and its sub-folders, where would you start? Wherever you start, you'll end up in a mess. Why? Because the VB Dir function cannot be used recursively, and to examine sub-folders you need a recursive function.

With Windows 2000, Microsoft introduced the Windows Scripting library. As with many Microsoft technologies, its name is cast in jelly, and tends to change from time to time. This library, as its name hints, was initially aimed at improving the lot of those poor souls attempting to write VB Script code for web purposes. However, it's useful for any of us wanting to write better file & folder code in VB and VBA. Microsoft Scripting Runtime is not a substitute for the file and folder dialogs discussed below; it does not contain any user interface components.

Use Windows Scripting in your VBA applications. Make a reference to the DLL that hosts this library using the VBA menu Tools|References. This pops the References dialog, where you can scroll to find the scrrun.dll file. Select Microsoft Scripting Runtime and click the OK button.

VBA References Dialog

Once you've made a reference to Microsoft Scripting Runtime you can view its properties and methods using VBA's built-in Object Viewer. Press function key F2 to pop VBA's object viewer dialog. The core of Windows Scripting are the FileSystemObject and the Dictionary classes. Read Microsoft's Script Runtime Overview for more information.

The FileSystemObject provides a number of useful methods that enable the easy collection of folders and files. The Dictionary class presumably underlies these methods, and is separately available for your own collection implementations. The Dictionary class appears to offer better performance than VB's own Collection class. There are also methods to construct file and folder names from their text components.

Once you've created a reference to Microsoft Scripting Runtime in your project, you can write code like this …

Dim oFileSystem As Scripting.FileSystemObject
Set oFileSystem = New Scripting.FileSystemObject

Dim oFolder As Scripting.Folder
Set oFolder = oFileSystem.GetFolder("C:\Windows")
If oFolder.Attributes AND 2 Then
 Debug.Print "Hidden folder."
End If
If oFolder.Attributes AND 4 Then
 Debug.Print "System folder."
End If
If oFolder.Attributes AND 16 Then
 Debug.Print "Folder."
End If
If oFolder.Attributes AND 32 Then
 Debug.Print "Archive bit set."
End If
If oFolder.Attributes AND 2048 Then
 Debug.Print "Compressed folder."
End If

Browse for a File

Q I want to browse for a file using VBA. But, I don't see any API reference to file operations such as open, save, or save as. How do I open a file using MicroStation VBA?

A  The Windows Common Dialog provides a way to let the user open or save a file. However, for reasons known only to Microsoft, the Common Dialog design-time component is provided with Visual Basic but not with Visual Basic for Applications. This doesn't make a lot of sense, because of course the DLL that provides that functionality is part & parcel of Windows (otherwise, we would never be able to open a file, right?).

There's nothing original about the code that accompanies this article. It's been published before on several web sites, such as The Code Project.

Because the Common Dialog DLL is on your system right now (I'm assuming that you are sitting in front of a Windows operating system), you can invoke it directly from code. You need to declare a number of constants from the Win32 API, a user defined type OPENFILENAME to pass data to the dialog, and declare the function name from the DLL.

Copy these declarations into your own VBA code module. Put them at the beginning of the module, before any procedure definitions.

' ---------------------------------------------------------------------
'   Win32 API declarations so that VBA can call
'   Windows functions directly
' ---------------------------------------------------------------------
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
' ---------------------------------------------------------------------
'   Win32 API structure definition as user-defined type
' ---------------------------------------------------------------------
    lStructSize         As Long
    hWndOwner           As Long
    hInstance           As Long
    lpstrFilter         As String
    lpstrCustomFilter   As String
    nMaxCustFilter      As Long
    nFilterIndex        As Long
    lpstrFile           As String
    nMaxFile            As Long
    lpstrFileTitle      As String
    nMaxFileTitle       As Long
    lpstrInitialDir     As String
    lpstrTitle          As String
    flags               As Long
    nFileOffset         As Integer
    nFileExtension      As Integer
    lpstrDefExt         As String
    lCustData           As Long
    lpfnHook            As Long
    lpTemplateName      As String
End Type
' ---------------------------------------------------------------------
'   Win32 API constants
' ---------------------------------------------------------------------
Private Const BIF_NEWDIALOGSTYLE         As Long = &H40
Private Const BIF_RETURNONLYFSDIRS       As Long = 1
Private Const MAX_PATH                   As Long = 260
Private Const OFN_OVERWRITEPROMPT        As Long = &H2
Private Const OFN_FILEMUSTEXIST          As Long = &H1000
Private Const OFN_PATHMUSTEXIST          As Long = &H800
' ---------------------------------------------------------------------

Here's a file open procedure ShowOpen. It's a function procedure, that returns the path of the file chosen by the user, or an empty string if the user cancelled the file open dialog. The declarations shown above must exist in the file where you put this funcion. We declare a variable OFName of type OPENFILENAME. Next, we fill in the members of that structure prior to calling the Win32 API function GetOpenFileName. If the user selects a file and press the OK button, then the OFName.lpstrFile member contains the chosed file.

' ---------------------------------------------------------------------
'   ShowOpen    Open common dialog
'   Returns:    full path of file to open, or zero-length string if Cancel
'   Example:
'   StrFilter = "Text Files (*.txt)" + Chr$(0) + "*.txt" + Chr$(0)
'   StrFilter = StrFilter & "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
' ---------------------------------------------------------------------
Public Function ShowOpen( _
    strTitle As String, _
    Optional strFilterDescr As String = "All files (*.*)", _
    Optional strFilterSpec As String = "*.*", _
    Optional strInitDir As String = vbNullString) As String
    On Error GoTo Proc_Error

    Dim OFName          As OPENFILENAME
    Dim strFileFilter   As String, _
        strFileSelected As String

    'strFileFilter = "Microstation Files (*.dgn)" & Chr$(0) & "*.dgn" & Chr$(0)
    'strFileFilter = strFileFilter & "Autocad Files (*.dwg)" + Chr$(0) + "*.dwg" + Chr$(0)
    'strFileFilter = strFileFilter & "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
    strFileFilter = strFilterDescr & Chr$(0) & strFilterSpec & Chr$(0)
    With OFName
        .lStructSize = Len(OFName)
        .hWndOwner = 0&
        .hInstance = 0& 'App.hInstance
        'Select a filter
        .lpstrFilter = strFileFilter ' "Text Files (*.txt)" & Chr$(0) &"*.txt" & Chr$(0) & "All Files (*.*)" & Chr$(0) & "*.*" & Chr$(0)
        .lpstrFile = Space$(254)
        .nMaxFile = 255
        .lpstrFileTitle = Space$(254)
        .nMaxFileTitle = 255
        If (vbNullString <> strInitDir) Then _
            .lpstrInitialDir = strInitDir
        .lpstrTitle = strTitle ' "Select File"
    End With
    If GetOpenFileName(OFName) Then
        strFileSelected = Trim$(OFName.lpstrFile)
        If (InStr(strFileSelected, Chr(0)) > 0) Then
            strFileSelected = Left(strFileSelected, InStr(strFileSelected, Chr(0)) - 1)
        End If
        ShowOpen = Trim$(strFileSelected)
        ShowOpen = vbNullString
    End If

    Exit Function
    ShowOpen = vbNullString
    MsgBox Err.Description
    Resume Proc_Exit
End Function

Browse for a Folder

Q I want to do something with each file in a particular directory. I want to choose a folder, then build a list of files in that folder. But, how do I browse for a Windows folder?

A  The Windows Common Dialog provides a way to let the user open a file, but it won't let you open a folder. However, there's no similar VB or VBA tool to let the user find a folder.

Fortunately, the Windows application programming interface (Win32 API) provides an alternative, although it's not a solution you would arrive at by intuition. To make life easier, we've wrapped the non-intuitive code in an ActiveX DLL.

Folder Browser

The Folder Browser is free. Instructions on its use are provided, with an example.