Skip to Content
0

VBA Refresh Macro W/ Custom Excel Functions

May 17, 2017 at 08:33 PM

278

avatar image

Hi Experts,

I am trying to create a refresh macro which performs the following steps:

1. Remove Excel Subtotals

2. Refresh Work Sheet

3. Add subtotal

I was able to write the refresh piece but am having difficulty with the syntax for steps #1 and 3 - I have very little experience with VBA. Any help would be much appreciated.

bpc | vba | macro
10 |10000 characters needed characters left characters exceeded

P.S. The primary tag looks incorrect - better to use:

EPM Add-In for Excel

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Vadim Kalinin May 18, 2017 at 06:06 AM
0

Why not to Google "vba add subtotals"

A lot of samples!

Range.Subtotal Method (Excel)

https://msdn.microsoft.com/ru-ru/library/office/ff838166.aspx

To remove:

RemoveSubtotal

But why not to use EPM report with required subtotals?

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Vadim,

It's not the syntax of removing/adding subtotals that's my issue. I can get that just from recording those simple steps in excel, but how do I combine that with the code for the BPC refresh so that my macro performs all steps?

This is what I have in Module1 of my VBA:

Dim EPMexample As New FPMXLClient.EPMAddInAutomation

Sub Button1_Click()

EPMexample.RefreshActiveSheet

End Sub

Is it possible to add the "remove excel subtotal function" command before the refresh, and "add excel subtotal function" after the refresh?

0

Here is the revised code, in bold I am getting an error "We couldn't do this for the selected range of cells. Select a single cell within a range of data and then try it again":

Sub Button1_Click()
Selection.RemoveSubtotal
EPMexample.RefreshActiveSheet

ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("D3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

0
The code mentioned in the above post is working fine if I leave the context for TIME unchanged. However, If I change TIME in the context and then run the macro, I get the error as mentioned above. Please advise.
0

Sorry, but the code mentioned in the above post is incorrect in general.

If you want some help - please show report screenshot (with all dimensions shown) and explain the requirements in details:

What do you want to sort

What subtotals you want to add.

0
Vadim Kalinin May 18, 2017 at 01:47 PM
0

Hi Kevin,

Definitely you need to get some VBA training... Try to create yourself some procedures that will do something with Excel sheet (even without EPM API involved).

"I can get that just from recording those simple steps in excel," - bad idea to use generated (recorded) code, try to write it yourself!

And for sure you can add code before and after "EPMexample.RefreshActiveSheet"

For example:

Sub Button1_Click()
MsgBox "Before Refresh" ' Any code here
EPMexample.RefreshActiveSheet
MsgBox "After Refresh" ' Any code here
End Sub
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin May 18, 2017 at 02:12 PM
0

Easy!

Selection is the range object containing the selected cells.

And at the beginning you are assuming that some range is selected by user... strange...

Then after refresh, the initial selection is replaced by single cell selection - you get the error!

After refresh you can get the range you want to sort and add subtotals using:

GetDataTopLeftCell

GetDataBottomRightCell

Please read API help...

Show 4 Share
10 |10000 characters needed characters left characters exceeded

P.S. Why do you need to sort using VBA??? You can sort using EPM settings....

0

My report has company and trading partner in the rows. A third row concatenates the company ID and inter ID - for example "1001 and 2001". I then need to sort on this local member to get the groupings I want for my subtotals. EPM sorting does not offer this - does it?

0

Sorry, but not clear! Better to provide a screenshot!

If you concatenate company ID and Intco ID then each line will have unique string in the local member column.

What subtotal you are talking about???

0

Ups, looks like you don't need an answer!

0