none
VSTO for Office 2003 – Cached objects not populating Excel workbook remotely RRS feed

  • Question

  • I am trying to insert data into a datasheet that is cached in a Mircrosoft Excel workbook using the ServerDocument class. I have a ListObject that is bound to a typed dataset in the workbook.  I’m adding  the dataset in the workbook to the data cache and then inserting data into the cached DataSet through a console application.

     

    The code runs fine on my development machine.  On the server,  the code runs without error, but does not populate the workbook with data.  I confirmed that the DataSet does have records by using a DataSet.WriteXml command.

     

    I’ve checked configuration and permissions settings on the server and no errors are being generated.

     

    My development machine is running Windows XP with Visual Studio 2008 and Excel 2003. 

    The server is a Windows Server 2003 box.  It has Office 2003 installed along with the primary interop assemblies, The .NET Framework and the VSTO runtime.

    A code sample is attached below:

    SafariReportDemo.SurveillanceDataSet lobExposureDataSet = new SafariReportDemo.SurveillanceDataSet();

               SafariReportDemo.SurveillanceDataSetTableAdapters.CP_LOB_Exposure_TotalsTableAdapter lobExposureTableAdapter = new SafariReportDemo.SurveillanceDataSetTableAdapters.CP_LOB_Exposure_TotalsTableAdapter();

               

    string workbookPath = (@"C:\Projects\SafariReport\bin\Debug\SafariReport.xls");

    ServerDocument sd = null;

     

                try

                {

                    lobExposureTableAdapter.Fill(lobExposureDataSet.CP_LOB_Exposure_Totals);

     

                    sd = new ServerDocument(workbookPath);

                    CachedDataHostItem dataHostItem1 = sd.CachedData.HostItems["SafariReport.Sheet1"];

                    CachedDataItem dataItem1 = dataHostItem1.CachedData["surveillanceDataSet"];

     

                    if (dataItem1 != null)

                    {

                        dataItem1.SerializeDataInstance(lobExposureDataSet);

                        sd.Save();

                    }

                }

                finally

                {

                    if (sd != null)

                    {

                        sd.Close();

                    }

                }

    Does anyone have any suggestions or thoughts.

    Thursday, July 14, 2011 6:53 PM

All replies

  • Hello Hulas5150,

    You haven’t said how you know the data isn’t saved within the cache of the target workbook. If you use Excel to open the workbook from the server and do not see the changed data the data didn’t get saved – probably because it hasn’t gone back over the network.  If you were to do something similar using the Excel object model within an instance of Excel the workbook would be saved back by Excel.  With a serverdocument the client needs API calls to the operating system to write the file back to the server.

    There are a couple of things you can do to determine whether this is the problem. You can use NetMon to log the network traffic at the time your code issues the SerializeDataInstance followed by the sd.Save(), and/or you can inspect the ‘last saved’ property of the file.  You could use an additional data element in the RunTimeStorage control (the cache) where you store the system date.time when your code adds data to the cache, and also updates the date.time. At that time show the current date.time, and the original date.time in a messagebox. Run your client application a second time a compare the strings in the messagebox to those in the earlier one.

    You will probably need to use System.IO methods to be assured the workbook gets back to the server.

    Please let us know whether this information helps resolve your problem.

    Regards,
    Chris Jensen
    Senior Technical Support Lead

    Tuesday, July 19, 2011 4:51 PM
    Moderator
  • Hi Chris,

    Thanks for responding.  I did confirm that the file is being saved, as the date is being updated...  But, I don't understand why the workbook is not populated with data when I open it for viewing on the server (the workbook is empty). 

    We have Excel 2003 installed on the server, so I would think that I should be able to open the workbook from the install directory and view the data.

    I will try some of you other suggestions in the interim to assist with debugging....

    Thanks.

     

    Tuesday, July 19, 2011 5:58 PM
  • Hi Hula5150,

    If you pull the file back to the client from the server and you do see that the data is displayed in the intended range on the worksheet it appears you’re missing the installation of your customization on the server. The VSTO application controls the Runtime Storage Control (RTC) and other VSTO components to populate the specified range of the worksheet. No matter which system the file resides on, the RTC control should be bound to the first worksheet of the workbook where the data is cached. That was done when you ran your VSTO application, and attached the customization.

    Please see this blog for an idea of how to resolve the problem.:
    VSTO Customization Will Not Load Without Visual Studio 2008
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/58da0637-2b81-4ddd-82cb-3da7c6d9c2de

    An index to many reports, with or without solutions, is in Microsoft VKB. Select the bing database and use the query argument   “Runtime storage control” and manifest  ’ .

    Please let us know if this information helps solve your problem.

    Regards,
    Chris Jensen
    Senior Technical Support Lead

    Wednesday, July 20, 2011 1:56 PM
    Moderator
  • Hi Chris,

     

    Resolved the issue, it was because Excel Pro 2003 was not installed on the server....  The Standard Edition was previously installed...

    However, I did run into another issue that I hope you can be of some assistance.  I would like to be able to email a copy of the workbook on the server to our end users without the managed extensions, but with the cached data.  There are several options noted here - http://msdn.microsoft.com/en-us/library/8fza57cs%28VS.80%29.aspx. Clearing the app manifest seems to be the likely solution, but returns an error  of "The supplied application manifest is not valid" noted in this thread  - http://social.msdn.microsoft.com/Forums/eu/vsto/thread/a0cdb75a-b143-47f4-909d-5af529ec704f. (There also is some mention in this thread of the properties Assembly Name and Location, which I am unable to locate.  Can you shed some light on where I can find them?  I'm not seeing them in the App manifest.)

    What can you recommend to resolve this issue or a possible workaround?

    Thanks.

    Wednesday, July 20, 2011 4:50 PM
  •  

    Hi Hula5150,

    Congratulations in solving your problem by upgrading the Excel product on your server.  Good troubleshooting.

    I’ve not tried to send a ServerDocument by email, without managed extensions, but with the cached data (which is cached in the RuntimeStorageControl (RTC) – don’t remove that) – so I haven’t much guidance.  You can research the entire Forum thread you mentioned in your reply or go to the blogosphere or by using Microsoft bing to search on ‘serverdocument and email’.

    The customer in the issue at http://social.msdn.microsoft.com/Forums/eu/vsto/thread/a0cdb75a-b143-47f4-909d-5af529ec704f

    in the post on Friday, October 19, 2007 9:07 PM, Steve 5656565656, said he found the _AssemblyName and _Assembly location in the File->Properties and the custom tab.

    Try reading and applying a solution such as one described in the content:
    Removing Customization Code Before E-Mailing a Document (Harry ... “
    http://blogs.msdn.com/b/vsto/archive/2008/07/18/removing-customization-code-before-e-mailing-a-document.aspx

    Please let us know whether that helps you succeed e-mailing the Excel Workbook file.

    Regards,
    Chris Jensen
    Senior Technical Support Lead

    Thursday, July 21, 2011 9:58 PM
    Moderator