Open a file without the directory name

[daniels012]

Can i open a workbook without knowing the directory? more importantly without showing the directory?

I want open the file "EstimatingSheet" where ever it is.

Thank you,
Michael

 

[iknowu99]

Yes and No.... it seems like we are trying to weed out the files that will not be processed. Therefore a condition is necessary, see if you can understand/test the following:

Sub gothroughCopenspecificfilename()

cdDir = "C:\"

    With Application.FileSearch
        .LookIn = cdDir
        .Filename = "*" & ".xls"
        .FileType = msoFileTypeExcelWorkbooks
        .SearchSubFolders = True 'search sub directories as well
        .Execute
        If .Execute > 0 Then
        
            'loop through all found files
            For IFoundFiles = 1 To .FoundFiles.Count
                
                'set incidental variables
                Pos = InStrRev(.FoundFiles(IFoundFiles), "\")
                file = Right(.FoundFiles(IFoundFiles), Len(.FoundFiles(IFoundFiles)) - Pos)
                Path = Left(.FoundFiles(IFoundFiles), Pos)
                
                'XXXXXXXXXXXXXXXXXXX
                'important condition to see if file name is "such" then open file
                If file = "EstimatingSheet" Then 'If InStr(file, "stimati") > 0 Then      >>>> might be better
                    'personally a better way i found is to use instr function and to search the inside part of that word
                    
                    
                    Application.DisplayAlerts = False
                    Set wbSource = Workbooks.Open(Path & file)
                    Application.DisplayAlerts = True
                    
                    
                    
                    
                   'do whatever here, like go through every sheet and copy Z26 to A1
                    For isheet = 1 To Worksheets.Count
                        sheetName = Sheets(isheet).Name
                        If sheetName = "EstimateWorkSheet" Then Range("A1") = Range("Z26")
                    Next isheet
                    'do whatever here, like go through every sheet and copy Z26 to A1
                    
                    
                    
                
                End If ' If file = "Estimate" Then open file
            Next IFoundFiles
        End If ' If .Execute > 0 Then
    End With 'With Application.FileSearch
    
End Sub