Count cells in Excel spreadsheet from within C#

I have a directory with thousands of Excel spreadsheets and I need to find the number of non blank cells in each excluding row and column headings.  I'm trying to do it using Excel's CountA function.
I'm running the code below which is giving this error message after looking at the first spreadsheet in the directory.  Please can you help?

"COM object that has been separated from its underlying RCW cannot be used"


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using System.Threading;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string OutputFile;
            long NumberOfNonBlankCells;
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.ApplicationClass();
            OutputFile = "C:\\Output\\Test2.csv";
            try
            {
                using (StreamWriter sw = new StreamWriter(OutputFile))
                {

                    foreach (string inputfile in Directory.GetFiles("C:\\Output\\Test\\Sample"))
                    {
                        try
                        {
                            sw.Write(inputfile.ToString() + "\n");


                            xlWorkBook = xlApp.Workbooks.Open(inputfile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                            //xlApp.WorksheetFunction wsf = xlApp.WorksheetFunction;
                            Excel.WorksheetFunction wsf;

                            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                            xlApp.DisplayAlerts = false;

                            int result = (int)xlApp.WorksheetFunction.CountA(xlWorkSheet.Cells.get_Range("b2", "f100"),
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //wsf.CountA(xlWorkSheet.Cells ,"S","S","K" );


                            xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
                            xlApp.Quit();
                            xlApp.DisplayAlerts = true;
                            releaseObject(xlWorkSheet);
                            releaseObject(xlWorkBook);
                            releaseObject(xlApp);
                           
                            sw.Write(inputfile.ToString() + "\t" + result  + "\n");


                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
               
            }
        }

    }
}
AlHal2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi AlHal2,
Try moving the 'releaseObject(xlApp);' outside of the file foreach loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlHal2Author Commented:
It worked on my first test.  Then I got a different message
Exception from HRESULT: 0x800401A8
0
AlHal2Author Commented:
This is great.  I also changed
xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
to
xlWorkBook.Close(true, inputfile, misValue);
0
AlHal2Author Commented:
The error message was because I was referring to column zz in a 256 column worksheet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.