CODEWITHSUNDEEP
c#excelexcel-formulaexcel-interop
Vajda
Vajda

Reputation: 1794

Detect dependent cells from another sheet

This is simplified example that illustrates my problem. I have in my sheet1 in cell A1 formula:

sheet1!A1 cell has formula '=sheet2!C1'
sheet2!B1 cell has formula '=C1'

And when cell C1 from sheet2 is changed, I want to detect all dependent cells, that means cell B1 from sheet2 and cell A1 from sheet1. But range.Dependents only returns cells from Active sheet. and I get only cell B1. In Range interop API documantation is stated that dependents will return only ranges from active sheet. Does anyone knows way how to detect all dependencies from all sheets, some other API call, or some other approach?

Upvotes: 1

Views: 2731

Answers (1)

Andy Brown
Andy Brown

Reputation: 19161

This is going to be a long code sample, but basically you just need to follow the arrows (I'm assuming you are using C#, as you haven't specified a language that you are using for the interop).

The output from the sample below is (there are two cells on Sheet1 to demonstrate how you walk all the remote references):

    Sheet1!A1
    Sheet1!A2
    Sheet2!B1

Health warning. This sample doesn't do any interop cleanup or app closing - this is for demonstration only

Edit Use Application.ScreenUpdating to prevent screen flicker during the tracing function.

using xl = Microsoft.Office.Interop.Excel;
// use in a console app
class Program
{
    static void Main(string[] args)
    {
        xl.Application app = new xl.Application();
        app.Visible = true;
        xl.Workbook wb = app.Workbooks.Add();
        xl.Worksheet worksheet1 = wb.Sheets[1];
        xl.Worksheet worksheet2 = wb.Sheets[2];
        xl.Range rngS1A1 = worksheet1.Range["A1"];
        xl.Range rngS1A2 = worksheet1.Range["A2"];
        xl.Range rngS2B1 = worksheet2.Range["B1"];
        xl.Range rngS2C1 = worksheet2.Range["C1"];

        rngS1A1.Formula = @"=sheet2!C1";
        rngS1A2.Formula = @"=sheet2!C1";
        ((xl._Worksheet)worksheet2).Activate();
        rngS2B1.Formula = @"=C1";

        List<string> dependentAddresses = ListDependents(rngS2C1);

        foreach (string address in dependentAddresses)
        {
            Console.WriteLine(address);
        }
        Console.WriteLine("done, press enter to exit");
        Console.ReadLine();
    }

    private static List<string> ListDependents(xl.Range sourceRange)
    {
        sourceRange.ShowDependents(false);
        string sourceAddress = sourceRange.Worksheet.Name + "!" + sourceRange.Address;
        int arrowNumber = 1;
        List<string> dependentAddresses = new List<string>();
        do
        {
            string targetAddress = null;
            int linkNumber = 1;
            do
            {
                try
                {
                    xl.Range target = sourceRange.NavigateArrow(TowardPrecedent: false, ArrowNumber: arrowNumber, LinkNumber: linkNumber++);
                    targetAddress = target.Worksheet.Name + "!" + target.Address;

                    if (sourceAddress == targetAddress) break;

                    dependentAddresses.Add(targetAddress);
                }
                catch (COMException cex)
                {
                    if (cex.Message == "NavigateArrow method of Range class failed")
                    {
                        break;
                    }
                    throw;
                } 
            } while (true);
            if (sourceAddress == targetAddress) break;
            arrowNumber++;
        } while (true);

        sourceRange.Worksheet.ClearArrows();
        return dependentAddresses;
    }
}

Upvotes: 3

Related Questions