Open file from certain path :{Solved}:

daniels012

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

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Short answer, yes.:)

But what would you put there?

PS did you try the code I posted?
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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

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

Icaro

New Member
Joined
Oct 9, 2006
Messages
5

ADVERTISEMENT

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

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

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

Where is the workbook with the code located?
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
One is here:
C:\Documents and Settings\Primary User\Desktop\Toms Main\
the other is here:
C:\Documents and Settings\MyDocuments\Daily\

Michael
 

Forum statistics

Threads
1,137,199
Messages
5,680,118
Members
419,883
Latest member
overhear

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