+ Reply to Thread
Results 1 to 7 of 7

Execute code after OLAP connection has refreshed

  1. #1
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Execute code after OLAP connection has refreshed

    Hi there, I have created a set of reports containing cube functions (reports are connected to OLAP). What I need is for my code to continue executing as soon as the OLAP connection refresh has finished. Now, the code carries on whilst the refresh is still happening.. This results in reports being saved with "VALUE" errors in it. I did a research into this to find: 1) BackgroundQuery property always FALSE for OLAP (therefore of no use) 2) wait command stops also the Refresh of data. I was amazed by the flexibility of cube functions for reporting and converted all the pivot tables (OLAP) into functions. I am very dissapointed now that I cannot use VBA code to refresh the data. Any help would be very much appreciated. I can post the code if needed. Thank you. Vojta

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Execute code after OLAP connection has refreshed

    You may try to add:
    Please Login or Register  to view this content.
    to wait until connection is refreshed.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Execute code after OLAP connection has refreshed

    Hi Izandol, thank you very much for your response. I tried to implement in my code after .RefreshAll command (shown below). However, this would just not make the code wait until the formulas has refreshed. Again, "VALUE" errors appear by the time I reopen the files. I am quite new to VBA programming so perhaps I am just not seeing something obvious. Thank you again for your help on this. Vojta

    Sub Refresh_TEST()
    Dim TargetFolder As String
    Const FILE_EXT As String = "xls*"
    Dim FileName As String
    Dim wbTemp As Workbook, wsTemp As Worksheet
    TargetFolder = "\\grafton.local\grceedfs1\GR-CEE_SharedData\REPORTING FY14-15\TEST\"

    FileName = Dir$(TargetFolder & "*." & FILE_EXT)

    Do While Len(FileName) > 0
    Set wbTemp = Workbooks.Open(TargetFolder & FileName)

    With wbTemp

    For Each wsTemp In ActiveWorkbook.Worksheets
    wsTemp.Unprotect Password:="usk"
    Next wsTemp

    .RefreshAll
    Application.CalculateUntilAsyncQueriesDone

    For Each wsTemp In ActiveWorkbook.Worksheets
    wsTemp.Protect Password:="usk", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True
    Next wsTemp
    .Save
    .Close
    End With
    FileName = Dir$
    Loop
    End Sub

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Execute code after OLAP connection has refreshed

    I do not know. I have used this before without issue. I have also seen other threads where it has been the solution but I cannot say why it will not work for you.

  5. #5
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Execute code after OLAP connection has refreshed

    Thank you for your effort anyway.

  6. #6
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Execute code after OLAP connection has refreshed

    Perhaps you may try the Sleep API routine? It should not pause the query while the code waits. Also you may try a loop with DoEvents:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-13-2014
    Posts
    4

    Re: Execute code after OLAP connection has refreshed

    Hi, tried both, however, none of these seems to work. Searched the internet back and forth and nobody seems to have answer for that. The fact that you cannot refresh reports with VBA is a huge setback to using cubefunctions in reporting. I am now equally disapointed with cubefunctions as I was amazed in the beginning. Thank you for your help anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 05-14-2018, 09:16 AM
  2. VBA code to execute for next row
    By GUERCHARD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2013, 10:05 AM
  3. [SOLVED] Code won't execute
    By ccpsc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-03-2012, 06:28 PM
  4. Replies: 1
    Last Post: 05-09-2006, 02:35 PM
  5. Replies: 0
    Last Post: 05-19-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1