Open file from certain path :{Solved}:

[daniels012]

We have 2 copies of a single file.
One is on :
Path2 = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"
The other:
Path3 = "C:\Documents and Settings\MyDocuments\Daily\"

I need it to pick the proper path based on the computer it is on. Is this even possible?

If I need to give more details, let me know!

Michael

 

[Norie]

Michael

Do you know the filename?

If you do you could perhaps use FileSearch to locate it.

Otherwise you could use Dir to check for the existence of the folder/file.

Dim strPath As String
Dim wb As Workbook

     strPath = "C:\Documents and Settings\Primary User\Desktop\Toms Main\" 
     If Dir(strPath & "Filename.xls")<>"" Then
        Set wb = Workbooks.Open (strPath & "Filename.xls")
     End If

     strPath = "C:\Documents and Settings\MyDocuments\Daily\" 

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

 

[daniels012]

Let me ask this!
Can I put something in cell J1 that would give me the current path and file name. Then open the file with this name?

Michael

 

[Norie]

Short answer, yes.

But what would you put there?

PS did you try the code I posted?

 

[daniels012]

OK
I figured out I can put:
=CELL("filename") in cell J1.

What would be the easiest way for me to use this to open my file?

Michael

 

[Norie]

Michael

Can you give more information?

Is the file you want to open in the same directory as the file with code?

PS you are using code aren't you, I've just realised you've not mentioned any.

 

[Icaro]

OK
I figured out I can put:
=CELL("filename") in cell J1.

What would be the easiest way for me to use this to open my file?

Michael

inserting an hiperlink?

 

[daniels012]

Yes,
I am sorry let me clarify.

Here is the exsisting code I use:

Application.ScreenUpdating = False
ActiveWorkbook.Save
Path = "\\Sidonna\c\Estimating\"
Path2 = "C:\Documents and Settings\Owner\My Documents\Daily\"
ActiveWorkbook.SaveAs Filename:= _
Path & _
Range("C9").Value & ".xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
new_file = ActiveWorkbook.Name
Range("F2").Select
Workbooks.Open Filename:=Path2 & "Estimating Sheet and R17 Fill.xls" Workbooks(new_file).Save
Workbooks(new_file).Close
Application.ScreenUpdating = True

When I use the code it works fine if this workbook is loaded on this computer:
Path2 = "C:\Documents and Settings\Owner\My Documents\Daily\"
If it is loaded on Tom's computer, obviously it gives an error. This is why I wanted to do what I want to do.
If I need to update a workbook, I can update both and not worry about which path.

Does this make more sense??


Michael

 

[Norie]

Michael

Where is the workbook with the code located?

 

[daniels012]

One is here:
C:\Documents and Settings\Primary User\Desktop\Toms Main\
the other is here:
C:\Documents and Settings\MyDocuments\Daily\

Michael