none
Is there a way to programmatically set Split in Excel using Interop? RRS feed

  • Question

  • I am exporting data to Excel in a Winforms app and would like to be able to Split to a particular cell so that when the user scrolls in either direction my row and column headers remain visible.  I haven't found a way to do that, just some references to a bool IWindow.Split property.

    Thanks.  Steve

    Thursday, November 17, 2016 9:04 PM

Answers

  • Hi,

    We could use Window.Split Property (Excel).

    E.g.

    using Excel = Microsoft.Office.Interop.Excel;
        Excel.Application xlApp;
                Excel.Workbooks xlWorkbooks;
                Excel.Workbook xlMyWorkbook;
                xlApp = new Excel.Application();
                xlApp.Visible = true;
                xlWorkbooks = xlApp.Workbooks;
                xlMyWorkbook = xlWorkbooks.Open(@"C:\test.xlsx");
                xlApp.ActiveWindow.SplitColumn = 2;
                xlApp.ActiveWindow.SplitRow = 2;
                xlMyWorkbook.Save();
                xlApp.Quit();

    Besides, I would suggest you check VBA references. Office Interop is similar to VBA.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    Friday, November 18, 2016 5:28 AM
    Moderator
  • Re:  keep some rows/columns visible using something called Interop.

    The term used in VBA is "FreezePanes".  Maybe that will be of help with with your search.
    The procedure in Excel is to select the cell in the top left corner and freeze the panes.
    The VBA code could look like...
        Range("B2").Select
        ActiveWindow.FreezePanes = True
    The above freezes row(1) and column(1).

    The definition of "ActiveWindow" is up for debate, now that the SDI has been implemented.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    Friday, November 18, 2016 5:32 AM

All replies

  • Hi,

    We could use Window.Split Property (Excel).

    E.g.

    using Excel = Microsoft.Office.Interop.Excel;
        Excel.Application xlApp;
                Excel.Workbooks xlWorkbooks;
                Excel.Workbook xlMyWorkbook;
                xlApp = new Excel.Application();
                xlApp.Visible = true;
                xlWorkbooks = xlApp.Workbooks;
                xlMyWorkbook = xlWorkbooks.Open(@"C:\test.xlsx");
                xlApp.ActiveWindow.SplitColumn = 2;
                xlApp.ActiveWindow.SplitRow = 2;
                xlMyWorkbook.Save();
                xlApp.Quit();

    Besides, I would suggest you check VBA references. Office Interop is similar to VBA.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    Friday, November 18, 2016 5:28 AM
    Moderator
  • Re:  keep some rows/columns visible using something called Interop.

    The term used in VBA is "FreezePanes".  Maybe that will be of help with with your search.
    The procedure in Excel is to select the cell in the top left corner and freeze the panes.
    The VBA code could look like...
        Range("B2").Select
        ActiveWindow.FreezePanes = True
    The above freezes row(1) and column(1).

    The definition of "ActiveWindow" is up for debate, now that the SDI has been implemented.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    Friday, November 18, 2016 5:32 AM
  • Thanks, Celeste.  That worked great.  Steve
    • Marked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    • Unmarked as answer by Cincy Steve Sunday, November 20, 2016 7:06 PM
    Sunday, November 20, 2016 7:05 PM
  • Thanks, James.  That also worked great.  Celeste's and your approaches are both viable.  Steve
    Sunday, November 20, 2016 7:06 PM