Using user form fields as folder path and file name

carlandtina02 · #1 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to keep formatted form fields after mail merge or replace text with formatted form fields	jer85	Word VBA	2	04-05-2015 10:00 PM
How can I get Outlook from one user to another w/o a .pst file (primary user on computer is corrupt)	Tenmakk	Outlook	0	03-01-2015 12:49 PM
How to find the path to the downloads folder when it is different for each user	CatMan	Excel Programming	2	12-07-2012 09:59 PM
Form Fields - Create blank form text field with if statement?	LAssist2011	Word	5	12-14-2011 03:02 PM
Update Outlook but user path changed	Ossie1972	Outlook	1	12-08-2010 08:19 PM