none
Any reason this code would fail in Excel 2007? RRS feed

  • Question

  • Background:

    This was written using Excel 2013. It is run via an automated process on a server running Excel 2007. When I run the agent manually, it works fine. When the server runs it, the process that actually runs the code locks up and I have to reboot the server to unlock the scheduled agent process.

    The server process isn't throwing any errors, the thread just locks up.

    I don't have ready access to an Excel 2007 install but am trying to find someone to run the code.

    In the meantime, I thought I'd poke here and see if anyone sees anything that looks out of sorts.

    How the system works.

    • The scheduled server process opens an Excel template (xlt) that is pre-loaded with an empty pivot table and corresponding pivot chart.
    • The agent then pushed data from an external source into the table.
    • After the data is in the table, the agent runs a macro embedded in the Excel template.
    • The macro code refreshes a pivot table and updates the pivot pie chart linked to table.

    If you need more detail, please let me know.

    Thanks for your thoughts.

    Sub updatePivot()

    '
        Sheets("PivotTable").Select
        Range("A3").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
       
        Sheets("PieChart").Select
        ActiveChart.ChartArea.Select
        ActiveChart.ChartTitle.Select
        Selection.Caption = "Time to Complete or Close CAPA"
        ActiveChart.SeriesCollection(1).Select
        ActiveChart.SeriesCollection(1).ApplyDataLabels
        ActiveChart.SeriesCollection(1).DataLabels.Select
        Selection.ShowPercentage = True
        Selection.ShowValue = False
        Selection.Position = xlLabelPositionOutsideEnd
       
        ActiveChart.ChartArea.Select
        ActiveChart.SeriesCollection(1).DataLabels.Select
        Selection.Format.TextFrame2.TextRange.Font.Size = 14

    End Sub

    Friday, April 3, 2015 11:07 AM

