Environment
InfoConnect Desktop (including Pro, for Unisys, for Airlines) 16.0 and higher
Reflection Desktop (including Pro, for X, for IBM, or for UNIX and OpenVMS) 16.0 and higher
Reflection Desktop (including Pro, for X, for IBM, or for UNIX and OpenVMS) 16.0 and higher
Situation
You are creating a Visual Basic for Applications (VBA) macro, and you want to allow the macro user to browse and select a file name.
Resolution
The Windows API provides a function called GetOpenFileName for this purpose. This example demonstrates how to use GetOpenFileName using Reflection VBA. Copy the following VBA code to a module in the Reflection VBA editor, and run the "test" subroutine.
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
Private Declare Function GetOpenFilename _
Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Public Function OpenFileNameDialog() As String
Dim fileName As String
Dim ofn As OPENFILENAME
With ofn
'Size of structure.
.lStructSize = Len(ofn)
'Size of buffer.
.nMaxFile = 260
'Create buffer.
.lpstrFile = String(.nMaxFile - 1, 0)
'Call function.
Ret = GetOpenFilename(ofn)
'Non-zero is success.
If Ret <> 0 Then
'Find first null char.
n = InStr(.lpstrFile, vbNullChar)
'Return what's before it.
fileName = Mid(.lpstrFile, 1, n - 1)
OpenFileNameDialog = .lpstrFile
End If
End With
End Function
Sub test()
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
Private Declare Function GetOpenFilename _
Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Public Function OpenFileNameDialog() As String
Dim fileName As String
Dim ofn As OPENFILENAME
With ofn
'Size of structure.
.lStructSize = Len(ofn)
'Size of buffer.
.nMaxFile = 260
'Create buffer.
.lpstrFile = String(.nMaxFile - 1, 0)
'Call function.
Ret = GetOpenFilename(ofn)
'Non-zero is success.
If Ret <> 0 Then
'Find first null char.
n = InStr(.lpstrFile, vbNullChar)
'Return what's before it.
fileName = Mid(.lpstrFile, 1, n - 1)
OpenFileNameDialog = .lpstrFile
End If
End With
End Function
Sub test()
Dim theFile As String
theFile = OpenFileNameDialog()
MsgBox theFile
End Sub
End Sub
Additional Information
Microsoft documentation for GetOpenFileName: https://msdn.microsoft.com/en-us/library/windows/desktop/ms646927(v=vs.85).aspx