Excel Error Hresult: 0X800A03Ec While Trying to Get Range with Cell's Name

  • Home
  • Languages
  • Csharp
  • Excel Error Hresult: 0X800A03Ec While Trying to Get Range with Cell's Name

Excel error HRESULT: 0x800A03EC while trying to get range with cell's name

The error code 0x800A03EC (or -2146827284) means NAME_NOT_FOUND; in other words, you've asked for something, and Excel can't find it.

This is a generic code, which can apply to lots of things it can't find e.g. using properties which aren't valid at that time like PivotItem.SourceNameStandard throws this when a PivotItem doesn't have a filter applied. Worksheets["BLAHBLAH"] throws this, when the sheet doesn't exist etc. In general, you are asking for something with a specific name and it doesn't exist. As for why, that will taking some digging on your part.

Check your sheet definitely does have the Range you are asking for, or that the .CellName is definitely giving back the name of the range you are asking for.

HRESULT: 0x800A03EC on Worksheet.range

This problem occurs if you are using a backwards compatible sheet (a .xls) instead of a .xlsx

To allow sheets to be opened in pre office 2007 version it can't contain more than 65k rows. You can check the number of rows in your sheet by using ctrl+arrowdown till you hit the bottom. If you try to get a range larger than that number of rows it will create an error

Excel throws HRESULT: 0x800A03EC exception when using Range

Cells() takes two integer parameters for row and column, or a string parameter for a single cell address. If you want to target a range, you need to use Worksheet.Range instead of Worksheet.Cells. Worksheet.Range can accept a starting and ending cell address, such as:

wSheet.Range("A1", "AD413").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

In your case, since you already have the string "A1:AD413" in a variable called range, you could just change the cell-formatting lines to:

wSheet.Range(range.Split(":")(0), range.Split(":")(1)).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

How do I fix the HRESULT: 0x800A03EC error when loading data from a DataTable?

Given the following definition for DataTable (name: dataTableNewsLetter):

DataTable dataTableNewsletter = new DataTable();

//add columns
//dataTableNewsletter.Columns.Add(new DataColumn() { Caption = "Artikelnummer", ColumnName = "Artikelnummer", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Artikelnummer", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Hersteller", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Beschreibung", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Nettopreis", DataType = System.Type.GetType("System.Decimal") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Bruttopreis", DataType = System.Type.GetType("System.Decimal") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Zustand", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "P/N", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie I", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie II", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Kategorie III", DataType = System.Type.GetType("System.String") });
dataTableNewsletter.Columns.Add(new DataColumn() { ColumnName = "Shop-Link", DataType = System.Type.GetType("System.String") });

//add data
DataRow row = dataTableNewsletter.NewRow();
row["Artikelnummer"] = "50018113"; //item number
row["Hersteller"] = "HP"; //manufacturer
row["Beschreibung"] = "HP DL38X Gen10 2 Drive NVMe Slim SAS Cable Kit - 871827-B21 NEU"; //description
row["Nettopreis"] = 195; //net price
row["Bruttopreis"] = 195; //gross price
row["Zustand"] = "New"; //condition
row["P/N"] = "869812-001"; //part number
row["Kategorie I"] = "Komponenten"; //category 1
row["Kategorie II"] = "Kabel-Adapter"; //category 2
row["Kategorie III"] = "NVMe-Kabel"; //category 3
row["Shop-Link"] = "https://www.gekko-computer.de/Komponenten/Kabel-Adapter/NVMe-Kabel/HP-DL38X-Gen10-2-Drive-NVMe-Slim-SAS-Cable-Kit-871827-B21-NEU.html"; //URL

//add
dataTableNewsletter.Rows.Add(row);

//add new row
row = dataTableNewsletter.NewRow();
row["Artikelnummer"] = "50015171"; //item number
row["Hersteller"] = ""; //manufacturer
row["Beschreibung"] = "NetApp Ethernet Kabel CAT 6 2m - 112-00195 X6561-R6"; //description
row["Nettopreis"] = 38; //net price
row["Bruttopreis"] = 38; //gross price
row["Zustand"] = "Used"; //condition
row["P/N"] = "112-00195"; //part number
row["Kategorie I"] = "sonstiges"; //category 1
row["Kategorie II"] = "Kabel-Adapter"; //category 2
row["Kategorie III"] = "Ethernet-Kabel"; //category 3
row["Shop-Link"] = "https://www.gekko-computer.de/sonstiges/Kabel-Adapter/Ethernet-Kabel/NetApp-Ethernet-Kabel-CAT-6-2m-112-00195-X6561-R6.html"; //URL

//add
dataTableNewsletter.Rows.Add(row);