All replies

  • Maybe you need to give the server process time to finish opening up the workbook before it proceeds with the rest of the code.  To wait, let's say 15 seconds, try adding the following right after opening the workbook...

    Dim dtEndTime As Date
    
    dtEndTime = Time + TimeValue("00:00:15") '15 seconds delay
    
    While Time < dtEndTime
        DoEvents
    Wend

    By the way, your code could be re-written as follows...

    Sub updatePivot()
    
        ThisWorkbook.Sheets("PivotTable").PivotTables("PivotTable1").PivotCache.Refresh
        
        With ThisWorkbook.Sheets("PieChart")
            .ChartTitle.Caption = "Time to Complete or Close CAPA"
            With .SeriesCollection(1)
                .ApplyDataLabels
                With .DataLabels
                    .ShowPercentage = True
                    .ShowValue = False
                    .Position = xlLabelPositionOutsideEnd
                    .Format.TextFrame2.TextRange.Font.Size = 14
                End With
            End With
            .Activate
        End With
    
    End Sub

    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    Friday, April 3, 2015 2:09 PM
  • Domenic,

    Thank you for your reply. I'm not sure the delay will help. The way the system work is that the agent opens an Excel file, populates it with data, then runs the macro embedded in the template. I have many other reports created by the same system that update pivot tables/charts. Most charts are bar and the two problem reports are the only ones with pie charts. Is there anything unique about pie charts that would require a bit of 'rest time'?

    I also had someone with Excel 2007/Windows 7 run these reports manually (so all the code executed locally rather than on the server) and the report worked fine. This adds to my confusion...

    Thanks also for the code simplification. I generally record a macro when I'm building the templates and use them as they're created by the recorder so they tend to be a bit more verbose than required. I tend to work from the perspective 'Quick + Dirty' > 'Slow + Elegant'  ;-)

    Bottom line, though, you see nothing here that would fail in Excel 2007?

    I am also in contact with the company that makes the reporting software to see if they have any idea why the server agent thread is locking up. My guess now is that I did something to the report concurrent with my 2013 install and it's not related to the version at all. I have a large gun and use it on my foot quite often...

    Thanks again for the reply and suggestions. I very much appreciate the help.

    Doug

    Friday, April 3, 2015 2:34 PM
  • Hello Dogubob,

    This was written using Excel 2013. It is run via an automated process on a server running Excel 2007. When I run the agent manually, it works fine. When the server runs it, the process that actually runs the code locks up and I have to reboot the server to unlock the scheduled agent process.

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.

    You may consider using the Open XML SDK if you deal only with open XML documents (.xslx). If not, you may try to search commercial components designed for the server-side execution. 

    Friday, April 3, 2015 3:10 PM
  • Eugene,

    Yep, totally know about the issue. The server has permissions modified to allow for unattended operation and has been doing so for several years across 2 versions of the server (it's a Domino box), several versions of the reporting software (Integra4Notes), 2 versions of OS, and at least 2 versions of Office. Something about the update to 2013 seems to be causing a problem OR I mucked up something minor that I'm just not seeing.

    As I said, I have a call into Integra to see if they can see anything I've managed to bork in these two specific reports.

    And now you have me thinking. When we moved from a physical box to a VM, we updated the OS. I'm not totally sure I went back and did the rights management thing...no clue why tons of bar charts would continue to work while two pie charts would die but, hey, maybe something about the fact that I touched the reports caused something to change and the server sees it as a 'no rights for you' situation.

    Thanks! Off to find the article and see what the server looks like. MUCH appreciated.

    Friday, April 3, 2015 4:00 PM
  • When we moved from a physical box to a VM

    I'd  recommend testing the software in the real fields (no VMs). It may introduce issues as well.

    Friday, April 3, 2015 4:04 PM
  • EXCELLENT suggestion.

    To quote a prior president 'not gonna happen...nope, not gonna happen'.

    I have one server and it's a VM so I'm kind of stuck.

    Thanks for the conversation. I find that this type of give-and-take can result in 'ah HA' moments that resolve the issue; like the 'oh crap, maybe I didn't update the server component services when we went VM' thing. Well off to see if that's working.

    <edit>

    So my DCOM setting all appear to be correct. The server 'user name' has full rights as does the user 'Interactive'.  Either I'm missing something subtle here or it's actually OK and something else is going on.

    Time to walk away for a bit and let this one stew. The server is stable. I can run the reports manually if required (and they only run once a month anyway so no big deal). I need to hear back from Integra and/or put more coffee on-board...

    Thanks again for the help.

    • Edited by Dogubob Friday, April 3, 2015 4:25 PM Server info available
    Friday, April 3, 2015 4:13 PM
  • When I run your code in Excel 2007, the following line generates an error...

    Selection.Format.TextFrame2.TextRange.Font.Size = 14
    If you delete that line, does the code run successfully?

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, April 3, 2015 9:28 PM
  • Domenic,

    Could you be more specific? What error do you get?

    Anyway, it looks like there is no Format property defined for the Selection object in Excel 2007.

    Saturday, April 4, 2015 5:51 AM
  • I get the following error...

    Rune-time error '-2147417848 (80010108)':
    
    Method of 'Size' of object 'Font2' failed

    The same error occurs with the Format property of the DataLabels object.  Actually, according to 'Excel Help', the Format property of the DataLabels object is read-only.

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    Saturday, April 4, 2015 1:36 PM
  • You are right, the Format property is read-only. 

    Also I'd recommend declaring all objects explicitly with fully-qualified names. See VBA code inconsistently crashes Excel (run-time error 80010108) for more information. 

    Saturday, April 4, 2015 9:28 PM
  • Interesting about the font thing.

    If I let the server run the code via a scheduled process, there are no error messages (I know, bad idea to automate Excel at the server level but it's what we do...livin' on the edge).

    If I run the code on my 2013 machine, it works fine. No errors.

    If A co-worker with 2007 runs the code, it also works fine. No errors.

    I'll wait to hear back from the report writer software vendor to see what they say, then I'll start playing with removing code. The problem I have with debugging is that I only have my live server available to me and when it hangs, I have to reboot at the OS level to un-hang the agent thread. For some reason, my users don't like server reboots...

    Thank you all for your inputs and help. I'll keep you posted as I work through this.

    Doug

    Monday, April 6, 2015 10:35 AM