Calculating a workbook file path

bdfagoodfella

Board Regular
Joined
Oct 22, 2004
Messages
64
Many of my workbooks are used by many people in many different departments.
I am tired of trying to correct problems caused by users accidentally creating a copy and then updating the copy instead of my original.
(Disabling the "save as" feature is just not enough.)

I am currently using the formula:
=CELL("filename",A1)
To retrieve the file location and compare it to my intended file location.

Unfortunately, sometimes the formula returns a UNC file path:
\\tpc-fs2\company_common\Production_History.......

And sometimes the formula returns a relative path(which could potentially change):
P:\Production_History……

Does anyone have any ideas on how I could force the formula to always return the UNC file path?

Thanks in advance for any suggestions.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi bdfagoodfella,

You may want to try something like the following, it only allows the file to be saved with the original file name back in the folder from which it was opened.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cells(1, 3) <> 1 Then Cancel = True
Cells(1, 3) = 1
ThisWorkbook.Save
Cells(1, 3) = ""
End Sub
Private Sub Workbook_Open()
Cells(1, 1) = ThisWorkbook.Path
Cells(1, 2) = ThisWorkbook.Name
Cells(1, 3) = ""
End Sub

Code:

ColinKJ
 
Upvote 0
I need to protect against dragging and dropping the file as well so I am going to examine the link that Andrew provided.
It looks like the code there is a step in the right direction. I just need to calculate the mapped drive letter so the user is not forced to enter it in a message box.

Thanks a ton guys!
 
Upvote 0
Hi, did you manage to get this working? I want to do the same with a Workbook but I was hoping to do it in an automated manner where the Drive Letter didnt need to first be entered..

Thanks
 
Upvote 0
I actually decided to provide all users with a shortcut to my file. Apparently using a shortcut set up with the UNC file path, allows the formula to calculate the location consistently using the UNC file path.
The only problem is the occasional person who opens the correct file by navigating to it in windows explorer. They receive a message telling them that they are in the wrong file. It is a minor inconvenience though. They call me and I simply tell them to use the darn shortcut.
This has turned out to be one of the most useful things I have ever incorporated into one of my spreadsheet applications so I highly recommend it.
 
Upvote 0
I didn't use any code for this piece actually...

a) I placed this formula in a worksheet cell:
=CELL("filename",A1)
b) I typed my intended file location in a second worksheet cell.
c) I wrote a simple macro, that fires when the spreadsheet opens, that checks to see if the two items match.

If the items do match I unhide the data entry screen.
If they don't match I show a screen that tells the user that they are in the wrong workbook.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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