?
Solved

Excel 2003 Automation using C#

Posted on 2009-05-04
6
Medium Priority
?
1,037 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 75

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 70

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 70

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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