The exception: Exception from HRESULT: 0x800A03EC can be replicated by doing the following:

for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
{
    //first row contains headers
    int xlRowNum = i + 2;

    string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString();
    string url = dataTableNewsletter.Rows[i]["Shop-Link"].ToString();
    
    //create hyperlink
    Debug.WriteLine($"location: {i}, 11");

    //The next line results in 'Exception from HRESULT: 0x800A03EC'
    //because 0 is an invalid index in Excel 
    ((Excel.Range)xlWSheet.Cells[i, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; 
}

It looks like you are creating a .xls (older Excel) file instead of a .xlsx (newer Excel) file. If you create a .xlsx file you could use one of the following NuGet packages instead of Excel Interop:

  • DocumentFormat.OpenXml
  • ClosedXml
  • EPPlus
  • NPOI

Is the tab-delimited file something you just created for testing?

A tab-delimited file isn't really an Excel file. If you open Excel and select File => Save As, you'll see that a tab-delimited file is saved as a .txt file. When I opened the tab-delimited file in Excel it generated a warning about the file format not matching the file extension. If the tab-delimited file is saved with a .txt extension, Excel seems to open a wizard when the file is opened. This can be eliminated by naming the file with a .csv extension instead - although it's not really a .csv file either, but it doesn't seem to generate any warnings.

Since you're retrieving data from a database and stated that the data is already in a DataTable, it seems prudent to use the DataTable to create the Excel workbook.

Try the following (Excel Interop):

Create a new Windows Forms App (.NET Framework) project.

Then either download / install NuGet package: Microsoft.Office.Interop.Excel or add a reference to Microsoft Excel xx.x Object Library (Project => Add Reference...=> COM => Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library))

Add the following using directives:

  • using Excel = Microsoft.Office.Interop.Excel;
  • using System.IO;
  • using System.Data;
  • using System.Diagnostics;

CreateExcelWorkbook:

public static void CreateExcelWorkbook(DataTable dataTableNewsletter, string excelFilename)
{
    Excel.Application xlApp = null;
    Excel.Workbook xlWBook = null;
    Excel.Worksheet xlWSheet = null;
    Excel.Range allBZ = null;
    Excel.Range exrngKopf = null;

    try
    {
        if (dataTableNewsletter == null)
            throw new Exception("Error - Data table is null.");
        else if (dataTableNewsletter.Rows.Count <= 0)
            throw new Exception($"Error - Data table doesn't contain any data.");

        //create new instance
        xlApp = new Excel.Application();

        //whether or not to make Excel visible
        xlApp.Visible = true;

        //prevent prompting to overwrite existing file
        xlApp.DisplayAlerts = false;

        //disable user control while modifying the Excel Workbook
        //to prevent user interference
        //only necessary if Excel application Visibility property = true
        //need to re-enable before exiting this method
        //xlApp.UserControl = false;

        //if writing/updating a large amount of data
        //disable screen updating by setting value to false
        //for better performance.
        //re-enable when done writing/updating data, if desired
        //excelApp.ScreenUpdating = false;

        //add Workbook
        xlWBook = xlApp.Workbooks.Add();

        //activate
        xlWBook.Activate();

        if (xlWBook.Worksheets.Count > 0)
            xlWSheet = (Excel.Worksheet)xlWBook.ActiveSheet;
        else
            xlWSheet = (Excel.Worksheet)xlWBook.Sheets.Add();

        xlWSheet.Name = "GEKKO Computer GmbH";

        //write column headers
        //Excel indices start with 1; A1 = 1,1
        for (int j = 0; j < dataTableNewsletter.Columns.Count; j++)
        {
            int xlColNum = j + 1;

            //set value - column header
            xlWSheet.Cells[1, xlColNum] = dataTableNewsletter.Columns[j].ColumnName;

            //get range for column
            //Excel.Range colRng = ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn;

            //use DataTable data types to set data type for Excel column
            //ToDo: change as desired
            if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.DateTime")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = @"yyyy\-mm\-dd;@";
            else if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.Int32")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = 0;
            else if (dataTableNewsletter.Columns[j].DataType.ToString() == "System.Decimal")
                ((Excel.Range)xlWSheet.Cells[1, xlColNum]).EntireColumn.NumberFormat = "0.00";
        }

        //set values in Excel using data from DataTable
        //ToDo: add desired code

        for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
        {
            //Excel row numbers start with 1
            //headers are in row 1, so data starts in row 2
            int xlRowNum = i + 2;

            for (int j = 0; j < dataTableNewsletter.Columns.Count; j++)
            {
                //Excel column numbers start with 1
                int xlColNum = j + 1;

                if (dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName] != null && dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName] != DBNull.Value)
                {
                    //set cell value
                    xlWSheet.Cells[xlRowNum, xlColNum] = dataTableNewsletter.Rows[i][dataTableNewsletter.Columns[j].ColumnName].ToString();
                }
            }
        }

        //set value
        allBZ = (Excel.Range)xlWSheet.UsedRange;
        //Debug.WriteLine($"allBZ.Rows.Count: {allBZ.Rows.Count}; allBZ.Columns.Count: {allBZ.Columns.Count}");

        //auto fit
        allBZ.EntireColumn.AutoFit();

        //set value
        //exrngKopf = (Excel.Range)xlWSheet.Rows[1]; //row 1; header row
        exrngKopf = (Excel.Range)xlWSheet.Cells[1, allBZ.Columns.Count]; //row 1; header row
        exrngKopf.EntireRow.Font.Bold = true;

        //set Border line style
        xlWSheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;

        xlWBook.Application.ActiveWindow.SplitRow = 1;
        xlWBook.Application.ActiveWindow.FreezePanes = true;

        if (xlWSheet.AutoFilter != null)
            xlWSheet.AutoFilterMode = false;

        xlWSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, allBZ, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "WFTableStyle";
        xlWSheet.ListObjects.get_Item("WFTableStyle").TableStyle = null;

        //fix hyperlinks
        for (int i = 0; i < dataTableNewsletter.Rows.Count; i++)
        {
            //first row contains headers
            int xlRowNum = i + 2;

            //string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString();
            string description = dataTableNewsletter.Rows[i]["Beschreibung"].ToString() + " - " + DateTime.Now.ToString("HH:mm:ss.fff");
            string url = dataTableNewsletter.Rows[i]["Shop-Link"].ToString();
            Debug.WriteLine($"Description: {description}; URL[{xlRowNum}, 11]: '{url}'");

            //create hyperlink - option 1
            //xlWSheet.Hyperlinks.Add(xlWSheet.Cells[xlRowNum, 11], url, System.Reflection.Missing.Value, description, description);

            //create hyperlink - option 2
            ((Excel.Range)xlWSheet.Cells[xlRowNum, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; //works

            //Debug.WriteLine($"location: {i}, 11");
            //((Excel.Range)xlWSheet.Cells[i, 11]).Formula = $"=HYPERLINK(\"{url}\", \"{description}\")"; //Exception from HRESULT: 0x800A03EC 
        }

        //save Workbook - if file exists, overwrite it
        //xlWBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlWBook.SaveAs(excelFilename, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
    }
    finally
    {
        if (xlWBook != null)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(allBZ);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exrngKopf);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWSheet);

            xlWSheet = null;
            allBZ = null;
            exrngKopf = null;

            //close Workbook
            xlWBook.Close(false);

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWBook);

            xlWBook = null;
        }

        System.Threading.Thread.Sleep(150);

        if (xlApp != null)
        {
            //quit
            xlApp.Quit();

            //release all resources
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);

            xlApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            System.Threading.Thread.Sleep(175);
        }
    }
}

