Updating Custom Function

[ptmuldoon]

I have a basic custom function to include the full file path and name in cell like such.

Please Login or Register  to view this content.

But when I either move or rename the file, I want the cell value to update to the current folder/file name structure automatically. And I only want to update this one specific worksheet or named ranged, and not the entire workbook as it slows down the opening of the file. I've tried using the below in the workbook module but it does not update the cells.

Please Login or Register  to view this content.

I did try changing the above to use CalculateFull and Excel throws a run time error. So what is the trick to updating a selected worksheet or named range in VBA without updating the entire sheet?

[ptmuldoon]

Well.

I found a way to do this using built in excel formula's like such. but there still could be an easier way, but I did need t e double \\ in the file path for OLE link in Word.

Please Login or Register  to view this content.

That give me something like C:\\Data\\Folder\\myfile.xlsx.

[PCI]

Another way could be to use names
FullPath =SUBSTITUTE(WkPath,"\","\\") & WkName
WkName =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
WkPath =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

and somewhere ( e.g. C3) put =FullPath