Textbox contents as folder/filepath

[carlandtina02]

Hi Guys,

I need a little help with the below code
The current code adds the contents of a textbox to the folderpath and pulls the file, But now i need the query to be in three parts:

1). A two part folder path consisting of:

a. (1st half ) "C:\Documents and Settings\carl.walker\My Documents\Reports\"
b. (2nd half) "Contents from textbox1"

2). The file name to pull from "textbox2"



Any help with this would be great.
Thanks in advance

Carl

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

End Sub]

[BadlySpelledBuoy]

Difficult to test with no sample workbook, but perhaps the below (or something like it) would do the trick:

Const strFldrPath As String = "C:\Documents and Settings\carl.walker\My Documents\Reports\" & TextBox1 & "\" & TextBox2 'Depending on the content of TextBox2 you may need to account for the file extension here.

BSB

[jindon]

Do you need to loop ?

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
Dim FileFound As Boolean: FileFound = False

CurrentFile = Dir(strFldrPath & "*" & FileName & "*")

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & strFldrPath

End If

BTW, variable declared as Const MUST be constant.

[carlandtina02]

Hi Guys,

Thanks for your replies,

Hi Jindon,

Your code looks as if it should work but when i run it, it displays the MsgBox "No Serial number available", as if its not seeing textbox " TBM1Serial"?
Am I doing something wrong


Thanks
Carl

Private Sub CommandButton28_Click()

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
Dim FileFound As Boolean: FileFound = False

CurrentFile = Dir(strFldrPath & "cmbDERS" & FileName & "TBM1Serial")

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & strFldrPath

End If

End Sub

[jindon]

I though "FileName" would be a global variable, since you haven't assign within procedure.

If not, you need to add one line

FileName = Me.TBM1Serial  '<--- this line
If FileName = vbNullString Then

But, yes, you still need to loop, because the user would enter just "g" or something...

[carlandtina02]

Hi Jindon,

I done as you suggested but the msg box that appears, looks like its not adding the folder path from value "cmbDERS" to the string in order to find the file in that folder? also did i mention that the file is pdf?

Private Sub CommandButton28_Click()

FileName = Me.TBM1Serial
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
Dim FileFound As Boolean: FileFound = False

CurrentFile = Dir(strFldrPath & "cmbDERS" & FileName & "TBM1Serial")

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & strFldrPath

End If

End Sub

regards
carl

[jindon]

Try change

CurrentFile = Dir(strFldrPath & "cmbDERS" & FileName & "TBM1Serial")

to

CurrentFile = Dir(strFldrPath & cmbDERS & "*" & Filename & "*" , 0)

And see how it goes...

[carlandtina02]

Hi Jindon,

I'm away from home so will test it out on Friday when I get back.

Thanks again
Carl

[carlandtina02]

Hi Jindon,

I tried with the altered line but still no joy unfortunately.....any ideas?

Private Sub CommandButton28_Click()

FileName = Me.TBM1Serial
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
Dim FileFound As Boolean: FileFound = False

CurrentFile = Dir(strFldrPath & cmbDERS & "*" & FileName & "*", 0)

'CurrentFile = Dir(strFldrPath & "cmbDERS" & FileName & "TBM1Serial")

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & strFldrPath

End If

End Sub

[jindon]

I need to see the real value of

MsgBox strFldrPath & cmbDERS & "*" & FileName & "*"

[carlandtina02]

Hi Jindon,

Value of cmbDERS is "a" which is the folder name

Value of FileName is "a123" which is the file name

I have attached a screen shot of the MsgBox that runs.

Thanks
Carl

Screen grab.jpg

[carlandtina02]

And with file

[jindon]

I can not see your picture... something is wrong I guess.

[carlandtina02]

Hope this works!

[jindon]

It is OK this time..

Can you also show me the mgsbox?

msgbox Dir(strFldrPath & cmbDERS, 16)

[carlandtina02]

Hi,

I only get that one msgbox shown above, even if i remove folder "a" from the "reports" folder?

Carl

[jindon]

I mean

Private Sub CommandButton28_Click()

FileName = Me.TBM1Serial
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
Dim FileFound As Boolean: FileFound = False
msgbox Dir(strFldrPath & cmbDERS, 16)  ' this one 
CurrentFile = Dir(strFldrPath & cmbDERS & "*" & FileName & "*", 0)

'CurrentFile = Dir(strFldrPath & "cmbDERS" & FileName & "TBM1Serial")

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (strFldrPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & strFldrPath

End If

End Sub

[carlandtina02]

Hi,

I only get that one msgbox shown, even if i remove folder "a" from the "reports" folder?

Carl

[carlandtina02]

ignore last message

[carlandtina02]

Second screen grab

[jindon]

Can you just try this?

Private Sub CommandButton28_Click()

FileName = Me.TBM1Serial
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, myPath as string
Dim FileFound As Boolean: FileFound = False
mypath = strFldrPath & cmbDERS & iif(right$(cmbDERS,1)<>"\","\","")
msgbox Dir(mypath, 16)  ' this one 
if mypath = "" then
    msgbox "No such folder":exit sub
endif
CurrentFile = Dir(mypath & "*" & FileName & "*", 0)

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (mypath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & mypath

End If

End Sub

[carlandtina02]

Nearly there now

I get the msgbox shown and if i click ok if retrieves the correct file, but if i delete folder "a" i just get a blank msgbox?

[jindon]

What do you mean by "delete folder "a"" ?

[carlandtina02]

I was doing some error checking to see what would happen if folder "a" didn't exist and to see if msg "no such file" ran.

[jindon]

Try change

mypath = strFldrPath & cmbDERS & iif(right$(cmbDERS,1)<>"\","\","")

to

mypath = strFldrPath & cmbDERS
If right$(mypath,1) <> "\" then mypath = mypath & "\"

[carlandtina02]

Hi Jindon,

I tried the following but it does the same thing?

Private Sub CommandButton142_Click()


FileName = Me.TBM1Serial
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, myPath As String
Dim FileFound As Boolean: FileFound = False
myPath = strFldrPath & cmbDERS
If Right$(myPath, 1) <> "\" Then myPath = myPath & "\"
MsgBox Dir(myPath, 16)  ' this one
If myPath = "" Then
    MsgBox "No such folder":
    
    Exit Sub
    
End If
CurrentFile = Dir(myPath & "*" & FileName & "*", 0)

If Len(CurrentFile) Then
ActiveWorkbook.FollowHyperlink (myPath & CurrentFile)
FileFound = True

End If

If FileFound = False Then
MsgBox Title:="File Not Found", _
Prompt:="File """ & FileName & """ not found in " & myPath

End If

End Sub

[jindon]

What is "same thing"?

[carlandtina02]

Sorry Jindon, I'm not explaining myself properly as usual

When the code runs, msgbox "." appears, and only when i click "ok" it retrieves the file.
What i need, is for it to retrieve the file without the msgbox appearing and need the msgbox to appear only if the folderpath does not exist.

We're pretty much there now, thanks for your help so far

Regards
Carl

[jindon]

You can delete unwanted msgbox, that was a msg for me to check....

[carlandtina02]

Hi Jindon,

Yes of course, what am I thinking

Thanks for all your efforts with this, you've really helped me out

Regards
Carl