Solved

Excel 2003 Automation using C#

Posted on 2009-05-04
6
1,027 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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