Working with Excel

[Duki]

Hey everyone,

I have an excel file I'm trying to work with. I can open it, write to it, and save it fine. But now I'm trying to work with adding sheets, and modifying the names of those sheets. For some reason though, my app keeps locking my excel files. I've had to create about 5 test.xlsx files because all of the other test files are permanently read-only (until I log off) for some reason. Here's my code - it looks like I should be closing the files after each run, no matter what, but for whatever reason I'm still getting locked messages.

object oMissing = System.Reflection.Missing.Value;
            Excel.Application xl = new Excel.Application();
            Excel.Workbook xlBook;
            Excel.Worksheet xlSheet;
            string laPath = "C:\\Users\\ctote.REG\\Documents\\My Dropbox\\test3.xlsx";

            try
            {
                xlBook = xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                xlSheet.Name = "108";
                xl.Worksheets.Add();
                xl.Worksheets[0].name = "Testing";
                xlBook.Save();
            }
            catch (Exception)
            {
                MessageBox.Show("locked");
            }
            finally
            {
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }

Can someone tell me what I'm doing wrong?

[nick.crane]

Add takes four parameters. Look here Sheets.Add.
The most important one is the last one that defines what you are adding.

[Duki]

Great thanks - would this be causing the locked files?

[nick.crane]

Quite possibly. I don't know what Add() does. It's not documented!

[Duki]

Ok it's strange. My app works fine as long as no exceptions are thown - but then it locks the file permanently.

my code:

public void create(string file, string sheet)
        {
            try
            {
                xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                //xlSheet.Name = sheet;
                xl.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
                xl.Worksheets[1].name = sheet;
                xlBook.Save();
            }
            catch (Exception)
            {
                MessageBox.Show("Create Error");
            }
            finally
            {
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }
        }

If for the name I put an invalid value, "205:", it throws the exception, then locks my file forever and ever (until I log out, etc.).

[nick.crane]

Maybe you need to select the sheet first.
I read somewhere that this app interface works like Excel, and you can only change the sheet name of the current sheet. so try this.

Excel.Worksheet xlSheet = xl.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
xlSheet.Select();
xlSheet.Name = sheet;

or

Excel.Worksheet xlSheet = xl.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
xlSheet.Select();
xl.ActiveSheet.Name = sheet;