none
How I arrange two Excel instance side by side via vba code RRS feed

  • Question

  • Hi

    I would like arrange two Excel instance side by side. How can I do this?

    thanks


    • Edited by Richard_MP Wednesday, October 15, 2014 9:10 AM
    Wednesday, October 15, 2014 9:09 AM

Answers

  • You didn't say if you have references to both instances so I'll assume you do. 

    Sub SideBySide()
    Dim lt As Single, tp As Single, wd As Single, ht As Single
    Dim xl1 As Excel.Application
    Dim xl2 As Excel.Application
    
        Set xl1 = Application
        xl1.WindowState = xlMaximized
        xl1.ActiveWindow.WindowState = xlMaximized
        lt = xl1.Left
        wd = xl1.Width / 2
        ht = xl1.Height
        tp = xl1.Top
    
        Set xl2 = New Application
        ' load addins, Personal etc if/as required
        xl2.UserControl = True
        xl2.Workbooks.Add
        xl2.ActiveWindow.WindowState = xlMaximized
        xl2.WindowState = xlNormal
        xl2.Width = wd
        xl2.Left = lt + wd
        xl2.Height = ht
        xl2.Top = tp
    
        xl1.WindowState = xlNormal
        xl1.Left = lt
        xl1.Top = tp
        xl1.Width = wd
        xl1.Height = ht
        
        xl2.Visible = True
        xl2.Interactive = True
        AppActivate xl2.Caption
        AppActivate xl1.Caption
    
    End Sub
    Obviously you won't want to be starting a third instance so adapt to your existing references. Also depending on your scenario there will probably be a better way to limit changes on the screen.


    Wednesday, October 15, 2014 8:30 PM
    Moderator

All replies

  • Hello,

    Your question is a bit vague.

    You want to do this in what language? If you want to script it:

    Dim ea  As Excel.Application 
    Set ea = New Excel.Application

    Dim ea2  As Excel.Application
    Set ea2 = New Excel.Application

    ea.Visible = True  

    ea2.Visible = True

    This will open 2 excel applications.

    But probably this is just a dull answer?

    Wednesday, October 15, 2014 2:47 PM
  • Do you mean two instances of Excel or two workbooks in the same instance.  If you mean workbooks in different Excel instances which version, 2013 or earlier, and do you have references to both instances. If you are talking about workbooks in the same instance, do you only have the two workbooks open, or more than two but you only want to view two.

    Wednesday, October 15, 2014 3:02 PM
    Moderator
  • Hi

    I use Excel 2010. 

    I mean two excel instance. Each instance has a own workbook loaded. I would like achieve that both Instance  placed side by side on my desktop. No workbook placement side by side in one Excel instance.

    I hope now you have a better picture.

    Thanks  

    Wednesday, October 15, 2014 6:29 PM
  • You didn't say if you have references to both instances so I'll assume you do. 

    Sub SideBySide()
    Dim lt As Single, tp As Single, wd As Single, ht As Single
    Dim xl1 As Excel.Application
    Dim xl2 As Excel.Application
    
        Set xl1 = Application
        xl1.WindowState = xlMaximized
        xl1.ActiveWindow.WindowState = xlMaximized
        lt = xl1.Left
        wd = xl1.Width / 2
        ht = xl1.Height
        tp = xl1.Top
    
        Set xl2 = New Application
        ' load addins, Personal etc if/as required
        xl2.UserControl = True
        xl2.Workbooks.Add
        xl2.ActiveWindow.WindowState = xlMaximized
        xl2.WindowState = xlNormal
        xl2.Width = wd
        xl2.Left = lt + wd
        xl2.Height = ht
        xl2.Top = tp
    
        xl1.WindowState = xlNormal
        xl1.Left = lt
        xl1.Top = tp
        xl1.Width = wd
        xl1.Height = ht
        
        xl2.Visible = True
        xl2.Interactive = True
        AppActivate xl2.Caption
        AppActivate xl1.Caption
    
    End Sub
    Obviously you won't want to be starting a third instance so adapt to your existing references. Also depending on your scenario there will probably be a better way to limit changes on the screen.


    Wednesday, October 15, 2014 8:30 PM
    Moderator
  • Yes this works.

    Thanks

    Sunday, October 26, 2014 4:35 PM
  • Hi,

    I implemented the code above from Sub SideBySide() and it works perfect on my pc with Excel 2016.

    Although when I run the code on another pc with Excel 2013, I experience an error :

    Run time error 48

    Error in loading DLL

    The error occurs at line :

     Set xl2 = New Application

    My Excel-libraries are the same at both machines.

    Does anyone know what can be the cause?

    Thanks!

    Marcel


    MB

    Tuesday, January 9, 2018 1:26 PM
  • No idea but try these

    Set xl2 = New Excel.Application
    Set xl2 = CreateObject("excel.application")

    If those don't work looks like you've got a problem automating a new instance of Excel.

    If your second instance is already running and you know the name of a loaded workbook in it try this

    Set xl2 = GetObject("workbook-name").Parent

    Tuesday, January 9, 2018 4:41 PM
    Moderator
  • Thank you for your answer Peter.

    The first two commands give the same error.

    Although after deleting the line : " Dim xl2 As Excel.Application ", the second command is working! (Set xl2 = CreateObject("excel.application").

    Thanks for your support!

    regards,

    Marcel


    • Edited by marcel502 Wednesday, January 10, 2018 12:25 PM
    Wednesday, January 10, 2018 11:22 AM
  • Glad it's working but strange.

    Variables should always be declared and ideally as the expected object or data type, if not known As Object or Variant. What happens if you remove the Excel qualifier from the declaration, ie

    Dim xl2 As Application

    If that fails at the very least -

    Dim xl2 As Object

    Wednesday, January 10, 2018 12:50 PM
    Moderator
  • As Application still gives the error.

    As Object is working fine. I will change it to Object.

    Thanks


    MB

    Wednesday, January 10, 2018 1:10 PM
  • Still odd, one last thing for interest - next lines after CreateObject lets see what xl2 is at that moment

    Debug.Print TypeName(xl2)
    Debug.Print xl2.Name

    I would expect 'Application' and 'Microsoft Excel'

    Wednesday, January 10, 2018 1:27 PM
    Moderator
  • Yes it indeed returns :  'Application' and 'Microsoft Excel'

    thanks


    MB

    Wednesday, January 10, 2018 1:35 PM