Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Current Workbook Path
There is a drawback to using the ActiveWorkbook.FullName or anything else
that relies on the ActiveWorkbook object. If you have more than one spreadsheet open and force a recalculation for all open workbooks, then any function that uses ActiveWorkbook will point at the active workbook, not the workbook that the function resides in. So if Book1 has the ActiveWorkbook.FullName, and Book2 has focus when the forced recalc is invoked, Book1 will get the ActiveWorkbook.FullName for Book2. The other alternatives are to use the CELL function, but there are some messages on this board that indicates the CELL function can cause Excel to crash. Not a good thing. Using the ThisWorkbook is another alternative, but then the user will have to enter the same code in every workbook that needs the path information. Again, not a good thing. The last option is to create a User function that is passed a Range object that will retrieve the Workbook Path from the passed cell. Ex. Public Function MyWorkbookPath(PassedCell as Range) MyWorkbookPath = PassedCell.WorkSheet.Parent.FullName End Function This can be placed in an addin, which will allow you to use it wherever you feel the need, plus, it will not fail since it does not use the ActiveWorkbook object. Good luck Mark "Terry" wrote: Found it and I've been doing it with CurDir and suffering for a long time. Just took putting it out here for others for it to click in. this is a great newsgroup.. Just for your info we use this as a toolbar macro to put the file name in A1. I wonder if it could be a function of the save command? But there might be a case where there is something in A1. thinking out load. sorry. Dim MyPath As String MyPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name ActiveCell.Value = MyPath -----Original Message----- Instead of using CurDir is there a command to use to get the path of the current Workbook. CurDir doesn't always work if the person has minimized xcel and traveled around to other places and then openned the excel file again. I know Workbook has a path property but can not figure out how to use it. Thanks... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
Current Workbook Path | Excel Programming | |||
Current Workbook Path | Excel Programming | |||
Current path to Qualified Path | Excel Programming | |||
Set The Directory to the Current WB Path | Excel Programming |