开发者

Error - Index refers beyond end of list

开发者 https://www.devze.com 2022-12-13 20:36 出处:网络
Problem saving file back to SharePoint with Excel COM lib. I open the file but it opens as \'xlviewer.xlsx\' so I assume I need to overwrite the original file.(I removed the try/catch to do some error

Problem saving file back to SharePoint with Excel COM lib. I open the file but it opens as 'xlviewer.xlsx' so I assume I need to overwrite the original file. (I removed the try/catch to do some error handling.) When I do I get this error: "Index refers beyond end of list" (error at line: 42 'SaveAs statement')

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace CopyInteractionIds
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application oApp;
            Excel._Workbook oWbFrom;
            Excel._Workbook oWbTo;
            Excel._Worksheet oWsFrom;
            Excel._Worksheet oWsTo;
            Excel.Range oRngFrom;
            Excel.Range oRngTo;

            //try
            //{
                oApp = new Excel.Application();
                oApp.Visible = true;
                Console.WriteLine("Opening source workbook");
                oWbFrom = (Excel._Workbook)(oApp.Workbooks.Open(@"path to my source wb"
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
                Console.WriteLine("Opening edited workbook");
                oWbTo = (Excel._Workbook)(oApp.Workbooks.Open(@"https://path to my out wb"
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
                Console.WriteLine("Copying data");
                oWsFrom = (Excel._Worksheet)oWbFrom.Worksheets.get_Item("Interaction Number Lookup");
                oWsTo = (Excel._Worksheet)oWbTo.Worksheets.get_Item("Interaction Number Lookup");
                oRngFrom = oWsFrom.get_Range("=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))",Missing.Value);
                oWsTo.get_Range("=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))", Missing.Value).Clear();
                oRngTo = oWsTo.get_Range("A1",Missing.Value);
                oRngFrom.Copy(oRngTo);
                Console.WriteLine("Saving edited file");
                oWbTo.SaveAs(@"path to out file"
         开发者_StackOverflow社区           , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    ,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oWbTo.Close(false,Missing.Value,Missing.Value);
                oWbFrom.Close(false, Missing.Value, Missing.Value);
                oApp.Quit();
            //}
            //catch(Exception ex)
            //{
            //    Console.WriteLine("Unable to process job: {0}",ex.Message);
            //}

        }
    }
}


I had a similar issue, it turned out my files were "damaged". Opening them all, having Excel repair them and then overwriting the damaged files seemed to do the trick for me.


Someone else had the file open.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号