Open file anywhere

[Villalobos]

Hi,

I would like to ask some help regarding file opening. My target is that if the specified file is somewhere on device "E" then the code open it (enough to give the filename in "Combobox1" only ). Unfortunately the currently used code open the file if the path is exactly defined.

 Private Sub CommandButton1_Click()
Dim DirFile As String
Dim WB As Variant
    DirFile = "E:\Home\AB\CD " & ComboBox1 & ".xlsm"
        If Len(Dir(DirFile)) = 0 Then
            MsgBox "File does not exist in this folder!"
        Else
            Set WB = Workbooks.Open(DirFile)
        End If
End Sub

Could somebody help me how to do it?

 

[sn152]

Hi Villalobos,

Please see the attached. Let me know if this is ok.

 

[Villalobos]

Hi sn152,

Thank you for your time.
Regarding you solution... I do not want to store the file path (because the path is continuously changing) in the workbook. I just would like that if i would be able to open the file by the name only (if the file is somewhere on device "E").

 

[Deepak]

check this....

Option Explicit

Private Sub CommandButton1_Click()
Dim DirFile As String, FPath As String
Dim WB As Workbook

FPath = "E:\Home\AB\CD\"
    DirFile = Dir(FPath & ComboBox1 & ".xlsm")
        If Len(DirFile) = 0 Then
            MsgBox "File does not exist in this folder!"
        Else
            Set WB = Workbooks.Open(FPath & DirFile)
        End If
End Sub

 

[Villalobos]

Not exactly what I would like to reach it.

Please, let me to clarify a bit.
When I try to open the requested file, in that moment I do not know exactly what is the correct path because the requested file path is continuously changing in every hour on device "E". The only thing what I know beside the file name: the file is on device "E" and nothing more. So the file could be in folder "AB" or "CD" or anywhere. My question would be that how is it possible to open a file if you just know the name and that is on device "E", the subfolders are unknown.

 

[Deepak]

Loop it..

http://stackoverflow.com/questions/19988122/vba-macro-to-list-files-in-multiple-folders

 

[Villalobos]

at the first sight it seems to me will be good for me... thanks the link