none
how to use Autofill function in automation in C# project

    Question

  • Hi Everyone,

    Does someone try to call the Autofill function in C# automation project.

    What I try like this, but it fails. The problem is 

     

    An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

    Additional information: AutoFill method of Range class failed.

     

    My codes are;

     

    Excel.Worksheet xlsWorksheet = null;

    Excel.Range xlsRange = null;

     

    xlsRange = xlsWorksheet.get_Range("D1", "D1");

    xlsRange.FormulaR1C1 = "=RC[-1]/60";

    xlsRange.AutoFill(xlsWorksheet.get_Range("D2", "D4"), Excel.XlAutoFillType. xlFillCopy);

     

    When it runs AutoFill function, it prompted the above problem.

     

    I also tried to use 

    xlsApp.Selection.AutoFill(xlsWorksheet.get_Range("D2", "D4"), Excel.XlAutoFillType.xlFillCopy);

    but the Selection doesn't have the AutoFill function in fact.

     

    Who have solved this problem successfully, please tell me ASAP.

     

    Thank you.

     

    WEI

     


    cuiwei
    Wednesday, December 21, 2011 10:22 AM

Answers

  • The code below will work.  You didn't have a worksheet defined.  Also the source and destination range havve to overlap for the autofile to work.

     

    static void myautofill(Excel.Workbook excelbk)
            {
                Excel.Worksheet xlsWorksheet = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];
    
                Excel.Range xlsRange = null;
    
                xlsRange = xlsWorksheet.get_Range("D1:D2", Type.Missing);
                Excel.Range  dest = xlsWorksheet.get_Range("D1:D5", Type.Missing);
                xlsRange.FormulaR1C1 = "=RC[-1]/60";
    
                xlsRange.AutoFill(dest, Excel.XlAutoFillType.xlFillCopy);
    
    
            }

     


    jdweng
    Wednesday, December 21, 2011 11:28 AM

All replies

  • The code below will work.  You didn't have a worksheet defined.  Also the source and destination range havve to overlap for the autofile to work.

     

    static void myautofill(Excel.Workbook excelbk)
            {
                Excel.Worksheet xlsWorksheet = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];
    
                Excel.Range xlsRange = null;
    
                xlsRange = xlsWorksheet.get_Range("D1:D2", Type.Missing);
                Excel.Range  dest = xlsWorksheet.get_Range("D1:D5", Type.Missing);
                xlsRange.FormulaR1C1 = "=RC[-1]/60";
    
                xlsRange.AutoFill(dest, Excel.XlAutoFillType.xlFillCopy);
    
    
            }

     


    jdweng
    Wednesday, December 21, 2011 11:28 AM
  • Thank you  jdweng,

     

    In fact, I defined worksheet variable, because I created a new workbook and added a new worksheet.

    After getting the worksheet variable again, my codes work well.

     

    Thank you again.

     

    Wei


    cuiwei
    Wednesday, December 21, 2011 1:37 PM