Microsoft Office Forums - View Single Post - [Solved] Using user form fields as folder path and file name

carlandtina02 · 03-21-2016, 05:14 PM

Hi Guys,

I have code that adds the value of a userform textbox as the file name,adds it to the folder path, then goes and pulls the file.

I need the code to use the folderpath (in the below code) open a folder based from the value in Text Box "TBM1Customer" then look for the file name and pull the file (in that order).

Any ideas would be much appreciated

Thanks
Carl

Code:

If Filename = vbNullString Then
  MsgBox "No Serial number available."
  Me.TBM1Serial.SetFocus
  Exit Sub
End If
Const strFldrPath As String = "C:\Documents and Settings\carl.walker\My Documents\Reports\"
Dim CurrentFile As String: CurrentFile = Dir(strFldrPath)
Dim FileFound As Boolean: FileFound = False
While CurrentFile <> vbNullString And FileFound = False
  If InStr(1, CurrentFile, Filename, vbTextCompare) > 0 Then
    ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
    FileFound = True
  End If
  CurrentFile = Dir()
Wend
If FileFound = False Then
  MsgBox Title:="File Not Found", _
  Prompt:="File """ & Filename & """ not found in " & strFldrPath
End If
'Unload Me