Forum Discussion

r0uladen's avatar
r0uladen
Copper Contributor
Dec 07, 2020

Opening Excel document from Sharepoint creates a second window

Hi When I try to open an Excel-file from Sharepoint it opens a second window (pic below). I think it has something to do with when you create macros in your personal workbook then Excel opens your...
excel
Macros and VBA
office 365
AWCombs's avatar
AWCombs
Copper Contributor
May 07, 2021

NemanSyedOfficial The easy way to fix this, or at least it worked for me.

  • Select the blank window
  • Click on 'Save As'
  • Copy the address to an explorer window (note the name of the file it is wanting to save as)
  • Close the empty excel window
  • delete the file in the XLSTART explorer window that was causing the blank window.

Good luck!

NemanSyedOfficial's avatar
NemanSyedOfficial
Copper Contributor
May 11, 2021

AWCombs thank you! As it turns out, it's not something I can delete - it's my Personal Macro Workbook!!

This isn't what I expected. I was expecting a temp file or some other form of detritus. I believe this must be some sort of bug that comes out of old tech + new tech. It's quite annoying, but I may have to live with it until someone at Microsoft finds it equally annoying enough to fix. Much appreciated!

  • AWCombs's avatar
    AWCombs
    Copper Contributor
    May 11, 2021

    NemanSyedOfficial If you don't need the macros with every excel workbook, you could probably cut it and paste to a personal folder elsewhere and manually open it when you need those macros.

    I hope you find a solution.

    Tony

    • NemanSyedOfficial's avatar
      NemanSyedOfficial
      Copper Contributor
      May 11, 2021
      Good idea, but it's my use-all-the-time goodies macro workbook. I find it really interesting this only happens with stuff that comes from SharePoint/OneDrive for Business and OneDrive, Maybe I'll write a macro just to toggle the hidden state of the newly-opened workbook. Hah!
      • NemanSyedOfficial's avatar
        NemanSyedOfficial
        Copper Contributor
        Oct 13, 2021

        I haven't solved this problem, but have a less-ugly workaround. In my personal.xlsb (the cause of, and solution to, many hours of grief in my life 😄 ) I created a new macro:

         

        Sub HideRedundantApplicationWindow()
' If you open Excel directly, i.e. not opening a workbook but just Excel,
' personal.xlsb is properly hidden. This is normal and good.

' If you open Excel indirectly by opening a locally saved workbook,
' personal.xlsb is properly hidden. This is normal and good.

' If you open Excel indirectly by opening a SharePoint- or OneDrive-hosted workbook,
' it opens two windows! One is for the workbook, and one appears blank.
' The blank one is actually personal.xlsb. That window shouldn't be visible.

' Toggling the visibility of the visible workbook's window solves this problem. (Why?)

' UI equivalent of this macro is:
' 1. From the blank window, choose View > Hide
' 2. View > Unhide > DesiredWorkbook.xlsx

' Assign this to the Quick Access Toolbar or give it a keyboard shortcut.

Dim MyWin As Excel.Window
Dim HiddenWindow As Excel.Window

    For Each MyWin In Application.Windows
        With MyWin
            'MsgBox .Caption & " is " & .Visible
            If .Visible = True Then
                Set HiddenWindow = MyWin
                HiddenWindow.Visible = False
                HiddenWindow.Visible = True
            End If
        End With
    Next MyWin

End Sub

        This is never a problem when Excel (with a personal.xlsb) is already open and you're opening an online file. It only happens when you open Excel (with a personal.xlsb) indirectly by opening the online file. So looping through all application windows in this way is arguably not necessary. Since there's only one that isn't personal.xlsb, you could just find and toggle that one.

         

        I haven't figured out how to make this work in the ThisWorkbook > Open event of personal.xlsb, so I've assigned it to QAT shortcut button and a keyboard shortcut. It happens every single time, so it's annoying enough to devote some real estate to the solution.

         

        Hope it helps someone!

Resources