Is it possiple to use file dialog box to search for a single file in a folder?

[Rodney Roe]

I created a userform that through user input will get a directory i.e.("C:\some folder\some file.xxx") up, and opens the folder where the file exists with a file dialog box. From here I would like the file dialog box to only show the "some file.xxx" that the user selected, not every single file that is in the folder.

Is this possible?

I'm using this simple code

ChDir (directory)
    strDocument = Application.GetOpenFilename(Filter, 1, Title)
If Len(strDocument) < 6 Then Exit Function
    ActiveWorkbook.FollowHyperlink strDocument 'Open document
 

i'm using vba in excel 2007
What I have works, i'm just trying to refine my program.
If anyone can help or has any other ideas to accomplish this i'd appreciate it.

thx
RRR

[Guido Geurs]

This works in Excel 2003.(maybe You have to modify it for 2007)
Use a textbox to enter the filter and use .Filename

Private Sub CommandButton1_Click()
   On Error GoTo Cancel_Sub
   With CommonDialog1
      .CancelError = True
      .Filename = TextBox1.Text
      .InitDir = Environ$("USERPROFILE") & "\My Documents"
      .ShowOpen
   End With
Exit Sub
Cancel_Sub:
End Sub

[Rodney Roe]

Once I get past the with block it give my an error 424 object required at line 4.

[Guido Geurs]

I have test the code in 2007 with no errors!
Attached is my workbook with macro.

[Rodney Roe]

Sorry, i'm a little new with the commondialog. Ok, my problem was that I didn't have the commondialog control. Where do you find that? Appart from that, i have a file list.xls how do I get just that file to show up in the dialog box? I see that the text box adds the file name into the file name box in the dialog, but all the files are still in the dialog box. What I need this for is so that I can easily search out a file and be able to open it or drag and drop into another program.

thanks for your help.
RRR

[Guido Geurs]

To insert a ComDiaBox, just click RMB on the "Toolbox" and select the component in the "Additional Controls" window (see attached GIFs).

The Textbox is used to enter the FILTER for the ComDiaBox.
If You enter "test*" You will see all the files starting with "test".

You say You have a list with the filenames.
If the data is the exact filename, You don't have to use ComDiaBox for opening the file!
Just use a file systemObject(FSO) : see atachment.
Or go to the form with the filter and enter the "test*" to see all the files starting with "test".

[Rodney Roe]

Ok I think I got it now. I just needed to use the "*" to trigger it.

Last question how do I get it to open the file? Does it return a string that I have to open with a hyperlink or is there another way. I can right click on the file and select a program to open with, but when I press the open button or double click on the file it doesn't do anything. Am I supposed to programatically tell it what program to use to open with? I've searched the commondialog on the internet but everything i get is a little above my head right now.

To answer your question about the filename, in my program the user selects a file name and depending on the file name it programatically selects the folder it is in. From there the user should be able to open it or drag it into another program that we're using. Hence the reason why I need the commond dialog. I need both those options available. Thanks for you help I've learned lots.

RRR

[Guido Geurs]

ComDia does not open the file, it put only the path and filename in the var= CommonDialog1.FileName

In the macro I have attached I set the path and filename fixt to UserEnvironment and the filename from the sheet:

FILENAME = Environ$("USERPROFILE") & "\My Documents\" & ActiveCell.Value

If you want to use the selected filename from the CommonDialog1 then the code will be: (see attachment)

        RET = Shell("notepad.exe " & .FILENAME, vbNormalFocus)

For opening the right folder:
in the attachment the Dir is set with:

      .InitDir = Environ$("USERPROFILE") & "\My Documents"

For your program it will be something like this:

      .InitDir = PATH_FROM_MY_PROGRAM

If you want the path:

PATH = Left(.Filename, InStrRev(.Filename, "\") - 1)

[Rodney Roe]

Thanks Guido you've been a great help. Here's what I came up with.
I have two different options.

With CommonDialog1
   .CancelError = True
   .Filename = TextBox1.Text & "*"
   .InitDir = "insert Path"
   .ShowOpen
  If Dir(.Filename) <> "" Then
    ActiveWorkbook.FollowHyperlink .Filename
  Else
    MsgBox "File Not Found."
  End If
End With
 

or this:

With CommonDialog1
   .CancelError = True
   .Filename = TextBox1.Text & "*"
   .InitDir = "insert Path"
   .ShowOpen
  If FSO.FileExists(.Filename) Then
     RET = Shell("executable program to open file with " & .Filename, vbNormalFocus)
     Exit Sub
  Else
     MsgBox "File not found"
  End If
End With
 

this works, but with the hyperlink I loose some functionality of the file when opened i.e. can't "Save As", and with the shell it opens a new instance of the program every time instead of using the one I already have open. I'll have to do some more research on these. thanks for your help.