cancel
Showing results for 
Search instead for 
Did you mean: 

Increase rows when refresh in a Workbook

Former Member
0 Kudos

Hello!

I hope you can help me.

I have a workbook with severals queries. Also I have 8 or 9 formulas into my workbook. When I refresh it, if I choose other month, some rows increase or decrease and so the formulas don´t work and workbook breaks.

I´ve think insert any code made with visual Basic.

Thanks and sorry for my english.

Edited by: José Luis López on Jun 24, 2008 9:47 AM

View Entire Topic
Former Member
0 Kudos

So you have a workbook in which you have added Excel-formulas which is extracting data out of the query data, right?

You need to be careful doing this with a variable number of rows. If you can use a query with a (fixed) structure in both rows and columns and no drilldown possibilities, then it's quite easy to use the result in further calculations in Excel.

If you need to keep the variable number of rows, you should do the calculations in VBA instead of in Excel-formulas. This is quite simple if you just need to loop through all the rows. If you want the macro to work with drilldown, down and across, as well, the code will be very complex. This you should avoid.

To write a macro looping through all rows (not working with drilldowns), the macro needs to know what column to read and the first data row. You also need to pick a column which can be used to verify if you have passed the last row of data or not. The simplest option would be to use a characteristic column in which there are always data in every row.

Regards,

Christoffer

Former Member
0 Kudos

Hello, Christoffer.

Exactly!

You describe perfectly my problem. My queries are with variable number of rows. Do I loop the Excel and take the values to after it fill in the formula with visual basic?

Regards.

Former Member
0 Kudos

Hello,

Assuming you want to accumulate the data, the pseudo code is as follows:

i = first line of data
c = column of key figure
r = column to check if end of data has been reached
a = 0
while (cell(i,r).value<>"")
    a = a + cell(i,c).value
wend

Then you can do whatever you want with thre result: use it in further calculations in VBA or just put it into a cell (which may be used in Excel formulas). If you are not familiar with the VBA syntax, you can read about it in the Microsoft Visual Basic Help included in Excel.

You can put this VBA code into the empty SAPBEXonrefresh function if you want it to be automatically run after each refresh. Here you can also get the resultarea directly, instead of using a reference column as I have done. You can get some information in the library: [http://help.sap.com/saphelp_nw70/helpdata/en/e3/e60138fede083de10000009b38f8cf/frameset.htm|http://help.sap.com/saphelp_nw70/helpdata/en/e3/e60138fede083de10000009b38f8cf/frameset.htm] (under Exits). I've never used this as I have only created macros which have been executed by the user.

Regards,

Christoffer