?
Solved

Excel 2003 Automation using C#

Posted on 2009-05-04
6
Medium Priority
?
1,034 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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