none
How to Transfere Autofilter Codeline from VBA to Vsto Autofilter RRS feed

  • Question

  • Hi @ all

    My Filter setting( ascending) which I set dosent work in VSTO . Could some give me an advice?

    


    VBA:
                Autofilter.Sort.SortFields.Add(Key:=Range("A6:H6"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:= _
                   XlSortDataOption.xlSortNormal)
    
    VSTO ?????
    
    Dim AF As AutoFilter 
                AF.Sort.SortFields.Add(Key:=Range("A6:H6"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:= _
                   XlSortDataOption.xlSortNormal)

    
    Thanks for Help

    • Edited by Richard_MP Monday, April 22, 2013 10:59 AM
    Sunday, April 21, 2013 3:43 PM

Answers

  • Hi Learning hard

    Thanks for the makro hint. I thought also take a Makro code modify a littel bit an the stuff ir running. TI was thinking this unitl I am faced with autofilter too. The end of the storry I am in trouble:-((.

    Your code line dosent work. So far I understoog the global code line is not working.

    I have some fix but it result is not much better maybe you see something in me code proposal.

                Dim strWksName As String = "sheet1"
                rngDummy = CType(Globals.ThisWorkbook.Application.ActiveSheet.Range("A6:H6"), Excel.Range)
                Dim sheet As Worksheet = CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet)
                CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet).AutoFilter.Sort.SortFields.Clear()
                rngDummy.Select()
                rngDummy.AutoFilter(1, , , , True)
                CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet).AutoFilter.Sort.SortFields.Add(Key:=Range("E6"), SortOn:=Excel.XlSortOn.xlSortOnValues, Order:=Excel.XlSortOrder.xlAscending, DataOption:= _
                Excel.XlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
                With sheet
                    .AutoFilter.Sort.Header = XlYesNoGuess.xlYes
                    .AutoFilter.Sort.MatchCase = False
                    .AutoFilter.Sort.Orientation = Excel.Constants.xlTopToBottom
                    .AutoFilter.Sort.SortMethod = XlSortMethod.xlPinYin
                    .AutoFilter.Sort.Apply()
                End With

    • Marked as answer by Richard_MP Tuesday, April 30, 2013 3:19 PM
    Tuesday, April 23, 2013 2:15 PM

All replies

  • Hi Boy88,

    I suggest that you make a copy of a Range and then turn on the autofilter. I find an article which i think it is helpful for you.

    Filtering by the Active Cell's Value, Font Color, or Fill Color in Excel 2007

    And I also find some related links as below:

    Problem with get/set Autofilter Filters from VBA

    How to use AutoFilter to filter records based on cell formatting in Excel

    Creating Auto Filtered Tables In Excel 2007


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Monday, April 22, 2013 3:13 AM
  • Hi  Learning hard

    this article doesn't help me for my problem. Do anyone deal with autofilter in VSTO env?

    I haven't expectet that the transfer is so difficult.

    So far I understood is in Range section the problem located. If I put Range info  in Watch Window the value is nothing all other parameter are set.

    Monday, April 22, 2013 8:29 PM
  • Hi Boy88, 

    I have created some code snippet as below, I help that can help you.

    // Get Activesheet of Excel
    Dim sheet As Worksheet = Globals.ThisAddIn.Application.ActiveSheet
    sheet.AutoFilterMode = False
    // AutoFilter, you also can use AutoFilter method
    sheet.AutoFilter.Sort.SortFields.Add(sheet.Range("A6:H6"), Excel.XlSortOn.xlSortOnValues

    About Use AutoFilter method, you can refer to the below article:

    http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.autofilter(v=vs.80).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 2:13 AM
  • Hi Learning hard

    Thanks for code snippet.

    The first code line generate an error meassage:

    'Worksheet' is ambiguous, imported from the namespaces or types 'Microsoft.Office.Interop.Excel, Microsoft.Office.Tools.Excel'

    I have made a small modification from ThisAddIn to ThisWorkbook because my solution is sheet based.

    Do you know the recent of ths meassage?

    Why do you not use: Dim AF as Autofilter. Why is this a wrong thinking by myself?

    
    Tuesday, April 23, 2013 6:04 AM
  • Hi Boy88,

    You can import the below namespace firstly.

    imports Microsoft.Office.Tools.Excel

    Because you want apply Filters to the Range of the WorkSheet, So you must get the active sheet firstly and then add Filters into the range of worksheet, but you can try my codes to check whether can work for you. If you use Dim AF as AutoFilter, the AF object don't have reference to a instance, but use worksheet.AutoFilter to get AutoFilter object, the worksheet is your opened worksheet, the AutoFilter object reference to instance now. I hope I clear this question and you understand the reason, Thanks 


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 6:32 AM
  • Hi Learning hard

    the nut is not cracked.

    I get no filter in active sheet and no filtering results.  I tested following  code line.

    Imports Microsoft.Office.Tools.Excel           
    
     Dim sheet As Worksheet = Globals.ThisWorkbook.Application.ActiveWorkbook.ActiveSheet
                sheet.AutoFilterMode = False
                '// AutoFilter, you also can use AutoFilter method
                sheet.AutoFilter.Sort.SortFields.Add(Key:=Range("A6:H6"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:=xlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
                CType
    
    
    Alternative:
    
    sheet.AutoFilter.Sort.SortFields.Add(sheet.Range("A6:H6"), Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Excel.XlSortDataOption.xlSortNormal)

    Tuesday, April 23, 2013 7:59 AM
  • comment sheet.AutoFilterMode =true and then test it.

    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 8:03 AM
  • Hi Learning hard

    I have made your suggseted change. It has no effect.

                Dim sheet As Worksheet = Globals.ThisWorkbook.Application.ActiveWorkbook.ActiveSheet
                sheet.AutoFilterMode = True
                '// AutoFilter, you also can use AutoFilter method
                sheet.AutoFilter.Sort.SortFields.Add(sheet.Range("A6:H6"), Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Excel.XlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
                sheet.AutoFilter.Sort.SortFields.Add(Key:=Range("A6:H6"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:=xlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
    What you think?
    Tuesday, April 23, 2013 8:27 AM
  • Hi Boy88, 

    I am sorry that i misunderstand your meaning.

    can you clear that  What kind of effect you want?, can you give the screenshot which you want by using VSTO. 


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 8:30 AM
  • Hi Learning hard

    I have made your suggseted change. It has no effect.

                Dim sheet As Worksheet = Globals.ThisWorkbook.Application.ActiveWorkbook.ActiveSheet
                sheet.AutoFilterMode = True
                '// AutoFilter, you also can use AutoFilter method
                sheet.AutoFilter.Sort.SortFields.Add(sheet.Range("A6:H6"), Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Excel.XlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
                sheet.AutoFilter.Sort.SortFields.Add(Key:=Range("A6:H6"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:=xlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
    What you think?

    I can use the following codes to autoFilter,Do you want that, The codes as below, you can test on your machine and check whether it is you want.

     private void Sheet1_Startup(object sender, System.EventArgs e)
            {
                ActivateAutoFilter();
                
            }
            private void ActivateAutoFilter()
            {
                this.Range["A1", missing].Value2 = "Fruits";
                this.Range["A2", missing].Value2 = "Banana";
                this.Range["A3", missing].Value2 = "Apple";
                this.Range["A4", missing].Value2 = "Banana";
                this.Range["A5", missing].Value2 = "Orange";
                this.Range["A6", missing].Value2 = "Apple";
                this.Range["A7", missing].Value2 = "Orange";
    
                Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
                    this.Controls.AddNamedRange(this.Range["A1", "A7"],
                    "NamedRange1");
    
                NamedRange1.AutoFilter(1, "Apple",
                   Excel.XlAutoFilterOperator.xlAnd, missing, true);
            }

    you can use this tool to convert C# to VB.net:

    http://www.developerfusion.com/tools/convert/csharp-to-vb/?batchId=463bee17-c583-4ff5-8595-4db6e3cff51a

    Thanks 


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 8:36 AM
  • Hi Learning hard

    of cource you could have an screen shoot what I  want achive.

    I would like set  this data filter with filtering on pin number absending.  So I hope I could give a more clear picture for you.

    Tuesday, April 23, 2013 8:54 AM
  • Hi Boy88,

    I have test on my machine and I can get the result what you want achive.

    you can try the following codes as below:

    Me.Range("A1", missing).Value2 = "Pin Number"
    Me.Range("A2", missing).Value2 = "1"
    Me.Range("A3", missing).Value2 = "2"
    Me.Range("A4", missing).Value2 = "3"
    Me.Range("A5", missing).Value2 = "4"
    Me.Range("A6", missing).Value2 = "5"
    Me.Range("A7", missing).Value2 = "6"
    Me.Range("A1", "A7").AutoFilter(Field := 1, Criteria1 := "Pin Numbere", [Operator] := Excel.XlAutoFilterOperator.xlFilterValues, VisibleDropDown := True)

    The below codes can filter with on Pin Number, you can update the codes according to your workbook.

    Thanks a lot 


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 9:20 AM
  • Hi Learning hard

    one thing which I had forgett to say Pin Number is Headline and the number should sort ascending.

    What I have to change in your last codeline that I get this result?

    Thanks

    Tuesday, April 23, 2013 10:47 AM
  • you can create workbook project by selecting your workbook, and you only use the below code :

    Me.Range("A6", "H6").AutoFilter(Field := 1, Criteria1 := "Pin Numbere", [Operator] := Excel.XlAutoFilterOperator.xlFilterValues, VisibleDropDown := True)

    If you aare very new for VSTO, I suggest you upload your entir project to skyDrive and share the link here, Then I can download the project and test it on my side. 

    Thanks  a lot .

     

    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 11:16 AM
  • Hi Learning hard

    I appreciate your work. I thought the Screenshoot of Excel Sheet indicate that are headline in use and the Numbers are ascending sorted.

    In the first codeline which I provide in this discussion was order ascending.

    I don't know why you are angry.

    I thought we are very close to solve this topic.

    How is your view

    Tuesday, April 23, 2013 11:41 AM
  • Hi Boy88,

    I suggest you  upload your project to SkyDrive and help me to solve your question quickly. I am not angry. I have another way to help you solve your question, you can record the macro and Go to Visual Basic Edit form, you can see the codes and then convert the VSTO codes which  is similar with VB.NET codes.

    Sub AutoFilterAscending
    
        Global.ThisWorkBook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
        Global.ThisWorkBook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=this.Range _
            ("A6:H6"), SortOn:=Excel.XlSortOn.xlSortOnValues, Order:=Excel.XlSortOrder.xlAscending, DataOption:= _
            Excel.XlSortDataOption.xlSortNormal
        With Global.ThisWorkBook.Worksheets("Sheet1").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    Tuesday, April 23, 2013 12:35 PM
  • Hi Learning hard

    Thanks for the makro hint. I thought also take a Makro code modify a littel bit an the stuff ir running. TI was thinking this unitl I am faced with autofilter too. The end of the storry I am in trouble:-((.

    Your code line dosent work. So far I understoog the global code line is not working.

    I have some fix but it result is not much better maybe you see something in me code proposal.

                Dim strWksName As String = "sheet1"
                rngDummy = CType(Globals.ThisWorkbook.Application.ActiveSheet.Range("A6:H6"), Excel.Range)
                Dim sheet As Worksheet = CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet)
                CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet).AutoFilter.Sort.SortFields.Clear()
                rngDummy.Select()
                rngDummy.AutoFilter(1, , , , True)
                CType(Globals.ThisWorkbook.Application.ActiveWorkbook.Worksheets(strWksName), Excel.Worksheet).AutoFilter.Sort.SortFields.Add(Key:=Range("E6"), SortOn:=Excel.XlSortOn.xlSortOnValues, Order:=Excel.XlSortOrder.xlAscending, DataOption:= _
                Excel.XlSortDataOption.xlSortNormal)
                sheet.AutoFilterMode = True
                With sheet
                    .AutoFilter.Sort.Header = XlYesNoGuess.xlYes
                    .AutoFilter.Sort.MatchCase = False
                    .AutoFilter.Sort.Orientation = Excel.Constants.xlTopToBottom
                    .AutoFilter.Sort.SortMethod = XlSortMethod.xlPinYin
                    .AutoFilter.Sort.Apply()
                End With

    • Marked as answer by Richard_MP Tuesday, April 30, 2013 3:19 PM
    Tuesday, April 23, 2013 2:15 PM
  • Have an look in this thread:

    How to convert VBA Autofilter code to VSTO code

    Cheers

    Boy 88

    Tuesday, April 30, 2013 3:19 PM