current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I don't have a lot of experience using C# to work with Excel. I've had to google a lot of the techniques but have had pretty good luck. What leads me here is a problem I'm having when trying to upgrade an Excel file on my company's network. I don't have any trouble upgrading an Excel file on a local drive but when it is directed to a file on the network it fails with "The Microsoft Jet database engine could not find the object '\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx'. Make sure the object exists and that you spell its name and the path name correctly."

I've tried to change the path string to have four slashes and two slashes. "\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx" but it fails, interpretted as: \\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx (Note: stackoverflow won't show my four & two slashes)

I've tried to use the C# technique with @ string FileName = @"\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx"; and changed the command to read: MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';"); But get this error: '\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Any ideas would be appreciated.

Here's the code for the routine that is failing:

  private void buttonSendToExcel_Click(object sender, EventArgs e)
    {
        //try
        //{
            string FileName = @"\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            string sql2 = null;
            string sql3 = null;
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\\nas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\nas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            sql = "Update [Sheet1$] set result=" + od + " where id=1";
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            sql2 = "Update [Sheet1$] set result=" + id + " where id=2";
            myCommand.CommandText = sql2;
            myCommand.ExecuteNonQuery();

            sql3 = "Update [Sheet1$] set result=" + yield + " where id=3";
            myCommand.CommandText = sql3;
            myCommand.ExecuteNonQuery();

            MyConnection.Close();
        //}
        //catch (Exception ex)
        //{
        //    MessageBox.Show(ex.ToString());
        //}
    }
share|improve this question
    
Can you make a local copy of the excel file, and run your code there. And if that works..you know it is solely a network issue. –  granadaCoder May 30 at 13:58
    
Yes, I had mentioned that it works fine on my local drive but fails on the network. –  Bob T May 30 at 14:09
    
I like using OleDbConnectionStringBuilder to help build my connection strings. It helps reduce syntax errors. –  S. Ahn May 30 at 14:27
    
Thanks @S.Ahn, I'm looking at it. I noticed in one of the examples that the network is being referred to with an IP address! –  Bob T May 30 at 15:20

2 Answers 2

up vote 0 down vote

Try a super slashed up path.

"\\\\Server\\MyNetworkDrive\\FolderOne\\FolderTwo\\MySuperCoolFile.xlsx"


@"\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";

SHould that be?

@"\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";

Other Idea.

Put single quotes around the file name. I actually put single quotes around every "value"..to be consistent.

example:

(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FileName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");

Note the single quote include to wrap the value of "FileName".

share|improve this answer
    
I thought the same thing and tried it that way and it still failed. Oddly, the error message showed the path with three slashes and one slash: "\\\server\mynet\folder1\folder2\coolfile.xlsx". Still scratching my head! See the first commented out MyConnection. –  Bob T May 30 at 14:29
    
Sorry, I should have read more carefully. I made an edit to my response, about the "@" thing. –  granadaCoder May 30 at 15:00
    
that's ok @granadaCoder. –  Bob T May 30 at 15:21
    
Check my single quote hint, that I just added to my answer. –  granadaCoder May 30 at 15:24
    
I thought your idea made sense so I tried it. Unfortunately it failed. Here's the error message: '\\nas1\eng\legacy\XView_Results\Book1-xview-test.xlsx' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. I've checked spelling and validity of path and it should have worked! –  Bob T May 30 at 15:44
up vote 0 down vote accepted

There were two things that were changed in my code to solve my problem. 1. Switched path to mapped mode, i.e., \server\file mapped to H:\file 2. Removed the component IMEX=1; from MyConnection

Here's the fixed code:

        try
        {            
            //string FileName = @"\\tiwnas1\eng\legacy\XView_Results\Book1-xview-test.xlsx";
            //string theFile = GetPath(@"\\tiwnas1\eng\legacy\Book1-xview-test_v8.xls");
            //string FileName = @"\\tiwnas1\eng\legacy\Book1-xview-test_v8.xls";

            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            string sql2 = null;
            string sql3 = null;
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\\\tiwnas1\\eng\\legacy\\XView_Results\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\tiwnas1\eng\legacy\XView_Results\Book1-xview-test.xlsx';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='H:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='h:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='h:\\Book1-xview-test_v8.xls';Extended Properties='Excel 8.0;HDR=YES;';");
            //MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FileName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            //sql = "Update [Sheet1$] set result=5.625 where id=1";
            sql = "Update [Sheet1$] set result=" + od + " where id=1";
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            //sql2 = "Update [Sheet1$] set result=5.375 where id=2";
            sql2 = "Update [Sheet1$] set result=" + id + " where id=2";
            myCommand.CommandText = sql2;
            myCommand.ExecuteNonQuery();

            //sql3 = "Update [Sheet1$] set result=110000 where id=3";
            sql3 = "Update [Sheet1$] set result=" + yield + " where id=3";
            myCommand.CommandText = sql3;
            myCommand.ExecuteNonQuery();

            MyConnection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.