VBA Print sequence with CUBEMEMBER / CUBEVALUE formula's

[Martin_B]

Good evening,

I ham having an issue with an identical report that I have to run for 300+ locations, the report itself works perfectly but I don't want to have to save it to PDF manually. I created a VBA script to do it automatically but I am having refresh issues which are causing my script to fail.

Has anyone encountered this before and hopefully how did you solve it?

Martin

 

[scottsen]

I have -- I remember it was a bit wonky to get it working to correctly detect the refresh is complete. But... can you be more specific on what issues you are seeing?

 

[Martin_B]

I have -- I remember it was a bit wonky to get it working to correctly detect the refresh is complete. But... can you be more specific on what issues you are seeing?

The screen appears to freeze with the CUBEVALUE cells showing zeros and the CUBEMEMBER cells 'GETTING_DATA#', then the script fails. Tried every refresh code I could find and also put a 2 second wait clause in (I know not the best idea).

Martin

 

[scottsen]

Ugh. So, I looked back at my code. It was not pretty.

a) I used a lot of OnTime like: Application.OnTime Now + TimeValue("00:00:01"), "PostSlice" because... well, nothing else worked.
b) I used this whack function to know if I was "done"... again, because nothing else worked

Function SlicingIsComplete()
    Dim Retval
    Dim s As Worksheet
    
    For Each s In srcWorkbook.Sheets
        Set Retval = s.Cells.Find("#GETTING_DATA", LookIn:=xlValues, LookAt:=xlWhole)
        If Not Retval Is Nothing Then
            SlicingIsComplete = False
            Exit Function
        End If
    Next s
    
    SlicingIsComplete = True
End Function

 

[Martin_B]

Ugh. So, I looked back at my code. It was not pretty.

a) I used a lot of OnTime like: Application.OnTime Now + TimeValue("00:00:01"), "PostSlice" because... well, nothing else worked.
b) I used this whack function to know if I was "done"... again, because nothing else worked

Function SlicingIsComplete()
    Dim Retval
    Dim s As Worksheet
    
    For Each s In srcWorkbook.Sheets
        Set Retval = s.Cells.Find("#GETTING_DATA", LookIn:=xlValues, LookAt:=xlWhole)
        If Not Retval Is Nothing Then
            SlicingIsComplete = False
            Exit Function
        End If
    Next s
    
    SlicingIsComplete = True
End Function

Thanks Scottsen,

This has definitely helped as the data is refreshing now, but it appears there is some of your code missing. Because now the cells that were causing an issue are updating but the actual data (cubevalue cells) are not.

I'll keep on banging my head against it, but at least I can save my files relatively easily.

Martin

 

[scottsen]

Application.OnTime Now + TimeValue("00:00:01"), "PostSlice"

Well, this was saying "in 1 second, call the function PostSlice. Which, you would need to define.

In my case I was doing a "Slice the data, create pdf, rinse, repeat". Not sure if you have a slicer or whatever, but that's the only weirdness I see in my code.

 

[Martin_B]

Application.OnTime Now + TimeValue("00:00:01"), "PostSlice"

Well, this was saying "in 1 second, call the function PostSlice. Which, you would need to define.

In my case I was doing a "Slice the data, create pdf, rinse, repeat". Not sure if you have a slicer or whatever, but that's the only weirdness I see in my code.

Thanks Scottsen, I appreciate you help.