Search code examples
excelexcel-2010office-interop

Sometimes the spreadsheet has the left side columns cut off, and you can't scroll to them

I'm using Excel COM Interop to automate creating spreadsheets, and sometimes the left side (columns A-H) of the spreadsheet is not visible and I can't scroll to the left. Columns B-E are intentionally hidden and there's a freeze pane at P4. This problem is inconsistent, it happens for only a few people, and doesn't happen every time.

Edit: The shift happens right after applying the freeze panes. Here's the snippet of code that adds the freeze pane.

inout Excel.Worksheet worksheet;
worksheet.Activate();
worksheet.Range[GetEditableArea()].Select();
worksheet.Application.ActiveWindow.FreezePanes = true;

and here's GetEditableArea()

function returns string ColumnRow;
ColumnRow = ColNumToString(COLUMN_OFFSET + 1) + str(ROW_OFFSET + 1);

COLUMN_OFFSET = 15 (the columns after this are dynamic) ROW_OFFSET = 3

I put dialog messages before each statement, and then watched the Excel spreadsheet getting created. The shift to the right is happening right after worksheet.Range[GetEditableArea()].Select();

Solution

  • You might find Window.Split rather than Freeze Panes to be more versatile for your requirement. More info here (VBA) and here (vb.net)

    If you persevere with Freeze Panes then clearly in use, to be of any use, your freeze point needs to be visible within the active window. If it isn't (which may be the case in your example depending on what ColumnRow returns) then the Select action will move it to centre window. You could use Application.Goto Range(?) which would set it as rightmost as it can. This may help. The issue would appear to be arranging for your required rows to be visible given the freeze point in any given situation.

    Before you set Freeze Panes you could use some of the following:

    ActiveWindow.ScrollColumn to more finely tweak overall column positions

    ActiveWindow.Zoom to reduce the zoom level to get more columns into view to accommodate the freeze point

    ActiveWindow.WindowState = xlMaximized to maximise the window area to get more columns into view

    Reduce column widths (Columns.AutoFit?) or Hide more columns to get the required columns into view

    Rearrange the columns in your model (temporarily or otherwise) to get the required columns into view