Consulting

Results 1 to 7 of 7

Thread: Powerpivot and Delay

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    Powerpivot and Delay

    This does not pertain specifically to VBA, but as an FYI:

    I write a lot of scripts to automate Excel, but these are executed outside of Excel as macro code is not permitted in the .xls(x) files.

    a typical script begins:

    oXL = CreateObject("Excel.Application")
    oXL.Visible = true
    which now often fail with the Powerpivot and SQL DataMining add-ins.

    adding

    While ! oXL.ready
      ...a time delay
    EndWhile
    the problem is resolved.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Have you also tried OnTime?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by xld
    Have you also tried OnTime?
    I was assuming that was an event and wanted to stay away from early event processing when working outside the file... Are you referring to using

    Application.OnTime (or in my case oXL.Ontime)

    Show me da' code

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    What is failing exactly? I'm assuming this is a VBS file of sorts? And if so, why would a company/system/admin not allow macros in a workbook but allow a script file to run?? I would certainly think it would be the other way around.

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by Zack Barresse
    What is failing exactly? I'm assuming this is a VBS file of sorts?
    What is failing: can receive an unknown COM error in previous scripts (compiled Winbatch or Powershell) that automate Excel. Occurs when Powerpivot and Data Mining add-ins are installed and active which takes Excel more time to fully load.

    Again, not a Powerpivot issue but more a timing issue starting Excel and setting properties. Issue was resolved by use of the .ready property, so the post was more an FYI.

  6. #6
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Quote Originally Posted by stanl View Post
    What is failing: can receive an unknown COM error in previous scripts (compiled Winbatch or Powershell) that automate Excel. Occurs when Powerpivot and Data Mining add-ins are installed and active which takes Excel more time to fully load.

    Again, not a Powerpivot issue but more a timing issue starting Excel and setting properties. Issue was resolved by use of the .ready property, so the post was more an FYI.
    In the new office there was this COM add-in which I didn't need but was installed as default. It would take Excel ages to start. Disabling improved start up time considerably.
    Quote Originally Posted by Zack
    What is failing exactly? I'm assuming this is a VBS file of sorts? And if so, why would a company/system/admin not allow macros in a workbook but allow a script file to run?? I would certainly think it would be the other way around.
    It could be that the file is not at a trusted location [network drive] and macro security is "disable with notification".
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    I'm surprised this is an issue since automating Excel should load it without any of its startup files/add-ins loaded unless you specifically enable them.
    Be as you wish to seem

Posting Permissions

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