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

Q Two of the most common requirements of computer software are the ability to choose a file to open, and to choose a file name when saving a file. That requirement is also true of MicroStation® VBA.

A  What you are looking for is the Windows Common Dialog Control. The Common Dialog Controls provide the familiar File Open, File Save As™, and other dialogs. Inexplicably, Microsoft chose to omit that functionality from Visual Basic for Applications™ (VBA). They are, however, included in Visual Basic™ (VB) in contrast to VBA.

Windows Common Dialogs

What makes the Common Dialog Controls' omission from VBA infuriating is that they are, of course, included with every copy of Windows. They are installed, along with many other system dynamically linked libraries (DLLs), in your Windows folder. Fortunately, there is a way to use them from VBA, but it's not intuitive. VB and VBA let you call functions implemented in Windows DLLs. Here's how…

Advanced Users Only

It's a good idea to build your own library of really useful procedures. A library in MicroStation VBA is simply a project. You can reference once project from another. For example, you might create a VBA project WindowsUtilities that contains your wrappers around the Windows Common Dialog Controls. When your new VBA application requires a File Open dialog, reference your library and call your wrapper procedure.

Browse for a Folder

A related problem is browsing for a folder. The Common Dialog Controls do not include a folder browser, so you're stuck whether using VB or VBA. To answer this question, LA Solutions has developed the Folder Browser ActiveX control.

Declare a Windows Function in VBA

Calling a DLL is simple, but complicated by the amount of ancilliary information that goes along with it. That's why it's a good idea to wrap Windows functions inside a VBA procedure. Once done, it's done, and you can forget about the details.

First, declare the Windows function you want to use… 

' ---------------------------------------------------------------------
'   Win32 API declarations so that VBA can call							
'   Windows functions directly											
' ---------------------------------------------------------------------
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long

The declaration tells VBA that function GetSaveFileName is imlemented in DLL comdlg32.dll. It takes a single argument by reference, which is a user defined type (UDT) OPENFILENAME.

Here's the definition of OPENFILENAME… 

' ---------------------------------------------------------------------
'   Win32 API structure definition as user-defined type					
' ---------------------------------------------------------------------
Private Type OPENFILENAME
    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

OPENFILENAME parameters understand one or two flags… 

' ---------------------------------------------------------------------
'   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
'--------------------------------------------------------------

Windows Function Declaration

Putting it all together, this is what you should copy into a VBA module to declare the call to a Save As… dialog… 

' ---------------------------------------------------------------------
'   Win32 API declarations so that VBA can call							
'   Windows functions directly											
' ---------------------------------------------------------------------
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
' ---------------------------------------------------------------------
'   Win32 API structure definition as user-defined type					
' ---------------------------------------------------------------------
Private Type OPENFILENAME
    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
'--------------------------------------------------------------

Using the Windows Common Dialog

Here's a VBA function ShowSave. It wraps the Windows function GetSaveFileName so that it's easy to call from your VBA code. Call it like this… 

Dim dgnFile  As String
dgnFile = ShowSave ("Save Design File As...", "Microstation Files (*.dgn)", "*.dgn", "V:\shared")

' ---------------------------------------------------------------------
'   ShowSave    Save As... common dialog
'   Arguments:  [in, String] dialog title,
'               [in, String] filter description, [optional]
'               [in, String] filter spec, [optional]
'               [in, String] default directory [optional]
'   Example call:
'   dgnFile = ShowSave ("Save Design File As...", "Microstation Files (*.dgn)", "*.dgn", "V:\shared")
'   Returns:    full path of file to be saved
' ---------------------------------------------------------------------
Public Function ShowSave( _
    ByVal strDialogTitle As String, _
    ByVal strProposed As String, _
    Optional ByVal strFilterDescr As String = "All files (*.*)", _
    Optional ByVal strFilterSpec As String = "*.*", _
    Optional ByVal strDefaultDir As String = vbNullString) As String
    On Error Resume Next
    Dim strFilter                           As String, _
        strFileSelected                     As String, _
        proposed                            As String
    Dim OFName                              As OPENFILENAME
    strFilter = strFilterDescr + Chr$(0) + strFilterSpec + Chr$(0)

    proposed = strProposed & Chr$(0) & Space$(254 - Len(strProposed)) 'Create a buffer
    Const Period                            As String = "."
    With OFName
        .lStructSize = Len(OFName) 'Set the structure size
        .hWndOwner = 0& 'Set the owner window
        .hInstance = 0& 'Set the application's instance
        .lpstrFilter = strFilter 'Set the filter
        .lpstrFile = proposed
        '.lpstrDefExt = Space$(Len(strFilterSpec))
        .lpstrDefExt = Mid$(strFilterSpec, 1 + InStr(strFilterSpec, Period))
        .nMaxFile = 255 'Set the maximum number of chars
        .lpstrFileTitle = Space$(254) 'Create a buffer
        .nMaxFileTitle = 255 'Set the maximum number of chars
        If (vbNullString <> strDefaultDir) Then _
            .lpstrInitialDir = strDefaultDir 'Set the initial directory
        .lpstrTitle = strDialogTitle 'Set the dialog title
        .flags = OFN_OVERWRITEPROMPT 'no extra flags
    End With
    If GetSaveFileName(OFName) Then 'Show the 'Save File' dialog
        strFileSelected = Trim$(OFName.lpstrFile)
        If (InStr(strFileSelected, Chr(0)) > 0) Then
            strFileSelected = Left(strFileSelected, InStr(strFileSelected, Chr(0)) - 1)
        End If
        ShowSave = Trim(strFileSelected)
    Else
        ShowSave = ""
    End If
End Function

Call Your File Open/Close Wrapper

ShowSave wraps the Windows function GetSaveFileName. Wrapping the Windows function makes it easy to call from your VBA code. You, or someone else using your wrapper, don't know (or need to know) that it contains a Windows function call. ShowSave is easy to call from your VBA code. Call it like this… 

Dim dgnFile  As String
dgnFile = ShowSave ("Save Design File As...", "Microstation Files (*.dgn)", "*.dgn", "V:\shared")

Dim msg      As String
If (0 < len (dgnFile)) Then
  msg = "User chose file '" & dgnFile & "'"
Else
  msg = "User cancelled file save"
End If
ShowMessage msg, msg, msdMessageCenterPriorityInfo

Trademarks

All trademarks are acknowledged. Trademarks may be registered in some jurisdictions.
Contact LA Solutions using this enquiry form.


Home		 Updated on 04-Mar-2007 Copyright © 2007 LA Solutions Ltd