CODEWITHSUNDEEP
c#.netexcel
Vikram Singh Saini
Vikram Singh Saini

Reputation: 1889

Links in excel are not updating automatically C#

What I had tried so far

I am developing Window based application based on .NET Framework 4.0. The application do two task -

  1. Open excel file so that it can get update from DDE Server (MCX) using Microsoft.Office.Interop.Excel;.
  2. Read data in excel file into DataGridView using RSS Bus Ado.Net Provider.

Normal Scenario

I had made Excel file shared so that it can be edited and read at same time.

Now when I run DDE Server, it sends some data to Excel. And Excel file show updated data with break of 2 seconds(default). That's fine.

Programming Scenario

When same excel file is opened from program, it doesn't show updated data. Below is the code snippet used -

xlApp = new Application { AutomationSecurity =    MsoAutomationSecurity.msoAutomationSecurityLow };
        _xlApp.CalculateFull();

        _xlBook = _xlApp.Workbooks.Open(ExcelFilePath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        //Add an event handler for the WorkbookBeforeClose Event of the Application object.
        _eventBeforeBookClose = BeforeBookClose;
        _xlApp.WorkbookBeforeClose += _eventBeforeBookClose;

        //Add an event handler for the Change event of both worksheet objects.
        _eventCellsChange = CellsChange;

        //Add an event handler for the Calculate event of both worksheet objects.
        _eventCellsCalculate = CellsCalculated;

        _xlSheet1 = (Worksheet)_xlBook.Worksheets.Item[1];_xlSheet1.Change += _eventCellsChange;
        ((DocEvents_Event)_xlSheet1).Calculate += _eventCellsCalculate;

        //Make Excel visible and give the user control.
        _xlApp.Visible = true;
        _xlApp.UserControl = true;

Problem

Is there any setting in C# that will allow links in excel file (opened from program) to be updated automatically?

Upvotes: 0

Views: 3492

Answers (2)

whale70
whale70

Reputation: 375

There is a simpler solution, the Workbooks Open method provides a parameter for just that reason:

_xlBook = _xlApp.Workbooks.Open(ExcelFilePath, UpdateLinks: 3);

See WorkBooks.Open documentation for the exact specification of the UpdateLinks parameter. The value '3' will always update all links.

Upvotes: 1

Vikram Singh Saini
Vikram Singh Saini

Reputation: 1889

To update links in excel automatically, I added following function that need to be called by timer tick event.

Code Snippet - 

public static void UpdateLinkValues()
    {
        var oleLinks = (object)_xlBook.LinkSources(XlLink.xlOLELinks);
        var oleLinkArray = (Array)oleLinks;

        for (var i = 1; i <= oleLinkArray.Length; i++)
        {
            _xlBook.UpdateLink(oleLinkArray.GetValue(i),XlLinkType.xlLinkTypeOLELinks);
        }
    }

This function retrieve embedded OLE links. And then call UpdateLink() to update values.

Upvotes: 0

Related Questions