How to create a VBA macro that allows the user to browse for a file name

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()

    Dim theFile As String

    theFile = OpenFileNameDialog()

    MsgBox theFile
End Sub