Edit cell reference only for save as path, macro lock

Please help me to solve the issue mentioned herein.

Im suppose to save a file as "REC AFTER BUSINESS 777.xls" in any location where the original spreadsheet resides.
Instead I get this "ThisWorkbook.PathREC AFTER BUSINESS 777.xls"

Criteria: Macro lock, I have no access to it. We are given the option to edit the unprotected cells only.
Currently, we map our drive differently, some using S: some using P: etc...
To avoid changing the path frequently, is there a generic way of assigning the path in Range ("D6").


To edit cell D6, I tried to edit D6 to ThisWorkbook.Path & "\" but failed to run the macro.

Please Login or Register  to view this content.

EXCEL SHEET CELLS VALUE AS BELOW

D3 = 777
D4 = REC
D5 = AFTER BUSINESS
D6 = ThisWorkbook.Path

Hi, ntljennifer,

please have a read at Forum Rule #3 and add code-tags to your procedure.

If you put ThisWorkbook.Path into a cell it will be treated as a string not as what you want it to be contemplated (the actual path to the workbook).

Enter the following formula into the cell D6:

Formula:

Please Login or Register  to view this content.

which should show the proper path to the workbook pnce it is saved.

Ciao,
Holger

Hi, ntljennifer,

according to the Forum Rules which you accepted you should add code-tags to your procedure from the opening post. Until that has been done no further answer to your thread should be given ( Forum Rule #7 ).

Ciao,
Holger

Hi, ntljennifer,

AFAIK the routine you use in the cell is an old Excel4-function which will retrace the path to the workbook. A1 could be changed to any valid cell reference on the sheet but A1 should be present on any sheet while ZZ1 would not be fitting on an Excel2003-workbook which fesatures only 256 column while ZZ should be 676.

Find will deliver the position of the of the square bracket indicating the sheetname (have a look at the reference to any closed workbook for understanding). Length minus 2 will deliver the corresponding to ThisWorkbook.Path. If you use minus 1 you will get the trailng backslash from the path as well and would not need to add it.

HTH,
Holger