Solved

Excel 2003 Automation using C#

Posted on 2009-05-04
6
1,025 Views
Last Modified: 2013-12-17
Background:  I developed an application that automated Excel 2003 using C#.Net in VS 2005.  The development machine had Excel 2003 loaded on it.  The application built, deployed, installed and ran just fine on the user's machine.

Change:  Excel 2007 was added to the development machine, without deleting Excel 2003, so both 2007 and 2003 are on the machine.  No change was made to the application.

Problem: When the application is redeployed and installed on the user's machine it comes back with an error saying that Office (or Excel) Object library 12.0 is not installed on the his machine. [I don't recall the exact error since this happened a few months ago, and I'm just now getting back to it.]

So I don't get it, the application compiled with the Excel 2007 object library even though the aaplication sets a references to Excel (and Office) 2003.

What gives?

Thanks for you comments in advance.

Mark

0
Comment
Question by:MarkMahon
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24300864
Are you deploying the Excel object library with your application? It sounds like the user does not have Access 2007 installed and you are not packaging it with your application.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 24301052
0
 

Author Comment

by:MarkMahon
ID: 24303533
kaufmed -

I agree.  But how do I make Visual Studio, when publishing, ignore the Excel 2007 object library and use the Excel 2003 library?

Mark
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 24310252
you have to reference Excel 2003 in your application.
0
 

Author Comment

by:MarkMahon
ID: 24314829
emoreau -

I attached the code, any suggestions how to reference 2003?

Also, I found some MS documentation on the subject:

"One problem with early binding is that most type library aware compilers will "auto-update" your reference to the latest library that is available on the system. This means that if you need to use the earliest version of the library in order to work with multiple versions of an Office application, you must explicitly specify the older library in your project.

For example, if you have an existing Microsoft Visual Basic project that was compiled on a computer with Microsoft Word 97, that project runs correctly against Word 97, Word 2000, Word 2002, Microsoft Office Word 2003, and Microsoft Office Word 2007. If you later install Word 2000, then the next time you open the Visual Basic project, the project reference auto-updates to the Word 2000 Object Library. If you then recompile the project using the Word 2000 library, the code may no longer work with Word 97. To correct this problem, clear the reference to the new library and explicitly browse for and select the older type library."

Which has me a bit befuddled ... I think I have to play a bit with this ... i.e., compile it, remove the reference to Excel 2007 (12),  and set a new reference to Excel 2003 (11), recompile and hope that the "type libray aware compiler" doesn't set it back to Excel 2007 (ugh).

Thanks for your comments.
using System;

using System.Collections.Generic;

using System.Text;

 

using System.Collections;

using System.Data;

using System.Data.OleDb;

 

using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Windows.Forms;

 

using System.Data.SqlClient;

using System.Diagnostics;

 

namespace BusinessTier

{

 

    public class GetExcelData

    {

        //Holds Map of Excel Field Names to Database Field Names

 

        //private Hashtable ht = new Hashtable();

 

        public GetExcelData()

        {

            //LoadColNames();

        }

 

        public DataTable GetExcelDataTable(string filepath, string filename, string sheetname)

        {

            string cnstr = BuildConnectionString(filepath, filename);

            DataTable dt = GetExcelDataTable(cnstr, sheetname, filepath, filename);

            return dt;

        }

 

        private string BuildConnectionString(string filepath, string filename)

        {

            //The "IMEX=1" parameter was added to force fields with mixed coding (both numeric and text) to import to C# as a text field.

            //  This bypasses Excel's nature of picking a data type based on the majority datatypes in the column of cell (e.g., if more cells 

            //  in the column contained text than numeric, Excel/C# assigns a string datatype to the field, nullifying numeric values)

 

            return ("Provider=Microsoft.Jet.OLEDB.4.0;" +

                    "Data Source=" +

                    filepath + filename + ";" +

                    @"Extended Properties=""Excel 8.0;IMEX=1;""");

                    //"Extended Properties=Excel 8.0;");

        }     

 

