question

ravikumar-1532 avatar image
0 Votes"
ravikumar-1532 asked ·

Converting excel macro to C#

I am creating excel VSTO , where I already have the macro to delete excel rows based on column values(if it is greater than zero)which now needs to be converted to c# , by seeing documentation on internet I have come up so far , but still three errors are present I am not able to solve them kindly help me:

 original macro:
     Sub delete2()
     '
     ' delete2 Macro
     '
        
     '
         Rows("1:1").Select
         Selection.AutoFilter
         ActiveSheet.Range("$A$1:$S$901").AutoFilter Field:=10, Criteria1:=">0", _
             Operator:=xlAnd
         Rows("8:1382").Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.delete Shift:=xlUp
         ActiveSheet.Range("$A$1:$S$891").AutoFilter Field:=10
         Selection.AutoFilter
     End Sub

modified by me till now:

 using Microsoft.Office.Tools.Ribbon;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using Excel = Microsoft.Office.Interop.Excel;
 using Office = Microsoft.Office.Core;
 using Microsoft.Office.Tools.Excel;
 using Microsoft.Office.Interop.Excel;
 using System.Windows.Forms;
    
 namespace CpCpk
 {
     public partial class Ribbon1
     {
         private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
         {
    
         }
    
         private void button1_Click(object sender, RibbonControlEventArgs e)
         {
             var excelappp = new Excel.Application();
             Excel.Worksheet excelApp = Globals.ThisAddIn.Application.ActiveSheet;
    
             excelappp.Rows["1:1"].Select();
             excelappp.Selection.AutoFilter();
             excelappp.ActiveSheet.Range["$A$1:$S$901"].AutoFilter(Field: 10,Criteria1:">0",Operator: Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd);
             excelappp.Rows["8:1382"].Select();
             excelappp.Range[excelappp.Selection, excelappp.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
             excelappp.Selection.delete(Shift: Microsoft.Office.Interop.Excel.XlDirection.xlUp);
             excelappp.ActiveSheet.Range["$A$1:$S$891"].AutoFilter(Field:10);
             excelappp.Selection.AutoFilter();
         }

although there are no syntax error , I am getting below error during execution :

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

kindly help me how to solve this



dotnet-csharpoffice-vba-devoffice-addins-devoffice-vsto-com-dev
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ravikumar-1532
I am not particularly familiar with macros. Can you provide some screenshots or more detailed descriptions to express your needs, let us try if there are other ways to do the same thing.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

Maybe remove excelappp variable and use your excelApp instead. (It can be renamed to something like active_sheet). Remove '.ActiveSheet.' parts. What error do you get in this case?

· 3 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi @Viorel-1 thank you for your response , i tried doing the same , it is giving below error:

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot perform runtime binding on a null reference'


0 Votes 0 ·
Viorel-1 avatar image Viorel-1 ravikumar-1532 ·

Which line thrown the exception and did you check if Globals.ThisAddIn.Application and Globals.ThisAddIn.Application.ActiveSheet are not null?


0 Votes 0 ·

these below tow lines are the cause: as i can't change them to "excelApp" because "Selection" method isn't avialble in "worksheet"

 excelappp.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
 excelappp.Selection.delete(Shift: Microsoft.Office.Interop.Excel.XlDirection.xlUp);

yes the data was present in the sheet.


0 Votes 0 ·