c# excel interop – cannot access the file

In a recent project we had the need to create a pivot table in Excel from an existing spreadsheet.

Should be relatively easy… as always never as straight forward as it should be!

I’m not going to show the whole code for creating the pivot, but in essence to create a pivot table you need to

  • Open the spreadsheet
  • Select the data range
  • Create a pivot cache
  • Create the actual pivot table
  • Add the Row/Column/Data fields
  • Save the file.

Easy right, well yes so far. This is the basics of the code:

            Application excelApp = new Application();
            Workbook excelWorkBook = excelApp.Workbooks.Open(file);
            Worksheet excelworksheet = excelWorkBook.ActiveSheet;
            Worksheet sheet2 = excelWorkBook.Sheets.Add();

            try
            {

                sheet2.Name = sheetname;
                excelApp.ActiveWindow.DisplayGridlines = showGridLines;


                Range oRange = excelworksheet.UsedRange;
                PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);  
                PivotCaches pch = excelWorkBook.PivotCaches();

                pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[1, 1], "PivotTable", Type.Missing, Type.Missing);// Create Pivot table

                PivotTable pvt = sheet2.PivotTables("PivotTable");
                pvt.ShowDrillIndicators = true;
                pvt.InGridDropZones = false;


                // add rows

                // add columns

                // add data fields

                #region Misc

                sheet2.UsedRange.Columns.AutoFit();

                pvt.ColumnGrand = true;
                pvt.RowGrand = true;

                excelApp.DisplayAlerts = false;

                if (removeSourceSheet)
                    excelworksheet.Delete();

                sheet2.Activate();
                sheet2.get_Range("B1", "B1").Select();

                #endregion

                excelWorkBook.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelApp.DisplayAlerts = false;
                excelWorkBook.Close(0);
                excelApp.Quit();

                return "OK";

            }
            catch (Exception ex)
            {
                excelWorkBook.Close(0);
                excelApp.Quit();

                return ex.ToString();

            }

So that’s it really, running it works a treat… unfortunately this is where my problems start.

When running in unattended mode you get something like:

A call to ‘x’ failed with this message: Microsoft Excel cannot access the file ‘<filename>’. There are several possible reasons:

The file name or path does not exist.
The file is being use by another program.
The workbook you are trying to save has the same name as a currently open workbook

Now personally I think this is misleading.. as it turns out to be none of those reasons!

Turns out excel interop doesn’t like running in unattended mode, aka non interactive or whatever you would like to call it – but I need it too.

Step in some black voodoo magic 🙂

Turns out you need to make sure 2 folders exist for it to work, and there are:

  C:\Windows\System32\config\systemprofile\Desktop
  C:\Windows\SysWOW64\config\systemprofile\Desktop

Now, when it runs it works perfectly. It should be noted this isn’t support by Microsoft, so use at your own risk – and it could stop working at any point in time!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: