Increase rows when refresh in a Workbook

[Former Member]

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

[Former Member · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[Former Member]

Jose

I had also faced the same problem.

I want to add the daily values and display at the end in the result line.

When the month is changed and if it has 31 days then the result line will disappear.

So leave one line at the end and create the formula with inclusion of that line and Save the workbook.

This saved workbook use as a permanent workbook template, This when you refresh it will not delete the result line.

Regards

M.A