c# excel. How pass workbook/sheet as reference

▲
0
▼

I want to pass an excel workbook/sheet as a reference between several functions in order to do different operations on it. I have been looking for hours and can't find out how to do this. Is this not common practice to have multiple functions work on an excel sheet in this way? the below code doesn't work ..doesn't work if i pass by value either.

'System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800401A8' '

pass_ref(ref xlWorkSheet1);  //call this


void pass_ref(ref Excel.Worksheet sheet) {

        MessageBox.Show("Pass Ref:" + sheet.Name); //can't produce sheet name
    }
c#
excel
reference
asked on Stack Overflow Apr 17, 2018 by MattBorg

2 Answers

▲
0
▼
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

pass_ref(xlWorkSheet1);


xlWorkBook.Close(true, misValue, misValue);

Found a workaround. Save the workbook before trying to pass the sheet off. This worked.

answered on Stack Overflow Apr 17, 2018 by MattBorg
▲
-1
▼

There are 3 basic ways to deal with Office Formats, depending on wich ones you need to support. And another one altogether might be better:

  • You only need the new Formats (xlsx). You can use the OpenXML SDK or any of the Wrapper classes. In a pinch you could use the ZipArchive and XMLReader classes to modify those formats too. They are simply a bunch of XML files in a .zip container.
  • You need to support old and new. The (t)rusty office COM interop is the usual way. This is what you picked right now. Of course it requires office to be installed and does not work from non-Interactive (Services, Servers) on top of all the normal issues inherent with COM interop. I would wager you run into one of those.
  • You need to support old and new 2: OleDB Provider. I was only told of this option and have no actuall knowledge of it.

I always advise going for the OpenXML SDK/Option 1. Rather limit the supported Formats then going back to the COM way. OleDB would be my second bet, despite not knowing the details. COM is best left as a fallback solution if nothing else works.

answered on Stack Overflow Apr 17, 2018 by Christopher

User contributions licensed under CC BY-SA 3.0