none
C-Api, xlset and autofilter

    Question

  • Hello,

    I have in my xll following macro sheet equivalent:

    LPXLOPER12 prepare_snapshot () {
    	int i, len, iLastRow, iLastColumn;
    	XLOPERX xWorkbook, xWorksheetName, xWorksheet, xRef, xRef2;
    	XLMREF12 xlmref;
    
    	Excel12(xlfGetWorkbook, (LPXLOPER12)&xWorkbook, 1, TempNum(1));
    	xWorksheetName.xltype = xltypeStr;
    
    	len = xWorkbook.val.array.columns;
    
    	for(i = 0; i < len; i++) {
    		// set current sheet
    		xWorksheetName.val.str = xWorkbook.val.array.lparray[i].val.str; 
    		
    		Excel12(xlSheetId, &xRef2, 1, (LPXLOPER12)&xWorksheetName);
    		xRef.val.mref.idSheet = xRef2.val.mref.idSheet;
    		Excel12(xlFree, NULL, 1, (LPXLOPER12)&xRef2);
    		xRef.xltype = xltypeRef;
    		xRef.val.mref.lpmref = (LPXLMREF12)&xlmref;	
    
    		// LastRow
    		Excel12(xlfGetDocument,
    			(LPXLOPER12)&xWorksheet,
    			2, 
    			TempNum(10), 
    			(LPXLOPER12)&xWorksheetName);
    		iLastRow = (int)xWorksheet.val.num;
    		Excel12(xlFree, NULL, 1, (LPXLOPER12)&xWorksheet);
    
    		// LastColumn
    		Excel12(xlfGetDocument, 
    			(LPXLOPER12)&xWorksheet,
    			2,
    			TempNum(12),
    			(LPXLOPER12)&xWorksheetName);
    		iLastColumn = (int)xWorksheet.val.num;		
    		Excel12(xlFree, NULL, 1, (LPXLOPER12)&xWorksheet);
    		
    		if ((iLastColumn > 0) && (iLastRow > 0)) {
    			// Referenz erstellen
    			xlmref.count = 1;
    			xlmref.reftbl[0].rwFirst	= 0;
    			xlmref.reftbl[0].colFirst	= 0;
    			xlmref.reftbl[0].rwLast		= iLastRow - 1;
    			xlmref.reftbl[0].colLast	= iLastColumn - 1;		
    		
    			//set values
    			Excel12(xlSet, 
    				0, 
    				2, 
    				(LPXLOPER12)&xRef, 
    				(LPXLOPER12)&xRef);
    		}
    	}
    	Excel12(xlFree, NULL, 1, (LPXLOPER12)&xWorkbook);
    	return TempBool(1);
    }
    

    No I have a sheet with

    A1 = "h1", B1 = "h2", C1 = "h3",

    A2 = Today(), B2 = A2*10, C2 = A2*100

    A3 = A2 + 1, B3 = A3*10, C3 = A3*100

    ...

    A7 = A6 + 1, B7 = A7*10, C7 = A7*100

    I now set an autofilter for A1:C7 and execute prepare_snapshot  for this sheet,

    then all formulas are replaced by their values.

    If I now select a specifc value of the autofilter, eg the last one, then this doesn't work.

    For Rows 2 -6 the formula remains and A7 shows h1, B7 h2 and C7 h3.

    Is there a way to replace the formulas by their values also in this case.

    with best regards

      Hendrik

    Monday, December 10, 2012 3:55 PM

All replies

  • Hi Hendrik,

    Thanks for posting in the MSDN Forum.

    I will involve some experts into your issue to see whether they can help you. There might be some time delay, appreciate for your patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, December 11, 2012 2:22 AM
  • I think the problem is that getting the last row with GETDOCUMENT only gets the last VISIBLE row, so when rows are filtered out or hidden or zero height it gives you the "wrong" answer.

    I use a (cached) single-thread-locked call via COM to get the used range, because I want to get the used range from inside a multi-threaded XLL UDF to be able to handle full-column references: unfortunately I have not yet found an easier way.

    If you don't want to do this from a UDF you maybe could use FIND * to find the last cell containing a value.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/


    Tuesday, December 11, 2012 1:16 PM
  • In my now working example the last visible row was also the last used row.

    But since this is called from a shared addin ( written in C#) I thought of following idea.

    1)  Deselect all specifc autofilter selection

    2) call prepare_snapshot

    3) restore specifc autofilter selection.

    So i thought of following code in C#

    tmpwb = oExcelApp.ActiveWorkbook;
    
    int i, len = tmpwb.Worksheets.Count;
    // Worksheets is 1 based
    for (i = 1; i <= len; i++)
    {
    	tmpwb.Worksheets[i].AutoFilter.ShowAllData();
    }
    
    WorkbookParser.Start();
    // Worksheets is 1 based
    for (i = 1; i <= len; i++)
    {
    	tmpwb.Worksheets[i].AutoFilter.ApplyFilter();
    }
    

    WorkbookParser.Start() calls prepare_snapshot via Application.run (using Microsoft.Office.Interop.Excel).

    1) works at least for Excel 2007 and newer (ShowAllData() isn't recognized by Excel 2003)

    2) If 1 works, then the formulas are replaced by their values.

    3) didn't work (would be ApplyFilter()), since ShowAllData() destroys the filter selections and all items for a filter are read-only.

    Tuesday, December 11, 2012 1:31 PM
  • If you have access to Interop you can get the UsedRange property of the worksheet, from which you can get the last row (UsedRange.Row+UsedRange.rows.count-1) and column. This would also work for hidden and zero-height/width rows/columns as well as Filtered rows

    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Tuesday, December 11, 2012 2:29 PM
  • As I already said in my first reply.

    In my not working example I get the correct last row and the correct last column.

    The problem is that xlset has not the expected effect.

    btw:

    Even if the autofilter selection is done in such a way that row 2, 4-7 are hidden and all others are visible,

    I get the correct last row and the correct last column.

    Wednesday, December 12, 2012 9:50 AM
  • @Hendrik, Apologies, I misunderstood you. You are correct.

    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Wednesday, December 12, 2012 12:28 PM
  • Hi Hendrik,

    I took a look at this but I'm unsure what might be causing the problem.  My only suggestion is to open a support case with us to troubleshoot further.  Hopefully someone else in the community will have some ideas.


    Sharon M, Microsoft Online Community Support

    Friday, December 14, 2012 4:54 PM