>>Okay...
>>
>>I've tried to reproduce your problem using the data you show below. With the oledb driver, I'm getting the same null results that you do; however, with our direct access service, we get the proper results returned.
>>
>>We had a lot of trouble using Oledb to import excel, that we now use a form of automation to do it which results in better control over the data. If you want, I can provide sample code. Its in c#.
>>
>>
>It means that you need to have Excel installed, right?
>
>I think it would be nice to see the code. Looks like Arryel Cabansag is having similar problems.


Here is the code to call the service:

            Array values = null;
            try
            {
                ExcelWrapperService ew = new ExcelWrapperService();
                ew.OpenFile(fileName, "");
                ew.GetExcelSheets();
                ew.FindExcelWorksheet(parm.excelSheetName);
                values = ew.GetRange("");
                ew.CloseFile();
                ew.StopExcel();
            }
            catch (Exception ex)
            {
                throw ex;
            }

and the service... this was modified from something I found on the web.

using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Threading;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

using Quay.Framework.Business;

namespace Quay.App.Business
{
	/// <summary>
        /// 09/29/06 - jb
        /// Excel wrapper for file import directly avoiding use of oledb driver which has a bunch of problems.
	/// </summary>
	public class ExcelWrapperService : qService
	{
		private Application _app = null;
		private Workbook _workbook = null;
		private Sheets _sheets = null;
		private Worksheet _worksheet = null;
		private static object _missing	= System.Reflection.Missing.Value;
		private static object _visible	= true;
		private static object _false	= false;
		private static object _true		= true;
		private bool _appVisible = false;
		private object _fileName;

        
                /*********************************************/
		#region Variables for workbook

		private object _updateLinks = 0;
		private object _readOnly = _true;
		private object _format = 1;
		private object _password = _missing;
		private object _writeResPassword = _missing;
		private object _ignoreReadOnlyRecommend = _true;
		private object _origin = _missing;
		private object _delimiter= _missing;
		private object _editable = _false;
		private object _notify = _false;
		private object _converter = _missing;
		private object _addToMRU = _false;
		private object _local = _false;
		private object _corruptLoad = _false;
		
                private object _saveChanges	= _false;
		private object _routeWorkbook = _false;
		#endregion


		/// <summary>
		/// Constructor
		/// </summary>
		public ExcelWrapperService()
		{
			this.startExcel();
		}

		
                /// <summary>
		/// Construct excel object, with visibility
		/// visible is a parameter, true/false
		/// </summary>
		/// <param name="visible">Visible parameter, true for visible, false for non-visible</param>
                public ExcelWrapperService(bool visible)
		{
			_appVisible = visible;
			this.startExcel();
		}

		/// <summary>
		/// Start excel
		/// </summary>
		private void startExcel()
		{
			if( _app == null )
			{
                            _app = new ApplicationClass();
			}
			
			// Make Excel Visible
                        _app.Visible = _appVisible;
		}


		/// <summary>
		/// Stop Excel
		/// </summary>
		public void StopExcel()
		{
                    if (_app != null)
		    {
			Process[] pProcess; 
			pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
			pProcess[0].Kill();
	             }
		}


		/// <summary>
		/// The following function will take in a filename, and a password
		/// associated, if needed, to open the file.
		/// </summary>
		/// <param name="fileName">file name to open</param>
		/// <param name="password">password, if any</param>
		public void OpenFile(string fileName, string password)
		{
			_fileName = fileName;

			if( password.Length > 0 )
			{
				_password = password;
			}

			try
			{
				// Open a workbook in Excel
				_workbook = _app.Workbooks.Open(fileName, _updateLinks, _readOnly, _format, _password,
                                                                    _writeResPassword, _ignoreReadOnlyRecommend, _origin,
					                            _delimiter, _editable, _notify, _converter, _addToMRU,
					                            _local, _corruptLoad);
			}
			catch(Exception e)
			{
				this.CloseFile();
                                throw e;
			}
		}


		public void CloseFile()
		{
			_workbook.Close( _saveChanges, _fileName, _routeWorkbook );
		}

		/// <summary>
		/// Get Excel Sheets
		/// </summary>
		public void GetExcelSheets()
		{
			if( this._workbook != null )
			{
				_sheets = _workbook.Worksheets;
			}
		}

		/// <summary>
		/// Search for named worksheet, if found return TRUE
		/// </summary>
		/// <returns>bool</returns>
		public bool FindExcelWorksheet(string worksheetName)
		{
			bool sheetFound = false;
			if( _sheets != null )
			{
				// Step thru the worksheet collection and see if worksheet is
				// available. If found return true;
				for( int i = 1; i <= _sheets.Count; i++ )
				{
					_worksheet = (Worksheet)_sheets.get_Item((object)i);
					if( _worksheet.Name.ToLower().Equals(worksheetName.ToLower()))
					{
						_worksheet.Activate();
						sheetFound = true;
                                                break;
					}
				}
			}
			return sheetFound;
		}


		/// <summary>
		/// Return content of range from the selected range
                ///     "A1" returns all cells as currently defined
		/// </summary>
		/// <param name="range">Range parameter: Example, GetRange("A1:D10")</param>
		public Array GetRange(string range)
		{
                       int rowCount = _worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, _missing).Row;
                       int columnCount = _worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, _missing).Column;

                       Range workingRangeCells = null;
                       if (range.Length == 0)
                       {
                             workingRangeCells = _worksheet.UsedRange;

                       }
                       else
                       {
                             workingRangeCells = _worksheet.get_Range(range, Type.Missing);
                       }
		       Array array = (System.Array)workingRangeCells.Cells.Value2;

		       return array;
		}
	}
}