Código VBA para buscar un archivo excel por su nombre en un directorio con sus carpetas y capturar en una variable la ruta.

Rob, no tengo ni idea de Excel, lo que se dice ni idea, pero por si te puede dar una idea. ¿Para qué escribir, que siempre se puede cometer un error, pudiendo elegir. No hagas caso a los controles Fecha, ni al botón, que los uso para otra cosa. Si tengo el formulario

Cuando pongo el cursor en el cuadro de texto Archivo

Se abre un explorador para que localice el archivo en cuestión. Una vez localizado, en una carpeta determinada, hago clic sobre el archivo para seleccionarlo

En este caso, el enmarcado en azul y pulso abajo el botón de seleccionar y

Uso la función Buscaarchivo() que es

Public Function buscaArchivo() As String
Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.ButtonName = "Seleccionar"
.Title = "Seleccionar el archivo"
.InitialFileName = Application.CurrentProject.Path
.InitialView = msoFileDialogViewDetails
.Filters.Clear
.Filters.Add "All files", "*.*"
If .Show = True Then
buscaArchivo = .SelectedItems(1)
Else
MsgBox "Ha pulsado el botón <Cancelar>."
End If
End With
End Function

Donde yo pongo

.InitialFilename=Application...

tu puerdes poner

.InitialFileName="M:\Trial report"

Para que muestre de principio la carpeta que quieras

En el evento Al recibir el enfoque del cuadro de texto Archivo le tengo puesto

Private Sub Archivo_GotFocus()
Archivo = buscaArchivo()
End Sub

Importantísimo. Abrir el edito de VB. Pulsar Herramientas-Referencias y activar la casilla

Microsoft Office XX. X 0bject Library

Lo del XX. X es por la versión de Office que tengas instalada. En el Office 10, es 11.0 y en el 16 es

16.0

Si te puede dar una idea...