Usage:

string excelFilename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "newsletter.xls");
HelperExcel.CreateExcelWorkbook(dataTableNewsletter, excelFilename);

//the following is necessary otherwise the Excel process seems to persist in Task Manager
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Resources:

  • Microsoft.Office.Interop.Excel Namespace
  • Range.EntireColumn Property
  • How do I get an Excel range using row and column numbers in VSTO / C#?
  • C# Excel how to add hyperlink with cell link
  • How To Create A Hyperlink Using Excel Hyperlink() Function In EPPlus .NET Application (C#) Part - Seven
  • How to automate Microsoft Excel from Microsoft Visual C#.NET
  • How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

Related Topics

C# Driver Development

Are C# Events Synchronous

Ignore Exception in C#

How to Store JSON in an Entity Field with Ef Core

Limit the Number of Parallel Threads in C#

Directly Sending Keystrokes to Another Process via Hooking

How to Translate Cultureinfo Language Names

How to Return 401 Instead of 302 in ASP.NET Core

Why Does Microsoft.Office.Interop.Excel.Application.Quit() Leave the Background Process Running

Check If All Items Are the Same in a List

Copy Arrays to Array

The Source File Is Different from When the Module Was Built

How to Register a .Net Com Dll with Regsvr32

When Using a Settings.Settings File in .Net, Where Is the Config Actually Stored

Rotate - Transposing a List<List<String>> Using Linq C#

Why Method Overloading Is Not Allowed in Wcf

Nsubstitute - Testing for a Specific Linq Expression

How to Determine the Length (I.E. Duration) of a .Wav File in C#


Leave a reply



Submit