Query an Excel Spreadsheet

PDTUM

Good Morning, I am trying to extract a column of data from an Excel spreadsheet and pass it to a list box. I thought this was straight forward and I have found several very similar code samples through Google and Code Project search, but I cannot get any to work. I have created several variations of each with my own syntax (which is usually simpler) but I keep getting this error: ERROR: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." I have tried adding a piece of code that I found to make sure the process is not running (just guessing here...see Sample 1), but I do not think that it works either. Below are 2 different code snippets that produce the same error. I would appreciate it these can be corrected of if a new code sample can be demonstrated to cure this issue ot produce the desired result. Sample 1

private DataTable GetExcel(string fileName, string sheetName)
{
DataTable dt = new DataTable(sheetName);

        try
        {
            string ConStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";", fileName); 
            string Sql = "SELECT \* FROM \[" + sheetName + "$\]";

            OleDbConnection Conn = new OleDbConnection(ConStr);
            OleDbCommand Comm = new OleDbCommand(Sql, Conn);
                
            OleDbDataAdapter da = new OleDbDataAdapter(Comm);

            Process\[\] process;
            process = System.Diagnostics.Process.GetProcessesByName("Excel");
            if (process.Length > 0)
            {
                process\[0\].Kill();
            }

            Conn.Open(); 
            da.Fill(dt);
            Conn.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
 
        return dt;
    }

Sample 2

    private void GetExcelInfo()
    {
        string theFile = textBoxExcelFile.Text;
        string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + theFile + ";Extended Properties=Excel 8.0";
        string sheetName = "Sheet1";

        // Create the connection object
        OleDbConnection Conn = new OleDbConnection(ConStr);
        try
        {
            // Open connection
            Conn.Open();

PIEBALDconsult

Do you have the file open in Excel?

PDTUM

No. The app uses the openFileDialog to create the path and put it in a textbox so I can transfer it to the connection string. It looks like so: C:\Users\Pat\Documents\Visual Studio 2010\Projects\Perfect 2012\Perfect 2012\bin\Debug\Filename.xls Also, I ran the following code to determine all the running processes at the time of the call:

ArrayList allProcesses = new ArrayList();
foreach(Process p in processlist)
{
allProcesses.Add(p);
}

No Excel processes were revealed. I am baffled. Thanks for your question...Pat

Dave Kreskowiak

Is you're project being targeting "AnyCPU" and is your version of Windows 64-bit?? If so, then you can't use the Jet 4.0 provider. It's a 32-bit only piece of code and you can't mix 32- and 6-4bit code in the same process. Try setting your compile options to target x86 only.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

PDTUM

Thank you Dave, This is a 32 bit system and I am already configured for x86. I appreciate your reply. Let me know if anything else comes to mind or if you see a problem in the code...Best, Pat

PIEBALDconsult

How about the SPACEs in the path?

JOAT MON

Try removing the conn.Open(); line. When a data adapter fills, it open the associated connection, fills the DataSet, then closes the associated connection automatically. <-- Edit: nevermind, just tried your code and it works fine. You must have something holding onto that file (or permissions, password, etc.) that prevents it from opening.

Jack of all trades ~ Master of none.

PDTUM

Hello..Thank you for your input. I agree about the connection, but this was a copied (supposedly already working code), so I left it. I removed the connection commands as suggested, but the result is the same. Let me now if you have any other thoughts...Best, Pat OK, I just saw the edit...hmmm, what can I say. I'll keep looking, perhaps try a reboot. If I find the answer, I will post it. If not, I'll have to try a different way to get to the intended result....P.

JOAT MON

The app doesn't create any Streams that use the file does it?

Jack of all trades ~ Master of none.

PDTUM

OOOOKKKK..........JOAT-MON YES...you are absolutely right. There was a Streamwriter object that was called when the file was imported. I normally enclose them in a using statement so that they will close automatically, but for some reason I did not do it this time. I have made the correction and it is working perfectly now. Thank you very much for your insight. I am marking your answer as THE answer. Kudows and Best Regards, Pat

JOAT MON

Good deal! I'm glad you found it and got it working.

Jack of all trades ~ Master of none.