        private DataTable GetExcelDataTable(string cnstr, string sheetname, string pathname, string filename)

        {

            string cn = cnstr;

            DataTable dtTreatyType = new DataTable();

            string select = String.Empty;

            DataTable dt = new DataTable();

            try

            {

                OleDbDataAdapter daTreatyType = new OleDbDataAdapter("SELECT * FROM [Data Capture$E1:E2] ", cn);

                daTreatyType.Fill(dtTreatyType);

                if (dtTreatyType.Rows[0][0].ToString() == "CAT")

                    select = "SELECT * FROM [Data Capture$A1:HJ7] ";

                else

                    select = "SELECT * FROM [Data Capture$A1:HJ17] ";

                OleDbDataAdapter da = new OleDbDataAdapter(select , cn);

                da.Fill(dt);

            }

 

            catch (Exception e)

            {

               throw new Exception(e.Message); 

            }

 

            //If excel decides that the column of "Reinsurance Rate Changes" is

            //  of type string then we did not get the change information.

            //  Open the Excel sheet and get the data cell by cell.

            //

            if (dt.Columns[64].DataType == Type.GetType("System.String"))

            {

                try

                {

                    GetReinsuranceRateChange(pathname, filename, dt);

                }

                catch (Exception ex)

                {

                    throw new Exception(ex.Message);

                }

            }

            dt.AcceptChanges();

 

            //This little loop dumps column infomation to the Debug window

            //  Comment out for now

            //foreach (DataColumn c in dt.Columns)

            //    Debug.WriteLine (c.ColumnName + ":  " + c.DataType.ToString());

 

            return dt;

        }

 

        private void GetReinsuranceRateChange(string pathname, string filename, DataTable dt)

        {

            //Declare excel variables

            Excel.Application xlApp = null;

            Excel.Workbook xlBook = null;

            Excel.Sheets xlSheets = null;

            Excel.Worksheet xlWkSheet = null;

            //Excel.Range xlEPIRange = null;

            Excel.Range xlRange = null;

            try

            {

                object oMissing = Missing.Value;

                //Instantiate the Excel and open the file

                xlApp = new Excel.Application();

                xlBook = xlApp.Workbooks.Open(pathname + filename,

                    false, true, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing);

                xlSheets = xlBook.Worksheets;

                xlWkSheet = (Excel.Worksheet)xlSheets.get_Item("Data Capture");

 

                int rowCount = 0;

                foreach (DataRow row in dt.Rows)

                {

                    rowCount++;

                    xlRange = (Excel.Range)xlWkSheet.get_Range("BM" + (rowCount + 1).ToString() + ":BM" + (rowCount + 1).ToString(), Type.Missing);

                    if (IsNumeric(xlRange.Cells.Value2.ToString()))

                        row[64] = xlRange.Cells.Value2.ToString();

                    else

                        row[64] = null;

 

                }

 

                xlBook.Close(false, oMissing, oMissing);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);

                xlBook = null;

 

                xlApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;

            }

 

            catch (Exception e)

            {

                //Throw exception if there is trouble

                throw new Exception(e.Message);

            }

 

            return;

        }

 

        private static bool IsNumeric(string p)

        {

            try

            {

                Convert.ToDouble(p);

                return true;

            }

 

            catch

            {

               return false;

            }

        }

    }

}

Open in new window

0
 

Accepted Solution

by:
MarkMahon earned 0 total points
ID: 24326543
I tried the "compile it, remove the reference to Excel 2007 (12),  and set a new reference to Excel 2003 (11), recompile and hope that the "type libray aware compiler" doesn't set it back to Excel 2007", but no luck.

It keeps compiling with Excel 2007.

The only solution that works so far is to unloaded Excel 2007 from my development computer and then compile.  Kind of lame.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now