Open a file without the directory name

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Michael

It could be possible but are you sure there's no way to specify it's directory?

Perhaps it's in the same folder as the current workbook?

Perhaps the name of the folder has some sort of naming convention?
 
Upvote 0
Norie thenk you for responding! :oops: :biggrin:

Here is my code:
Code:
Sub SaveToSidonna()
'
    Dim wb As Workbook
    Dim strPath As String, fname As String, i As Integer
    
        Application.ScreenUpdating = False
            Sheets("ESTIMATING").Select
            ActiveWorkbook.Save
            Path = "\\Sidonna\c\Estimating\"
            strPath = "\\Sidonna\c\Estimating\"
            fname = Range("B11") & "*.xls"
        With Application.FileSearch
            .NewSearch
            .LookIn = strPath
            .Filename = fname
            .Execute
            i = .FoundFiles.Count
        End With
    If i > 0 Then
        fname = Range("B11") & i + 1 & ".xls"
            x = MsgBox("Your file already exists, do you want to make another copy?", vbYesNo)
        Else
            fname = Range("B11") & ".xls"
    End If
        If x = vbYes Or i = 0 Then ActiveWorkbook.SaveAs strPath & fname

    'Path2 = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"
    new_file = ActiveWorkbook.Name
        Workbooks(new_file).Save
        Workbooks(new_file).Close
    'Range("F2").Select
    'Workbooks.Open Filename:=Path2 & "EstimatingSheetTest.xls"
    strPath = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"
     If Dir(strPath & "EstimatingSheet.xls") <> "" Then
        Set wb = Workbooks.Open(strPath & "EstimatingSheet.xls")
     End If

     strPath = "C:\Documents and Settings\Owner\My Documents\Daily\"

     If Dir(strPath & "EstimatingSheet.xls") <> "" Then
        Set wb = Workbooks.Open(strPath & "EstimatingSheet.xls")
     End If
      
    Application.ScreenUpdating = True
    
End Sub

As you can see now I have code like this for 2 of our peoples computer:
Code:
strPath = "C:\Documents and Settings\Owner\My Documents\Daily\"

     If Dir(strPath & "EstimatingSheet.xls") <> "" Then
        Set wb = Workbooks.Open(strPath & "EstimatingSheet.xls")
     End If

I am trying to avoid entering every possible users computer path to get to the right file. which would also make the code longer.
So I was trying to enter just the name of the folder. In the code above the user opens "Estimating Sheet", but the code changes the name and closes it. I would like it to do this, but then open the "Estimating Sheet" back up.

Does this make sense?

Michael
 
Upvote 0
Code:
'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>>
 
Upvote 0
Michael

Where will the file the code is being run from reside?
 
Upvote 0
Norie,
It is in the Estimating Sheet Module 1 code
Is this what you mean?
I click a button then the code is executed.

Michael
 
Upvote 0
Michael

No it's not.

And I think I'm a little confused.

You say you want to open the EstimatingSheet workbook, but then you say that's where the code is.:eek:
 
Upvote 0
OK
I open a file Called "Estimating Sheet".
I fill out all the data on the Estimating Sheet
I hit a button and it saves a copy of the file... naming it what ever the text is in Cell B11 to another directory.
Code:
Sub SaveToSidonna() 
' 
    Dim wb As Workbook 
    Dim strPath As String, fname As String, i As Integer 
    
        Application.ScreenUpdating = False 
            Sheets("ESTIMATING").Select 
            ActiveWorkbook.Save 
            Path = "\\Sidonna\c\Estimating\" 
            strPath = "\\Sidonna\c\Estimating\" 
            fname = Range("B11") & "*.xls" 
        With Application.FileSearch 
            .NewSearch 
            .LookIn = strPath 
            .Filename = fname 
            .Execute 
            i = .FoundFiles.Count 
        End With 
    If i > 0 Then 
        fname = Range("B11") & i + 1 & ".xls" 
            x = MsgBox("Your file already exists, do you want to make another copy?", vbYesNo) 
        Else 
            fname = Range("B11") & ".xls" 
    End If 
        If x = vbYes Or i = 0 Then ActiveWorkbook.SaveAs strPath & fname 

    'Path2 = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"



i then save and close the newly created file.

Code:
new_file = ActiveWorkbook.Name 
        Workbooks(new_file).Save 
        Workbooks(new_file).Close

I then want to open Estimating Sheet again.

Is this clearer??
 
Upvote 0
I guess I'll just have to list every possible computer like the 2 I already have. :( :(
I just have to go find out everyone's directory and if we get a new person with the file, I have to add another directory. :cry: :unsure:

Michael
 
Upvote 0
Michael

I'm not sure if that's needed but then again I'm not sure what you are doing.:eek:

Why close the workbook in the first place?
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top