none
AutoFilterMode not working on deployed computer RRS feed

  • Question

  • Hi fellow professionals,

    I am completely puzzled by this situation.

    I wrote a program to process excel files.

    I have 4 tables to populate on each worksheet across 13 worksheets.

    What happen is the code runs beautifully on my computer. Every data required gets populated.

    When I port the EXACT same code over to the deployment computer, only the 1st table of all 13 worksheets has data. Table 2, Table 3 and Table 4 of all 13 worksheets has no data.

    Based on my professional debugging logic, I identify that it was the "AutoFilterMode=false" that is not working. Because I need to clear the filter to get the next set of data.

    [Here's what I did but to no avail]

    - Updated Microsoft Excel via Windows Update
    - Got the tech guys to give me administrator rights so as to be able to access the C drives DLLS.

    But nothing is working and it's puzzling me!

    Does any professional out there know what else could be the issue ?

    Thursday, April 20, 2017 2:01 AM

Answers

  • Hi Icychemist,

    Have you added any Add-Ins in your Excel, that deployed computers don't have?

      (this screenshot is from Excel 2016: I don't have Excel 2013)
    In case it helps, check version of .NET Framework (You must have done it already).
    ____________________
    Ashidacchi
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Thursday, April 20, 2017 4:09 AM
  • Hi Icychemist,

    I guess your issue causes from Excel environment, instead of Visual Studio. So, I asked you to check Excel Add-Ins.
    ___________
    Ashidacchi
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Thursday, April 20, 2017 8:59 AM
  • Oh my god.

    My focus was fixated on the wrong problem because I was anchoring on the fact that it works on my computer, but not the deployed computer, then something must be wrong with the system, not the code.

    The problem turned out to be date time issue.

    My autofilter filter Date for my 2nd 3rd and 4th table. That explain why it they always doesn't appear.

    Do anyone happen to know how to format DateTime properly?

    Here's my current code:

    rangeOverall.AutoFilter(6, "<" + DateTime.Today);

    This line works fine on my computer, Windows 10.
    But it is not working on the deployed computer, Windwos 7.

    Any idea?

    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Friday, April 21, 2017 1:39 AM
  • Hi Icychemist,

    Please check if "Formats" and "Location" in Region (Control Panel) are the same between your computer and deployed one.

    and I recommend you to make a test program and to check how date-time is displayed on Excel sheet in your computer and deployed one.
    _____________
    Ashidacchi

    P.S.
      displayed format on Excel sheet depends on "Date and time" in Control Panel. i.e. it is varied by setting of "Date and time". 

    • Edited by Ashidacchi Friday, April 21, 2017 2:09 AM
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Friday, April 21, 2017 1:52 AM
  • string systemDateFormat = System.Globalization.CultureInfo.DateTimeFormat.ShortDatePattern;

    Got the answer !

    Thanks !!

    • Marked as answer by Icychemist Friday, April 21, 2017 3:05 AM
    Friday, April 21, 2017 3:05 AM

All replies

  • Hi,

    Please provide 
    (1) version and build number of Excel and Windows
    (2) your code
    ______________
    Ashidacchi
    Thursday, April 20, 2017 2:07 AM
  • My Version of Excel: 
    Office Home and Student Excel 2013 15.0.4911.1000
    MSO 15.0.4911.1000

    Deployed Computer's Version of Excel:
    Office Standard 2013 15.0.4919.1000
    MSO 15.0.4911.1000

    My Code:

                    //861 FILTER RESULT  
                    if (worksheetOne.AutoFilter != null)
                    {
                        worksheetOne.AutoFilterMode = false;
                    }
    
                    Excel.Range rangeOverall = worksheetOne.UsedRange;
                    rangeOverall.AutoFilter(7, "861");
                    rangeOverall.AutoFilter(8, "Click and Collect");
                    rangeOverall.Sort(rangeOverall.Columns[6], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing,
                                    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending,
                                    Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns,
                                    Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal,
                                    Excel.XlSortDataOption.xlSortNormal);
    
                    Excel.Range filteredRange861CAC = worksheetOne.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
                    int numberOfRows861CAC = 0;
    
                    foreach (Excel.Range row in filteredRange861CAC.Rows)
                    {
                        numberOfRows861CAC += 1;
                    }
    
                    //861 COPYING FROM FILTERED RESULT TO BRANCH DAILY 861 WORKSHEET
                    filteredRange861CAC.Copy();
                    WKSBDT861WG.Paste(WKSBDT861WG.Cells[9, 1]);
    
                    #endregion
    
                    #region LATE ORDERS
    
                    //861 DETERMINE WHICH ROW NUMBER FOR TO INSERT LATE ORDER INFORMATION INTO
                    int writeIntoRowNumber861LO = 9 + numberOfRows861CAC + 2;
    
                    //861 FILTER RESULT
                    if (worksheetOne.AutoFilter != null)
                    {
                        worksheetOne.AutoFilterMode = false;
                    }
    
                    rangeOverall = worksheetOne.UsedRange;
                    rangeOverall.AutoFilter(6, "<" + DateTime.Today);
                    rangeOverall.AutoFilter(7, "861");
                    rangeOverall.AutoFilter(8, "Standard Delivery");
                    rangeOverall.Sort(rangeOverall.Columns[6], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing,
                                    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending,
                                    Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns,
                                    Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal,
                                    Excel.XlSortDataOption.xlSortNormal);
    
                    Excel.Range filteredRange861LO = worksheetOne.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
                    int numberOfRows861LO = 0;
    
                    foreach (Excel.Range row in filteredRange861LO.Rows)
                    {
                        numberOfRows861LO += 1;
                    }
    
                    //861 COPYING FROM FILTERED RESULT TO BRANCH DAILY 861 WORKSHEET
                    filteredRange861LO.Copy();
                    WKSBDT861WG.Paste(WKSBDT861WG.Cells[writeIntoRowNumber861LO, 1]);
    

    The one in bold is where I thought the problem is at. 

                  
    Thursday, April 20, 2017 2:34 AM
  • Hi Icychemist,

    # I cannot know where the one in bold is.

    Is each copied range filtered corrct? 
     //861 COPYING FROM FILTERED RESULT TO BRANCH DAILY 861 WORKSHEET
     filteredRange861LO.Copy();
    What do you see on the sheet just after the above line is executed?
    ______________
    Ashidacchi
    Thursday, April 20, 2017 3:08 AM
  • Hi Ashidacchi.

    I only see 1 row of data, which is the header of the table. There no longer is data.

    However, the exact same code works fine on my developer computer.

    Some people are suggesting it could be the dll files problem and asked me to add the dll files into my project and then reference it from there directly.

    I have tried so many ways but to no avail. I have been stuck with this issue for 2 days and more, fighting to get this resolved as soon as possible.

    Any insights from you side ?

    Thursday, April 20, 2017 3:29 AM
  • Hi Icychemist,

    Have you added any Add-Ins in your Excel, that deployed computers don't have?

      (this screenshot is from Excel 2016: I don't have Excel 2013)
    In case it helps, check version of .NET Framework (You must have done it already).
    ____________________
    Ashidacchi
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Thursday, April 20, 2017 4:09 AM
  • Hi Ashidacchi,

    Still meddling with the issue since 3 hours ago. I thought what you suggested was the solution for it, but unfortunately it didn't solve the problem....

    I included the Add-in for Visual Studio Tools for Office Design-Time as I noticed that the deployed computer didn't have it added. Then I also noticed that this Add-in didn't "Load on Startup". It could be a problem since my program generates the excel file on the fly. Hence, I went on to google and edited the registry to make it Load on Startup. But still, it is not working as it should be.

    Any other clues you might have ?

    Thursday, April 20, 2017 8:14 AM
  • Hi Icychemist,

    I guess your issue causes from Excel environment, instead of Visual Studio. So, I asked you to check Excel Add-Ins.
    ___________
    Ashidacchi
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Thursday, April 20, 2017 8:59 AM
  • One discovery I made is that....

    On the deployed computer, AutoFilterMode = false; seems to be working ONLY the first time on a new worksheet.

    Whenever there is a new worksheet, the first table gets populated. But not Table 2, 3 and 4 of every worksheet despite having AutoFilterMode = false; before every table.

    Thursday, April 20, 2017 11:51 AM
  • Oh my god.

    My focus was fixated on the wrong problem because I was anchoring on the fact that it works on my computer, but not the deployed computer, then something must be wrong with the system, not the code.

    The problem turned out to be date time issue.

    My autofilter filter Date for my 2nd 3rd and 4th table. That explain why it they always doesn't appear.

    Do anyone happen to know how to format DateTime properly?

    Here's my current code:

    rangeOverall.AutoFilter(6, "<" + DateTime.Today);

    This line works fine on my computer, Windows 10.
    But it is not working on the deployed computer, Windwos 7.

    Any idea?

    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Friday, April 21, 2017 1:39 AM
  • Hi Icychemist,

    Please check if "Formats" and "Location" in Region (Control Panel) are the same between your computer and deployed one.

    and I recommend you to make a test program and to check how date-time is displayed on Excel sheet in your computer and deployed one.
    _____________
    Ashidacchi

    P.S.
      displayed format on Excel sheet depends on "Date and time" in Control Panel. i.e. it is varied by setting of "Date and time". 

    • Edited by Ashidacchi Friday, April 21, 2017 2:09 AM
    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    Friday, April 21, 2017 1:52 AM
  • thank you so much Ashidacchi for staying with me through out this challenging mountainous terrain...

    I got around the problem by checking for the System's default DateTimeFormat.

    string [] systemDateFormat = DateTime.Today.GetDateTimeFormats('d');
    
    //And then set the date format into the excel column
    rangeShipmentDate.EntireColumn.NumberFormat = systemDateFormat[0];
    
    
    
    
    

    It's is resolved.

    THANK YOU SO MUCH !! ARIGATO..

    • Marked as answer by Icychemist Friday, April 21, 2017 2:31 AM
    • Unmarked as answer by Icychemist Friday, April 21, 2017 3:01 AM
    Friday, April 21, 2017 2:30 AM
  • Hi Icychemist,

    You've got it! I feel very happy to hear that.
    Congratulations!!
    ______________
    Ashidacchi
    Friday, April 21, 2017 2:32 AM
  • I realise that code wasn't correct.

    It returns me "21/04/2017", I need it to return me "DD/MM/YYYY" so I can set the format into the excel.

    Do you happen to know how to achieve this ?

    Friday, April 21, 2017 3:00 AM
  • string systemDateFormat = System.Globalization.CultureInfo.DateTimeFormat.ShortDatePattern;

    Got the answer !

    Thanks !!

    • Marked as answer by Icychemist Friday, April 21, 2017 3:05 AM
    Friday, April 21, 2017 3:05 AM