Macro: Edit link on behalf of Cell Information

[lnkinho]

Hi

Is it possible to edit link via Macro with information from a specific cell?

I want to change the excelfile name "110.xlsx" via a macro. So I thought I can put the filename in cell B1, lets say "109".

So when I click on the macro: either the macro can seach for 110 and replace all with B1, or change the name of the link. Not sure if either way is possible?

"=IFERROR('C:\Users\XX\Desktop\XX\cashflow\[110.xlsx]Contract Template'!$B2;0)"

Got any tips?

 

[Arithos]

This might be useful to you, the same ish issue there.

Or something like this


=Indirect("IFERROR('C:\Users\XX\Desktop\XX\cashflow\["& B1 &".xlsx]Contract Template'!$B2;0)")

 

[lnkinho]

Hi,

hm, when I try that link I get #REF!. Any Idea why?

 

[Arithos]

Link or formula?

If formula:
You have changed the "XX" to what it should be? And you have placed the workbook name in cell B1? the Workbook should be open, is it? (Last should not create ref error tho)

 

[lnkinho]

Hm, think its the formula. Hm, Its open. But is it only possible when its open. I want it to be possible to not open the external workbook - isnt that possible?

Thats the link: =INDIRECT("IFERROR('C:\Users\mathis.edel\Desktop\FRAMO\cashflow\["& B1 &".xlsx]Contract Template'!$B2;0)")

 

[Arithos]

Indirect just fix the formula. Does it work without Indirect? but when you insert into the Indirect, and add the & B1 & . It wont work`?

 

[lnkinho]

Hm, Im not sure. We can drop the Iferror for now, then the link is "='C:\Users\mathis.edel\Desktop\FRAMO\cashflow\[109.xlsx]Contract Template'!$B4" Could you put that link into the indirect formula?

 

[Arithos]

That should be like below, and have 109 in cell B1 of the same sheet as the formula.

=INDIRECT("='C:\Users\mathis.edel\Desktop\FRAMO\cashflow\[ " & B1 & ".xlsx]Contract Template'!$B4")

 

[lnkinho]

Look here, doesnt work.. Cant understand what is wrong actually..

 

[Arithos]

I cant see anything i'm afraid. Test the indirect formula without the workbook filepath, and just the name, and see how it does.

Your workbook is open?