Excel screen updating problem
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13

Thread: Excel screen updating problem

  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Excel screen updating problem

    Hi

    This is a first post so I appreciate your patience!

    I am building an Excel COM addin in Visual C#. I have some methods which call other methods etc, each of which is doing something 'visible' within Excel such as hiding/showing rows, or copy/pasting values.

    At the start of the root method and each method which is called, I have entered the line:

    excelapp.Application.Screenupdating = false;

    and set it back to true at the end of the root method.

    But the screen still seems to be updating (e.g. user can see rows being hidden in step and the cutcopymode dotted border shows briefly)? Is it the case that in C# screenupdating is reset within called sub-methods? Or does anyone have any other ideas?

    Thanks!
    Ameet

  2. #2
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: Excel screen updating problem

    Welcome to the forums

    That depends, how are your methods using the Excel Interop? Could you post some code to clarify? (remember to use [code] tags)

    If the user is not meant to see the Excel sheet being edited, I would set the Visible property of the Excel application to false until the work has completed - but I'm not sure it would work in your case.
    It's not a bug, it's a feature!

  3. #3
    Join Date
    Jul 2011
    Posts
    6

    Re: Excel screen updating problem

    Happy to post the code... but it's long and probably a bit ugly to experienced C# programmers. How about I first try to clarify further what I'm aiming to do and if that's no help then I will post the code?

    - the excel file is basically a financial model
    - as part of the add-in, I have a task pane
    - in that task pane, I have a treeview box with each node/child-node corresponding to specific line(s) in the model
    - when the user expands/collapses the tree nodes, the relevant lines are hidden/shown and in some cases other operations are performed (e.g. formulae in certain lines are changed or existing entries are copied and pasted in as values)

    So, with regards the structure of the code:
    - the task pane class contains the node expand/collapse methods
    - these call methods from another class which contain all methods governing the behaviour of the model (and in fact some of those call other methods in other classes)

    I don't want to hide the application or workbook while running this code since I think that would appear strange from a user perspective - ideally, I just want the screen to stop updating when a user clicks on expand/collapse and then show the result (i.e. screenupdating false >> true).

    Does that help explain the query?

    thanks again!

  4. #4
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: Excel screen updating problem

    I've just created this tiny test-app, which seems to work.
    Although I've worked with Excel Interop before, I've never used the ScreenUpdating property.

    It seems to work using my sample code:

    Code:
                Excel._Application excelApp = new Excel.Application();
                object missing = Type.Missing;
                excelApp.ScreenUpdating = false;
                excelApp.Visible = true;
                Excel.Workbook book = excelApp.Workbooks.Add(missing);
                Excel.Worksheet sheet = book.Worksheets.Add(missing, missing, missing, missing);
                sheet.Cells[1, 1] = "Test";
                System.Threading.Thread.Sleep(2000);
                excelApp.ScreenUpdating = true;
    It's not a bug, it's a feature!

  5. #5
    Join Date
    Jul 2011
    Posts
    6

    Re: Excel screen updating problem

    thanks. Yep, I can get that to work. The problem arises when another method is called from within that one sometime after the ScreenUpdating = False line... So for example, instead of

    Code:
    sheet.Cells[1,1] = "test"
    something like:

    Code:
    MyMethod(Excel.range rng, string text)
    Does that make sense?

  6. #6
    Join Date
    Jul 2011
    Posts
    6

    Re: Excel screen updating problem

    ps, note that within 'MyMethod' in the example above, I also have excelapp.ScreenUpdating = false there

  7. #7
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: Excel screen updating problem

    Can you replicate it using a test-project like the one I posted?

    I tried adding a button to my test-form, which sets the ScreenUpdating property. It also seems to work just fine:

    Code:
    public partial class Form1 : Form
    {
        private Excel._Application excelApp;
        private Excel.Workbook book;
        private Excel.Worksheet sheet;
    
        public Form1()
        {
            InitializeComponent();
        }
    
        private void Form1_Load(object sender, EventArgs e)
        {
            excelApp = new Excel.Application();
            object missing = Type.Missing;
            excelApp.ScreenUpdating = false;
            excelApp.Visible = true;
            book = excelApp.Workbooks.Add(missing);
            sheet = book.Worksheets.Add(missing, missing, missing, missing);
            sheet.Cells[1, 1] = "Test";
            System.Threading.Thread.Sleep(2000);
            excelApp.ScreenUpdating = true;
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
            excelApp.ScreenUpdating = false;
            sheet.Cells[1, 2] = "Test";
            System.Threading.Thread.Sleep(4000);
            excelApp.ScreenUpdating = true;
        }
    }
    It's not a bug, it's a feature!

  8. #8
    Join Date
    Jul 2011
    Posts
    6

    Re: Excel screen updating problem

    You're right - when I break it down into a simple test project, it seems to work ok. Will have to look into why it's not working in my app...

    Thanks for your help. For your reference, the type of scenario in which I though it was not working was:

    Code:
    public partial class Form1 : Form
        {
            private Excel._Application excelApp;
            private Excel.Workbook book;
            private Excel.Worksheet sheet;
    
            
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
    
            }
    
            private void button1_Click_1(object sender, EventArgs e)
            {
                excelApp = new Excel.Application();
                excelApp.Visible = true;
    
                book = excelApp.Workbooks.Add();
                sheet = book.Worksheets.Add();
            }
    
    
            private void button2_Click(object sender, EventArgs e)
            {
                excelApp.ScreenUpdating = false;
                object missing = Type.Missing;
    
                for (int i = 1; i < 50; i++)
                {
                    for (int j = 1; j < 20; j++)
                    {
                        sheet.Cells[i, j] = "test"; 
                    }
                }
    
                OtherClass.SubMethod(excelApp, sheet);
    
                excelApp.ScreenUpdating = true;
            }
    
        }
    where there is another class as follows:

    Code:
    class OtherClass
        {
    
            public static void SubMethod(Excel._Application excelApp, Excel.Worksheet wks)
            {
                excelApp.ScreenUpdating = false;
    
                for (int i = 1; i < 50; i++)
                {
                    for (int j = 1; j < 20; j++)
                    {
                        wks.Cells[i, j] = "replace";
                    }
                }
    
                excelApp.ScreenUpdating = true;
    
            }
    
    
        }

  9. #9
    Join Date
    Nov 2014
    Posts
    5

    Re: Excel screen updating problem

    I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

    How to Avoid Screen Updating in Excel.compressed (1).pdf

  10. #10
    Join Date
    Nov 2014
    Posts
    1

    Re: Excel screen updating problem

    upppppppppppppppppppppp phụ chủ thớt

  11. #11
    Join Date
    Nov 2014
    Posts
    5

    Re: Excel screen updating problem

    I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

    How to Avoid Screen Updating in Excel.compressed (1).pdf

  12. #12
    Join Date
    Nov 2014
    Posts
    5

    Re: Excel screen updating problem

    I have encountered a similar problem with Screen Update. I resolved it. I attachment has the solution with screenshots.

    How to Avoid Screen Updating in Excel.compressed (1).pdf

  13. #13
    Join Date
    Nov 2014
    Posts
    5

    Re: Excel screen updating problem

    I have encountered a similar problem with Screen Update. I resolved it. I will explain how to handle the screen updates using macro. We can speed up our macro by turning off the screen updating while the macro runs, by adding the below line of code to the macro:

    Sub Macro () Application.ScreenUpdating = False
    Write the necessary code Here
    覧覧覧-
    覧覧覧- Application.ScreenUpdating = True End Sub.

    The prior versions of excel 2000, it was not required to turn Screen Updating back to true. When the macro is finished, Excel would revert back to turning the screen updating to true.
    But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
    Application.ScreenUpdating = True

    How to Avoid Screen Updating in Excel.compressed (1).pdf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center