jerry12302
Active Member
- Joined
- Apr 18, 2005
- Messages
- 449
- Office Version
- 2010
- Platform
- Windows
I build formulas using concatenation and range value them to create the text of the actual formulas I need in order to build complex link formulas with variable ranges and file names. Below is an example:
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S24
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S24
Since Excel cannot link to another file using text of the desired file name and range within another cell, like I can do in Lotus with @@(@coord(3,5,100,8)), for example, I would like to remove the preceding apostrophes in the text of the linking formula using VBA. Currently I would have to edit every formula to manually remove it to get the formula I need. I have hundreds of these.
In Lotus a simple macro would be {EDIT}{HOME}{DEL}~ I could never find an Excel equivalent.
Any ideas?
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-01.xlsm]Summary Report'!S24
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S21
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S22
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S23
'=+'C:\MyExcelfiles\Budgets\2021\[Budget 2021-02.xlsm]Summary Report'!S24
Since Excel cannot link to another file using text of the desired file name and range within another cell, like I can do in Lotus with @@(@coord(3,5,100,8)), for example, I would like to remove the preceding apostrophes in the text of the linking formula using VBA. Currently I would have to edit every formula to manually remove it to get the formula I need. I have hundreds of these.
In Lotus a simple macro would be {EDIT}{HOME}{DEL}~ I could never find an Excel equivalent.
Any ideas?