Programmatically Link Excel XLSX to External Workbooks Using C# .NET

August 08 2022

While working with Excel files, one of the most commonly faced situations is to pull data from multiple Excel files into a single file. For example, a Sales department may need to consolidate data from multiple Excel files (including sales data from different regions or over multiple years) into a single Excel file for further study or trend analysis.

Collecting data manually by copying from these workbooks is not a good option because of several reasons, including:

can be a time-consuming exercise when the source workbooks have a multitude of records or worksheets
higher maintenance when changes to original source workbooks take place
introduction of errors, resulting in incorrect analysis

Excel solves this problem by creating an External Reference Formula (also known as External Workbook Link or Cross-Workbook Formula). The formula creates a link between the source and the destination workbooks, thus saving time, reducing errors, and preserving data integrity. In contrast, a manual copy-paste can cause errors and introduce data integrity and accuracy issues. Creating links also eliminates the need to maintain the same data in multiple Excel files.

This Blog teaches developers how to programmatically use the GcExcel API for C# to create an External Workbook Link.

Ready to Create External Workbook Links On Your Own? Download a FREE 30-Day Trial Now!

Use-Case Example

A customer has engaged your team to help with aggregating data from multiple systems that the customer uses to track sales. Unfortunately, each sales region utilizes a different system to track the sales. However, they have managed to create Excel files with similar or even exact layouts, so they have manually aggregated the data from these over the years. Their new technical lead has questioned this process's efficiency and asked your team to propose a solution. An example of these files is shown below:

Use-Case

The "AggregatedInfo" sheet stores the aggregated information for the data on the "Sales Orders Sheet." The customer wants the system to use the information from the "AggregatedInfo" sheet to fill in the details for a Summary report, as shown below:

Revenue Summary

Click here to download the complete sample for the code snippets used in this blog.

We have determined that this is easily accomplished using GcExcel and C#, and we can do this with a few simple lines of code. However, let's look at the syntax for adding an External Workbook Link.

The Syntax for External Workbook Formula

The External Link formula starts with an = (equal) followed by the path of the source Excel file within single quotes (''). The Excel workbook's filename in the path goes with the square brackets [] followed by the sheet name. The reference range/cell comes after the closing quote, separated by an exclamation mark (!) as shown below:

='Path to Source File [Excel workbook name.xlsx] SheetName' ! CellReference

The path to the source file can be

an absolute file path

='D:\SharedFolder\[SourceWorkbook.xlsx]Sheet1'!A1

a relative path location

='\SharedFolder\[SourceWorkbook.xlsx]Sheet1'!A1

a web URL

= 'http://wwww.grapecity.com/gcexcel/[SourceWorkbook.xlsx]Sheet1'!A1

And the cell reference can be a single cell ("A1"), a range of cells ("A1:D4"), or a named range or table, as we see in the sections that follow.

Adding External Workbook Formula Programmatically

Adding External Workbook Formula using GcExcel and C# involves the following steps:

1. Initialize the Destination Workbook

2. Add Cross-Workbook Formula in the Destination Workbook

3. Update the Excel Links

4. Save the Destination Workbook

Step 1 - Initialize the Destination Workbook

The first step toward adding External Workbook Links is to initialize a Destination workbook where the data from the Source workbook(s) is to be consolidated.

var destination_workbook = new Workbook();
destination_workbook.Open("SalesSummaryReport.xlsx");

Step 2 - Add Cross-Workbook Formula in the Destination Workbook

Cross-workbook or External Workbook Link is added using the IRange.Formula property as shown below:

destination_workbook.ActiveSheet.Range["A1"].Formula = <external workbook reference>;

In the sample we've created for this blog, the Excel files (Northeast.xlsx and Midwest.xlsx) are sourced from a local disk, and the files - South.xlsx and West.xlsx are sourced as embedded resources. Therefore, the External Workbook Formula is set up as follows:

destination_workbook.ActiveSheet.Range["C2"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C2";   //for file on disk
 
destination_workbook.ActiveSheet.Range["D2"].Formula = "='[South.xlsx]AggregatedInfo'!C2";   //for file as embedded resource

Step 3- Update the Excel Links

In this step, the reference formula configured in Step 2 is evaluated to fetch the latest value from the specified reference. GcExcel provides three methods to update the External Workbook Links:

IWorkbook.UpdateExcelLink(linkname)
IWorkbook.UpdateExcelLink(linkname, sourceWorkbook)
IWorkbook.UpdateExcelLinks()

Note:

The "linkName" argument in the above methods refers to the source Excel file path configured in the link formula. You can set it manually or use the Workbook.GetExcelLinkSources method. We recommended using the latter to avoid any human error.

IWorkbook.UpdateExcelLink(linkname)

This method updates an individual link in the workbook. We recommend using this method when the source workbooks are accessible from the disk because this method updates the destination workbook cache directly from the external source file on the disk.

destination_workbook.ActiveSheet.Range["A1"].Formula = "='D:\\shared folder\\[Source.xlsx]Sheet1'!C3";
foreach (var link in destination_workbook.GetExcelLinkSources())
{
     destination_workbook.UpdateExcelLink(link);
}

For the use case above, we've used this method for locally sourced Excel files, as shown in the code below:

...
string[] files = Directory.GetFiles(path + "\\xlsx\\","*.xlsx");
...
if (file.Contains("Northeast"))
    {
        destination_workbook.ActiveSheet.Range["C2"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C2";
        destination_workbook.ActiveSheet.Range["C3"].Formula = "='xlsx\\[Northeast.xlsx]AggregatedInfo'!C3";
        destination_workbook.ActiveSheet.Range["C4"].Formula = "=C3 - 'xlsx\\[Northeast.xlsx]AggregatedInfo'!C4";
        destination_workbook.ActiveSheet.Range["C5"].Formula = "=C4-C2";
                     
        foreach (var link in destination_workbook.GetExcelLinkSources())
        {
            if (link.Contains("Northeast"))
                destination_workbook.UpdateExcelLink(link);
        }                   
    }
...

UpdateExcelLink(linkname, source_workbook)

This is the same as the previous method. However, the difference is that it fetches values cached in a workbook instance. This method is suitable for situations when the source file is not accessible directly from a disk, for example, an HTTP server or online share such as OneDrive.

var source_workbook = new Workbook();
source_workbook.Open("xlsx\\Source.xlsx");  //load the file into a source workbook instance
 
destination_workbook.ActiveSheet.Range["A1"].Formula = "='[Source.xlsx]Sheet1'!C2";
 
foreach (var link in destination_workbook.GetExcelLinkSources())
{
    destination_workbook.UpdateExcelLink(link, source_workbook);
}

For the use case above, we've used this method for embedded Excel files, as shown in the code below:

. . .
 
var south_SourceWb = new Workbook();
south_SourceWb.Open(GetResourceStream("xlsx\\South.xlsx"));
 
. . .
 
destination_workbook.ActiveSheet.Range["D2"].Formula = "='[South.xlsx]AggregatedInfo'!C2";
destination_workbook.ActiveSheet.Range["D3"].Formula = "='[South.xlsx]AggregatedInfo'!C3";
destination_workbook.ActiveSheet.Range["D4"].Formula = "=D3 - '[South.xlsx]AggregatedInfo'!C4";
destination_workbook.ActiveSheet.Range["D5"].Formula = "=D4-D2";
 
. . .
 
foreach (var link in destination_workbook.GetExcelLinkSources())
{
    if (link.Contains("South"))
        destination_workbook.UpdateExcelLink(link, south_SourceWb);
    . . .
}

UpdateExcelLinks()

Use this method when you want to update all the links in the workbook at once. However, this method uses the UpdateExcelLink(linkname) method internally and thus works only when the source files exist on disks.

destination_workbook.ActiveSheet.Range["A1"].Formula = "='xlsx\\[Source1.xlsx]Sheet1'!C1";
destination_workbook.ActiveSheet.Range["A2"].Formula = "='xlsx\\[Source2.xlsx]Sheet1'!C2";
destination_workbook.ActiveSheet.Range["A3"].Formula = "='xlsx\\[Source2.xlsx]Sheet1'!C3";
 
destination_workbook.UpdateExcelLinks();

Note: Since not all the source files are available locally in our sample, we've not used this method.

Step 4 - Save the Destination Workbook

Once the External Workbook formulas are set and updated to fetch the latest values from the sources, save the destination workbook. If there are any calculations to be made, call IWorkbook.Calculate method before saving the file as in the code below:

destination_workbook.Calculate();           
destination_workbook.Save("SalesSummaryReport.xlsx");

Download the Sample to see the feature in action!

Other Common Use-Cases with External Workbook Link

We've accessed a cell value from the source workbook in the above examples. But we often need to access a range, a named range, or a table and use the External Reference within a formula. Let us see how the formula appears in these cases:

Access a Range

To access a range in the cross-workbook formula, use a range string as shown below:

worksheet.Range["F3:F6"].Formula = @"='xlsx\\[Midwest.xlsx]Summary Report'!C3:C6";

Access a Named Range

To access a named range in the cross-workbook formula, use the name of the range as shown below:

worksheet.Range["F3"].Formula = @"='xlsx\\Midwest.xlsx'!RangeName";

Notice that the source workbook name is not enclosed within the square brackets [ ] when accessing a named range.

Access a Table

To access a Table in the cross-workbook formula, use the structured references with Excel tables syntax as shown below:

worksheet.Range["F3"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[#Data]";  //get informaton from the data cells
worksheet.Range["F4"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[[Column1],[#Data]]"; //get information from data cells of Column1
worksheet.Range["F5"].Formula = @"='xlsx\\Midwest.xlsx'!TableName[[#Headers],[#Totals]]"; //get information from headers and totals row

Notice that the source workbook name is not enclosed within the square brackets [ ] similar to accessing a named range.

Using External Workbook Link in a Formula Function

Like any other reference in Formula functions, External Workbook Link can be used within or combined with formula functions. For example, getting a total of the production cost for all regions can be accomplished with the formula below:

worksheet.Range["A2"].Value = "Total Production Cost:";
worksheet.Range["A2"].Formula = "=SUM('xlsx\\[Midwest.xlsx]AggregatedInfo'!C2, 'xlsx\\[Northeast.xlsx]AggregatedInfo'!C2, '[West.xlsx]AggregatedInfo'!C2, '[South.xlsx]AggregatedInfo'!C2";

Thanks for checking out this blog, and good luck with your data analysis needs!

Check out the .NET Demo | .NET Help | Java Demo | Java Help to learn more about the External Workbook formula.

Ready to Create External Workbook Links On Your Own? Download a FREE 30-Day Trial Now!