Updating a file location with Macro

[Johnny Thunder]

Hello Friends,


I am trying to write a macro that will update a file location based on where the file is on the server by how the user has his drives mapped.

For example; on my computer file "Cost Allocations" is on my :H Drive

But on someone elses computer "cost Allocations" is on there :G drive because of how there drives were mapped.

So when I send the file to someone I want a pop-up to show to tell the person they need to locate the file and when they find it the macro will retain the file location and update all formula's to reference that persons drive location.

Excel 2003
Any help would be appreciated!

 

[sous2817]

Wouldn't the UNC be the same regardless of what letter it's mapped to? May be better to use that rather than the drive letter...

 

[Johnny Thunder]

when I have the UNC in the formula it donesn't work anymore? I am not sure why?

 

[sous2817]

Can you post your code, I've used UNCs and never had a problem.

 

[Johnny Thunder]

Can you post your code, I've used UNCs and never had a problem.

\\Wb\cp\apps\CPLMS\WBCP Financial Systems\PECOS\PECOS Cost Allocations\ACTUALS CK\2011\Cost Allocation Project\2011 Cost Allocation Step 1_Test.xls - This is the server location and file name.

This is the formula whee it needs to be applied too.

=SUMPRODUCT(--('Data Dump'!$G$2:$G$3435=$C2),--('Data Dump'!$J$2:$J$3435=$E2),--('Data Dump'!$K$2:$K$3435=$G2),'Data Dump'!Y$2:Y$3435)*$L2*0.77

anywhere that has the tab data dump is where the server location is not working. Someone was telling me that the reason it doenst work is because this is the server name\File name\ but the tab name is not geting pulled.


Thank you for all your help!

 

[sous2817]

Have a look here:

http://office.microsoft.com/en-us/e...age-links-to-other-workbooks-HA001054812.aspx

Towards the middle you'll see "Network drive letters in links" and see if that helps at all. It's going to be a long, nasty looking formula because of the link of your UNC, but it should work. When you said you were using a macro, I was expecting VBA not a formula. I've used UNC references in my VBA code, never in a formula, but I don't see why it wouldn't work.