macro to perform a 'page dbrw'

[stingo]

hi,

the question is this,

I want to build a wxcel form without DBRW formulas, and create a macro associated to a button doing something like:

send all data in the range between cell A10 to C20.

Practically I want it to do a serie of DBRWs on all the cells contained in a table (if you know BPC is like the EVDRE command).

Is there someone having some idea in how to do it?

[qml]

There are a number of ways to achieve that, the simplest one not even requiring a macro, just a flag cell and DBS formulas (maybe somewhere in a hidden area. Like that: =IF(FlagCell=1,DBS(...),0). The user would submit data by changing the flag value (manually, using a button, from a dropdown etc) and refreshing the sheet. Or you can use a macro for that (change flag/recalculate/change flag back).

[stingo]

There are a number of ways to achieve that, the simplest one not even requiring a macro, just a flag cell and DBS formulas (maybe somewhere in a hidden area. Like that: =IF(FlagCell=1,DBS(...),0). The user would submit data by changing the flag value (manually, using a button, from a dropdown etc) and refreshing the sheet. Or you can use a macro for that (change flag/recalculate/change flag back).

well, in this case I still have a formula for every cell in my area, I want to avoid it.

[David Usherwood]

You can write a macro to work through the data and send it to TM1, on the lines of:

Code: Select all

for each c in range("Send Area")
if c.value <> 0 then
  application.run("DBSW",c.value, <cube>, <dimension refs>)
  endif
  next c

_However_
This approach will send the data to TM1 cell by cell, which will be slower than using DBRWs directly. If you don't have a lot of data the speed loss might not be too painful.
IBM suggest using the VIEW function to ensure the data is handled in one lump. This certainly works for retrieval - I can't recall (and haven't tested) what they said about using it for update. if you do, you can put conditional logic on the View function to ensure it doesn't send when you don't want it.

[qml]

You can also consider creating a TI Process that would be called from your VBA for every data line (or column or cell etc), take the data you want to write to the cube (and all dimension elements too) passed as parameters and the actual writing to the cube can be done by the TI. I have employed this previously with good results (first of all, it's actually a very fast solution). This solution also means that your users do not have to have WRITE access to the cube itself, they just need READ to the TI.