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

 

[daniels012]

Well, I want to start fresh with the Estimating sheet. If it does not close it has the new workbook open with the new name.

Does this make any more sense? Norie i am sorry for bieng a pain. It works fine when I list the Directories?????

Michael

 

[Norie]

Michael

Sorry it just doesn't make sense.

Perhaps I'm missing something?

If you want to use the new workbook why not close the original workbook and leave the new one open?

 

[daniels012]

OK
Let me try to be clearer.
If I save a file using:

If x = vbYes Or i = 0 Then ActiveWorkbook.SaveAs strPath & fname

Then this will leave the file open... When I go to do my next Estimating Sheet it has the one that is open NOT EstimatingSheet?? Here is what I am talking about!

Michael

 

[daniels012]

Ok then!! back to my original question, can I write code to open a file without the directory? Or maybe search for a file then open it?

Michael

 

[daniels012]

bump

 

[daniels012]

can I open a search using code to find a file?

Michael

 

[iknowu99]

'i find this code useful
cdDir = "C:\Documents and Settings\"

    'Start FileSearch
    With Application.FileSearch
        .LookIn = cdDir
        .Filename = "*" & ".xls"
        .FileType = msoFileTypeExcelWorkbooks
        .SearchSubFolders = True
        .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)
                
                                                            Application.DisplayAlerts = False
                Set wbSource = Workbooks.Open(path & file)
                                                            Application.DisplayAlerts = True
                    
                    For isheet = 1 To Worksheets.Count
                        wbSource.Activate
                        sheetName = Sheets(isheet).Name
                        
                        If InStr(sheetName, "MATIN") > 0 Or InStr(sheetName, "mating") > 0 Then  'ESTIMATING

let me know if more explaining is necessary>>

if file="estimating.xls" then 'code to open
the code i mentioned before works well for me as far as checking that the worksheet name matches; the code goes through the directory and subdirectories as well!! which means you can put the directory as C:\

 

[iknowu99]

'i find this code useful
cdDir = "C:\Documents and Settings\"

    'Start FileSearch
    With Application.FileSearch
        .LookIn = cdDir
        .Filename = "*" & ".xls"
        .FileType = msoFileTypeExcelWorkbooks
        .SearchSubFolders = True
        .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)
                
                                                            Application.DisplayAlerts = False
                Set wbSource = Workbooks.Open(path & file)
                                                            Application.DisplayAlerts = True
                    
                    For isheet = 1 To Worksheets.Count
                        wbSource.Activate
                        sheetName = Sheets(isheet).Name
                        
                        If InStr(sheetName, "MATIN") > 0 Or InStr(sheetName, "mating") > 0 Then  'ESTIMATING

let me know if more explaining is necessary>>

if file="estimating.xls" then 'code to open
the code i mentioned before works well for me as far as checking that the worksheet name matches; the code goes through the directory and subdirectories as well!! which means you can put the directory as C:\

 

[daniels012]

iknowyou99,

Not all sheets start with the "Documents and Settings"
ie: C:\Users\michael\Documents\
So is this what you mean when you say I can change:

which means you can put the directory as C:\

instead of:

cdDir = "C:\Documents and Settings\"

thank you,
Michael

 

[daniels012]

Isn't this all I want/need to do?

Sub FindEstimating
cdDir = "C:\Documents and Settings\" 

    'Start FileSearch 
    With Application.FileSearch 
        .LookIn = cdDir 
        .Filename = "*" & ".xls" 
        .FileType = msoFileTypeExcelWorkbooks 
        .SearchSubFolders = True 
        .Execute 
                               Set wbSource = Workbooks.Open(path & file) 
End Sub

Michael