writeback using VBA

[mafteutza]

Hello,

I want to alter a cell in a slice in excel, using TM1 Perspectives. I use the formula =DBR(... to retreive the data
It works fine when i do it manually, but when i use a macro in excel, it deletes the formula and just lets the text. The formula which helps me get the data from TM1 disappears and I am left with a simple cell filled with the text i wanted to insert.

I recorded a macro when I do this manually:
Sub Macro1()
Range("D4").Select
ActiveCell.FormulaR1C1 = "1.3"
Range("D5").Select
End Sub

When I run this macro(exactly the one I recorded) it does not write back into the database. The formula which helps me get the data from TM1 disappears and I am left with a simple cell filled with the text i wanted to insert. Any clues as whis is this happening or how could i solve this?

[tomok]

Hello,

I want to alter a cell in a slice in excel, using TM1 Perspectives. I use the formula =DBR(... to retreive the data
It works fine when i do it manually, but when i use a macro in excel, it deletes the formula and just lets the text. The formula which helps me get the data from TM1 disappears and I am left with a simple cell filled with the text i wanted to insert.

I recorded a macro when I do this manually:
Sub Macro1()
Range("D4").Select
ActiveCell.FormulaR1C1 = "1.3"
Range("D5").Select
End Sub

When I run this macro(exactly the one I recorded) it does not write back into the database. The formula which helps me get the data from TM1 disappears and I am left with a simple cell filled with the text i wanted to insert. Any clues as whis is this happening or how could i solve this?

You can't do this with a DBR formula using VBA. If you want to write back with VBA you'll have to place a value in the cell and use a call to a DBS formula to send that value to TM1.

[mafteutza]

Thank you for your help.
I have another question, about DBS formula: If I use this formula, will the database be overwritten every time I recalculate the sheet? Never mind, I see that it does. Have a nice day

[tomok]

Thank you for your help.
I have another question, about DBS formula: If I use this formula, will the database be overwritten every time I recalculate the sheet? Never mind, I see that it does. Have a nice day

It won't if your VBA creates the DBR formula, recalculates, and then destroys it.

[David Usherwood]

Years ago I wrote a routine to do this fully in VBA, using Application.Run("DBSW",.....). So you don't need to put a value in a cell first. But, importantly, you don't get the Stargate view speedup if you go that route - so it's not a good idea if you have lots of numbers to send.