Cube functions in macro updated PowerPivot workbook

[BojeErvenius]

Hi,

I have a PowerPivot workbook stored on a shared location. I am updating this workbook, once every night, through task scheduler which runs a vb scipt to run a macro in the workbook. The macro is simply ActiveWorkbook.Model.Refresh. Everything works just fine, charts, pivottables etc are all updated. But one thing is not…

I have some cube functions and the cells with this function are not refreshed. The cells look empty and the user of the workbook has to choose one cell, enter the formula bar and then click enter. Then the data is loaded into the cells. What am I doing wrong? This is not a problem when I update the workbook manually, or run the macro by myself.

/Boje Ervenius

 

[Matt Allington]

If you only have 1 workbook, you could try Power Update. Power Update Software -

it is free for 1 workbook

alternatively you could try changing your macro to edit the cell with the cube value.

 

[BojeErvenius]

If you only have 1 workbook, you could try Power Update. Power Update Software -

it is free for 1 workbook

alternatively you could try changing your macro to edit the cell with the cube value.

Hi,

Thank you for replying.

I have many workbooks so power update is nog an option at this moment.

I have tried to put some vba code in the macro to edit a cube value cell. Unfortunately this does not help. I think it has something to do with the fact that I am doing the update through task scheduler. When the workbook is opened the user has to do something (edit function, use auto filter etc.) to get the workbook to load data.

I am not a programmer, I have just copied the script from the web.

Bat-file
cscript J:\Script.vbs "J:\FILE.xlsm"

vbs-file
Dim args, objExcel
Set args = WScript.Arguments
Set objExcel = Createobject("Excel.Application")
objExcel.Workbooks.Open args(0)
objExcel.Visible = True
objExcel.Run "Update"
objExcel.Activeworkbook.Save
objExcel.Activeworkbook.Close(0)
objExcel.Quit

 

[Matt Allington]

I have experienced what you describe, but I now use power update and I don't have any more problems. It is worth the $500 to me to have this tool, but I guess that is not for everyone. How about you put a macro in the workbook open event? You can do it yourself
1. Turn on the macro recorder (store the macro in the active workbook)
2. Edit one of the cells with a cubevalue
3. turn off the macro recorder
4. go to the VBA editor (Alt F11)
5. navigate to the module with the code. Probably Module1 or Module2
6. copy the code inside the SUB/ENDSUB (you don't need the sub bits)
7. Navigate to the module called "ThisWorkbook"
8. In the drop down, click on "general" and change to "workbook". This should create the sub for AutPen
9. Paste code and save as a macro enabled workbook.

 

[BojeErvenius]

I have experienced what you describe, but I now use power update and I don't have any more problems. It is worth the $500 to me to have this tool, but I guess that is not for everyone. How about you put a macro in the workbook open event? You can do it yourself
1. Turn on the macro recorder (store the macro in the active workbook)
2. Edit one of the cells with a cubevalue
3. turn off the macro recorder
4. go to the VBA editor (Alt F11)
5. navigate to the module with the code. Probably Module1 or Module2
6. copy the code inside the SUB/ENDSUB (you don't need the sub bits)
7. Navigate to the module called "ThisWorkbook"
8. In the drop down, click on "general" and change to "workbook". This should create the sub for AutPen
9. Paste code and save as a macro enabled workbook.

Hi Matt,

Thank you! Adding a macro on opening the workbook solved the problem.

You made me curious of powerupdate so I asked our IT department to install it for me, but it seems like I have to be an administrator to run the program.

/Boje Ervenius

 

[Matt Allington]

Yes, this is a windows requirement due to the way Power Update uses Windows Scheduled tasks. You need administrator rights to be able to schedule tasks. Once the scheduled tasks are set up, you no longer need to be and Admin for them to run, but that may be a problem in some situations - I am well aware of how some IT departments can be a bit 'thingy' about this stuff. So options are:
1. Get IT go give you an exemption (but that may not be possible)
2. Get a second PC set up that you can use as a dedicated refresh PC (with Admin Rights). This works well because it works when you are out of the office too
3. Get temp admin rights and set up a couple of "folder refresh" jobs on your PC. Then they can take away admin rights. You just need to add files to/from the folder as you like and they will get added to the queue