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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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?
 

daniels012

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

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
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>>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Michael

Where will the file the code is being run from reside?
 

daniels012

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
 

daniels012

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

daniels012

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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
Top