Excel Windowstate

Add-in Express™ Support Service
That's what is more important than anything else

Excel Windowstate
Open New Excel Workbook Minimised 
Michael Kaden


Guest


Hi Andrei,

I want to find out, that instead of using: 


AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = False
Code here
AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = True


I could "hide" the new wordbook and the actions on it by opening it minimized. I found something in the ADX Forum (Anthony Pitts 15.3.2017), but when I apply this in 

    wb = wbs.Open(DataFile, 0, True)

                Dim windowState = AddinModule.CurrentInstance.ExcelApp.Application.ActiveWindow.WindowState
                AddinModule.CurrentInstance.ExcelApp.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized


or 

  AddinModule.CurrentInstance.ExcelApp.Application.WindowState = Excel.XlWindowState.xlMinimized


I do not get any error, but the workbook (wb) still opens in the "normal" state. So how can i open the workbook in the minimized windowstate?

Thank you & kind regards

Michael
Posted 05 May, 2021 04:45:16 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

I believe we talked about this. If you put these two lines (only them) in the Click event of a test Ribbon button, you'll see the window minimized, correct?


Andrei Smolin
Add-in Express Team Leader
Posted 05 May, 2021 09:42:35 Top
Michael Kaden


Guest


Thank you Andrei,

yes that works. I should have been more explicit, sorry. I am opening an existing workbook from a button in a userform, and I want that the Workbook is opening minimized.

I tried 


  Dim windowState = AddinModule.CurrentInstance.ExcelApp.Application.ActiveWindow.WindowState 
            AddinModule.CurrentInstance.ExcelApp.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized


In the click event of the button and also in the Workbook_open Event, but nothing works. The workbook always opens in the xlNormal WindowState.

thank you & kind regards

Michael
Posted 06 May, 2021 05:14:39 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

I think you won't get the workbook *open* minimized. I suggest minimizing the window after the workbook opens.

Michael Kaden writes:
ExcelApp.Application.ActiveWindow


1. Application is excessive. Moreover, we explain that you shouldn't access the Application property. Instead, you should use the ExcelApp property only. Below is a citation from the Add-in Express manual:

When an add-in loads, Office supplies it with a reference to the host application???s Application object which is the entry point to the host???s object model. The add-in module provides that reference through the HostApplication property and through host-specific auto-generated source code properties such as ExcelApp, OutlookApp and WordApp.

It is essential that you get an Application object of the add-in???s host application only through these properties.

Using any other way to obtain an Application object ??? via CreateObject(), New Application, or via the Application property available on all objects in all Office object models ??? may lead to issues. Specifically, Outlook ??? of all Office applications ??? regards the Outlook.Application object got via the OutlookApp (or HostApplication) property as safe. Otherwise, you get an unsafe Outlook.Application object. Using it to access protected Outlook properties and methods may end with security warnings.


2. The code line above may not return the window that you try to minimize. This is because the window in question may not be active. Try using {a Workbook object}.Windows.Item(1).WindowState = xlMinimized after you get a reference to the Workbook object.

In C# that would be {a Workbook object}.Windows[1].WindowState = xlMinimized.

Remember about the need to release COM objects that code line creates: avoid chaining COM calls; instead, store each COM object in a separate variable and release every such variable after use.


Andrei Smolin
Add-in Express Team Leader
Posted 06 May, 2021 09:26:32 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your help, and your points about "Application" property.

I think I got what I want, but there are some things I can not still understand.

I think it has something to do with me not being completely aware of the differences between WinForm and WPF User Control as ElementHost in a WinForm. I realised that if you want anything to happen before a WPF Button_Click Event finishes completely, you have to put a DoEvents in the Code.

With this I could get what I wanted 

Dim wbs As Excel.Workbooks = AddinModule.CurrentInstance.ExcelApp.Workbooks
Dim wb As Excel.Workbook = Nothing
Dim wsheet As Excel.Worksheet = Nothing
Dim DataFile as String ="C:_Work KundenVPC222 Project 01Data receivedSmall Book.xlsm"
     
wb = wbs.Open(DataFile, 0, False)
wb.Activate()

Dim windowState = AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState
AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized
AddinModule.CurrentInstance.ExcelApp.DoEvents()

For Each wsheet In wb.Worksheets
CB01.Items.Add(wsheet.Name)
Next
wb.Close(SaveChanges:=False)
If wsheet IsNot Nothing Then Marshal.ReleaseComObject(wsheet)
If wb IsNot Nothing Then Marshal.ReleaseComObject(wb)
If wbs IsNot Nothing Then Marshal.ReleaseComObject(wbs)


It works as intended, but on very large files the File.Open takes a few seconds before the Workbook is minimized, and therfore shows up in the WindowStare xlNormal. I can prevent that by wrapping the File.Open in a screenUpdating 

AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = False
     
wb = wbs.Open(DataFile, 0, False)
wb.Activate()

Dim windowState = AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState
AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized
AddinModule.CurrentInstance.ExcelApp.DoEvents()
AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = True


As far as I can see, this works perfect and does exactly what I want, however, now the strange observation. The Workbook (wb) never comes up anywhere in the task manager. Even if I comment out: 

'wb.Close(SaveChanges:=False)
'If wsheet IsNot Nothing Then Marshal.ReleaseComObject(wsheet)
'If wb IsNot Nothing Then Marshal.ReleaseComObject(wb)
'If wbs IsNot Nothing Then Marshal.ReleaseComObject(wbs)


I put sleep and/or breakpoints in to check the Task Manager - nothing there!But it still writes all the correct sheet Names into the CB01 combobox?

Do you have an explanation for that?

Thank you very much for your help & kind regards.

Michael
Posted 06 May, 2021 11:11:05 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
put a DoEvents in the Code


This opens a door to code reenterability. Imagine an event occurring wile your code waits for something: this can break the logic of your add-in.

In the Add-in Express manual, we wrote this:

In our practice, DoEvents is the cause of a terrific number of issues. Being developers ourselves, we used DoEvents in our add-ins until we realized that DoEvents is evil! Use the SendMessage method to wait for a little while. Use a timer to wait for a big while. Of all timers we recommend using System.Windows.Forms.Timer: its event occurs on the thread where the timer was created.


That is, you need to do this in steps:
- open the workbook, if this takes a lot of time, set ScreenUpdating = true before opening the workbook.
- when the workbook opens (WindowActivate?), minimize it and set ScreenUpdating = false; maybe, you'll need to call these in the reverse order.
- when this is done, wait for a little while (to let Excel redraw itself) and start processing the workbook.


Andrei Smolin
Add-in Express Team Leader
Posted 07 May, 2021 08:16:42 Top
Michael Kaden


Guest


Thank you very much Andrei,

I was reading a lot about the problems with DoEvents. In the ADX Forum, the publications you recommended and also other publications but as far as I can see the DoEvemts are ok in the following scenario:

With a Ribbon Button, I open a WinForm with ElementHost WPF user Control modal (ShowDialog). I ensure that the userForm has no other Events like MousePressed or KeyUp etc. I have one button with a click_Event in which I use the DoEvents to change the Progress bar value. On starting the Click_Event I disable all other controls like Buttons, Combobox, TextBox etc. on the modal user Form. I only enable them again on End Sub or in the Error handling. I also only work on the Main UI thread, when this user Form is used.

It works very well, do you think there could be another problematic scenario? All the other trials I made, like splitting the task reading 20.000.000 cells from a workbook, also with send message, did not give me satisfactory user interface (Progress bar moving). Do you think DoEvents with the above mentioned component disabling is OK?

Now to the other question which I am really puzzled with. On the outset of this issue, i.e. reading many millions of cell values we wanted that this process is not visible to the user, with like opening and closing Excel Workbooks etc.

Andrei Smolin writes That is, you need to do this in steps:
- open the workbook, if this takes a lot of time, set ScreenUpdating = true before opening the workbook
.

As above, we do not want to see the workbook.

But we also wanted, that the user is not sitting in front of a "frozen" screen for minutes. This seemed to be really difficult, so we tried reading the data with OpenXML or ClosedXML from the UI thread or other threads. It works, but there were many problems for example with empty rows and columns etc. or variable type and also that ClosedXML is third party and OpenXML is not maintained by Microsoft did not encourage to use it. We also looked at getting the data through OLE. Finally we also did not succeed there.

With many weeks of trial and error we got this code which does exactly what we wanted in the first place, i.e. getting the cell values completely unnoticed by the user. Because there are so many posts stating that this is not possible, we tested over and over again. All with the same result:

the workbook where the data is extracted from:
... is not visible on the user interface
... is not visible on the task bar
.... is even not visible on the task manager
.... the cell values are read and transferred very fast and reliable

.... even if the Excel DataFile is already open, nothing happens on the user interface.


This is absolutely perfect and exactly what we wanted in the first place. However as there is so much written that this is not possible, I feel very uncertain about this and therefore kindly ask you to advise:

1.) Can you reproduce the behavior using the Code below, just as an example, reading the sheet names?
2.) Do you see any problem in doing it this way?
3.) It is clear, that the workbook opens, reads data and closes. Do you have any explanations why it does not show anywhere, not even in the Task Manager although the reading is done during ScreenUpdating = True? 

Private Sub BU01_Click(sender As Object, e As System.Windows.RoutedEventArgs) Handles BU01.Click 'Getting Files and sheets

   Dim XLopen As Boolean = False
   If SRM = True Then servb.LogWrite("XLSetUpWPF BU01_Click get Files and sheets start", True)
           'Start get the filename with BGW and XML

        'Disable all Buttons       
        BU01.IsEnabled = False

        'Disable CompboBoxes
        CB01.IsEnabled = False

   Dim openfile As New Forms.OpenFileDialog
   openfile.Filter = "Microsoft Excel|*.xls*"
   openfile.ShowDialog()
   DataFile = openfile.FileName

   TB01.Text = DataFile

   CB01.Items.Clear()

   Dim wbs As Excel.Workbooks = AddinModule.CurrentInstance.ExcelApp.Workbooks
   Dim wb As Excel.Workbook = Nothing
   Dim wsheet As Excel.Worksheet = Nothing
   AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = False

   For Each wb In wbs 'Check if File is open
       If wb.FullName = DataFile Then
           XLopen = True
           wb.Activate()
           Exit For
       Else
               
           wb = wbs.Open(DataFile, 0, False)
                wb.Activate()
              Dim windowState = AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState
                AddinModule.CurrentInstance.ExcelApp.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized
              
           Exit For
       End If
   Next
  AddinModule.CurrentInstance.ExcelApp.ScreenUpdating = True



   For Each wsheet In wb.Worksheets
       CB01.Items.Add(wsheet.Name)
   Next
   CB01.SelectedIndex = 0

'Here I could more reading from the workbook and also move the progress bar with DoEvents, as described above (modal form all components disabled) 


   If XLopen = False Then
       wb.Close(SaveChanges:=False)
   If wsheet IsNot Nothing Then Marshal.ReleaseComObject(wsheet)
   If wb IsNot Nothing Then Marshal.ReleaseComObject(wb)
   If wbs IsNot Nothing Then Marshal.ReleaseComObject(wbs)
   End If

   TB01.Text = DataFile

        'Enable Buttons
        BU01.IsEnabled = True

        'Enable all Text box Inputs
        TRF.IsEnabled = True

        'Enable CompboBoxes
        CB01.IsEnabled = True

    End Sub



If this is really a feasible way to get cell data in the background, I belive it would answer a lot of unsolved issues floating around on Google?

Thank you very much, kind regards & have a nice weekend

Michael
Posted 07 May, 2021 10:31:17 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
All with the same result


It's okay if it's okay for you. I dislike such solutions as they actually rely on this implicit assumption: the Excel build currently installed on your machine works in the very same way that all previous and future Excel builds work.


Andrei Smolin
Add-in Express Team Leader
Posted 10 May, 2021 09:21:25 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your comment. Sorry for the late reply, I was in hospital, luckily not Corona.

I agree with your comment fully, thank you. I believe it is a special challenge for anybody making AddIn's or Extensions to Windows or Office to ensure compatibility with future Microsoft versions. If this is possible at all. Hardware compatibility issues give additional headaches.

Thanks again & kind regards

Michael
Posted 20 May, 2021 02:16:45 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

No worries, I hope you are recovering well!


Andrei Smolin
Add-in Express Team Leader
Posted 20 May, 2021 03:35:05 Top