locked
Select, Copy, and Re-Insert a range of Excel Rows Using C# RRS feed

  • Question

  • Hello Everyone.

     

    Using C# to replace an existing and now defunct legacy routine in Delphi, I’ve been trying re-develop a routine in C# that worked well in Delphi 7 that will open each of my Excel files, select the last 22 rows, copy them, and reinsert them back into their respective files.  For example, I find the last row, August of 2019 has 22 business days so I begin my range 22 rows back from the last row.  Using the Excel file, I want to select the range A4157 to A4178.  Then, I want to take the rows I’ve copied and insert them after row A4156 or before row A4157.  I then take a separate list of August dates and write them to each of the lower 22 from A4178 to A4200.  I retain the last row because additional calculations on other worksheets in the same workbook refer to the last row for data and will index automatically from A4178 to A4200 doing it this way.  As each entry made during the month it is copied down to the last row so the last row is always up to date.

     

    I’ve included the Main portion of the software.  The lines commented out are things I’ve tried that didn’t work as were many variations that I deleted and included here for info only.  The line with the * actually does open the test file and even highlights the range but displays the runtime error,

     

    Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'bool' to 'Microsoft.Office.Interop.Excel.Range''

     

    I’m not sure what that means. 

     

    My Opsys is Windows 10

    The C# is VS 17 (I just downloaded it from Microsoft this year.)

    MS Office Excel is new last fall and is not on the Cloud.

    My experience: CIS Michigan, C# 4 months.

     

    static void Main(string[] args)

            {

                var excelApp = new Excel.Application();

                excelApp.Visible = true;  // false;

                excelApp.DisplayAlerts = false;        

        

                // open Hours workbook

                string excelWorkBookPath = "C:\\Users\\UnknownUser\\EMPLOYEE\\AAA-TRYOUT\\empname_Hours.xls";

                // get all workbooksheets

                Excel.Sheets excelSheets = excelWorkBook.Worksheets;

     

                // Get a sheet

                string currentSheet = "Calculations";

                Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

     

                //Excel.Range firstRowCell = excelWorkSheet.Cells[A4157];

                //Excel.Range newPeriodRows = excelWorkSheet.Range[firstRowCell, lastRowCell];

                //Excel.Range selectRange = excelWorkSheet.Cells[A4157,A4178].select;

                //Excel.Range copyRange = excelWorkSheet.Range[excelWorkSheet.Cells[A4157], excelWorkSheet.Cells[A4178]].select;

     

             * Excel.Range selectRange = excelWorkSheet.Range[excelWorkSheet.Cells[A4157], excelWorkSheet.Cells[A4178].select;

                Excel.Range copyRange = excelWorkSheet.Range[excelWorkSheet.Cells[A4157], excelWorkSheet.Cells[A4178]].select;

     

                OpenEmployee(empNameArrayXs, cardArray);

                OpenDates(workDatrArry);    

               

                Marshal.ReleaseComObject(excelApp.Workbooks);

                //excelApp.Workbooks.Close();

                excelApp.Quit();

    I’ve been trying different approaches from many online and YouTube sources for over two weeks with no luck.  Any help will be greatly appreciated as would name(s) of comprehensive books or PDFs that deal with the subject of using C# and Excel integration.

     

    Thank you, Bill

    • Moved by CoolDadTx Wednesday, September 11, 2019 1:35 PM Office related
    Wednesday, September 11, 2019 1:28 PM

All replies

  • To:  Bill Kilgore
    Re:  C# problems

    I don't speak C#, I use the native code supplied with Excel (VBA) to program Excel.
    For what it is worth...
    Inserting data at a particular row location moves the existing data down.
    So copying rows 3 & 4 and inserting them below row 2 leaves you with the data appearing twice.

    So you end up with...
    row1 data
    row2 data
    row3 data
    row4 data
    row3 data
    row4 data

    It appears to me that you could add (or insert) your new data below existing data.
    Also, you may have difficulty finding C# experts in a forum dealing with Excel.


    Wednesday, September 11, 2019 4:39 PM
  • Nothing Left to Lose

    Believe me when I say I wish I could simply 'add on.'  As I said above,

    "...I want to take the rows I’ve copied and insert them after row A4156 or before row A4157.  I then take a separate list of August dates and write them to each of the lower 22 from A4178 to A4200.  I retain the last row because additional calculations on other worksheets in the same workbook refer to the last row for data and will index automatically from A4178 to A4200 doing it this way..."

    Inserting a copy of July (or at least 22 lines in the case of August 2019)  allows me to retain July's data and then change the bottom 22 lines lines to be re-dated to the business days in August.  The crux here is that many of the summaries on the following worksheet refer to data obtained from the  bottom line.

    Thank for your timely response, Bill






    Wednesday, September 11, 2019 8:31 PM
  • My question is not as complicated as it appears. Using Visual Studio C# 2017 I just want to,

    -Select 20 rows from an Excel worksheet.

    -Copy those 20 rows.

    -Re-insert the 20 copied above the 20 selected to force the bottom 20 down.

    thanks, Bill K

    Friday, September 13, 2019 5:38 PM