Excel/VBA: Open New Workbook In New Window

3
votes

I am using Excel 2010, and am looking for a VBA script that will open a new workbook in a new window (such that I could, for example, place one workbook on each of 2 monitors).

I'd then place this VBA/macro on the ribbon and assign it a shortcut key. Thus, it'd work like CTRL+N, but the new workbook would open in a separate window/instance of Excel, instead of the same.

I've tried just using Shell ("excel.exe"), but I suppose since it is running from my PERSONAL.XLSB workbook, it then asks if I want to Read Only or Notify.

I just want CTRL+N functionality, but with the new window addition.

Thank you!

2
vbaexcel
Does using Workbooks.Add not provide the functionality you're looking for? – cherry_bueno
Cherry - you are correct, and in the same sense, Rory's answer also included this command. So thank you both! – Chase

2 Answers

3
votes

You can use this:

Sub NewApp()
    With CreateObject("Excel.Application")
        .Workbooks.Add
        .Visible = True
    End With
End Sub

but be aware that any automation of this sort won't load startup workbooks and add-ins by default.

3
votes

Alternate way to do the same thing, includes selecting the file you want to open:

Sub tgr()

    Dim strFilePath As String
    Dim xlApp As Object

    strFilePath = Application.GetOpenFilename("Excel Files, *.xls*")
    If strFilePath = "False" Then Exit Sub  'Pressed cancel

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open strFilePath

End Sub