Insert Row Error Automating Excel with C#

Visual Studio Tools for Office

Jerry8989

I would like to just insert a few rows within my excel file. I'm currently able to add text anywhere within the file but for some reason when I try and add extra lines it craps out. I'm going to dump some data and I have to be able to insert extra lines because below the data I already have text that I need to keep.

/***********************************************************************************************************/

Microsoft.Office.Interop.Excel.Range m_objRange;

Microsoft.Office.Interop.Excel.Range m_objSheet;

m_objRange = m_objSheet.get_Range("5:9", m_objOpt);

ERROR ON THIS ROW:

m_objRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, System.Type.Missing);

/***********************************************************************************************************/

ERROR:

System.Reflection.TargetParameterCountException was unhandled by user code
Message="Number of parameters specified does not match the expected number."
Source="mscorlib"
StackTrace:
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.Insert(Object Shift, Object CopyOrigin)
at QualityTracking.User.GenerateExcel.DumpToExcel(_Worksheet m_objSheet, Boolean dumpHeader) in C:\PDCApps\QualityTracking\QualityTracking\User\GenerateExcel.aspx.cs:line 174
at QualityTracking.User.GenerateExcel.btnExportToExcel_Click(Object sender, EventArgs e) in C:\PDCApps\QualityTracking\QualityTracking\User\GenerateExcel.aspx.cs:line 54
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Top

Re: Visual Studio Tools for Office Insert Row Error Automating Excel with C#

Dennis Wallentin

Hi Jerry,

Hm, I'm not so good with C# but it looks like You have declared the Sheet object as a Range Object:

Microsoft.Office.Interop.Excel.Range m_objSheet;


If You're working with a individual workbook in VSTO then the following VB code works as expected:

Code Snippet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim rnInsert As Excel.Range = Globals.Sheet1.Range("5:9")

rnInsert.EntireRow.Insert()

End Sub





Top

Re: Visual Studio Tools for Office Insert Row Error Automating Excel with C#

Jerry8989

My fault this is the code. I set m_objSheet equal to the sheet with the name passed into my function.

That way I have the sheet I need to do my work on. I'm able to add text in specific cells no problem.

But for some reason adding a new line is not working.

Microsoft.Office.Interop.Excel._Worksheet m_objSheet = GetWorkSheet("CAR", m_objSheets);

private Microsoft.Office.Interop.Excel._Worksheet GetWorkSheet(string sheetName, Microsoft.Office.Interop.Excel.Sheets objSheets)

{

Microsoft.Office.Interop.Excel._Worksheet ws;

for (int n = 1; n <= objSheets.Count; n++)

{

ws = (Microsoft.Office.Interop.Excel._Worksheet)(objSheets.get_Item(n));

if (ws.Name == sheetName)

{

return ws;

}

}

return null;

}

Does this make it a bit clearer

Why would the Insert function error out

Thanks for the response


Top