Set NewApp = CreateObject("Excel.application") does not perform Excel's normal initialization steps.

What's the purpose of the SecondMonitor macro?

Are you going to be automating Excel to carry out some processing/tasks or is it just to open another instance of Excel?

to open excel we need to create three objects

             Dim app,book,sheet As Object
             Set app= CreateObject("Excel.Application")
             Set book= app.Workbooks.Open("d:\Inventory.xls")
             app.Visible = True
             Set sheet= app.ActiveWorkbook.Worksheets("Sheet1")

Select allOpen in new window

Vijaya Kumar, that is (a) irrelevant and (b) not true. "Open Excel" means nothing else than starting the application, there is no need for a workbook. Excel can run without having a workbook opened.

rberke, I think opening Excel with Office Automation does not trigger any automatic action by purpose. If you need to simulate a user opening Excel, start the application wit Run and corresponding commandline parameters - but that will leave you without access to the internals of Excel.

Excel 2013 and Excel 2016 work great on dual monitors.  You can easily put a workbook on monitor 1 and drag a different workbook to monitor 2.

Unfortunately, our company has many computers with Office 2010 which does not support that feature.   The people using office 2010 are a little annoyed, so I wrote SecondMonitor which simulates the feature. The macro closes the activeworkbook and opens it in a new application, which can be dragged.  

This macro is located in ...\xlstart\global.xla   which is a repository for many company macros.

SecondMonitor's main code is

    Set NewApp = CreateObject("Excel.application")
    
    NewApp.Visible = True
    
    On Error Resume Next ' some users do not have a "personal.xls" workbook, so we need to suppress errors
    NewApp.Workbooks.Open NewApp.StartupPath & "\" & "global.xla" 'allow EFG macros to work in the newApp.
    NewApp.Workbooks.Open NewApp.StartupPath & "\" & "personal.xls" 'allow EFG macros to work in the newApp

    On Error GoTo 0
NewApp.Workooks.open    TheWorkbookThatIsBeingMoved

Select allOpen in new window

I can easly modify it to loop and open every file in  the startup path, but I conjecture that there are other Excel startup functions at are being missed. For instance perhaps addins are not being loaded?  



Actually Launching Excel "1 by hand" , "2 by Explorer" and "3 programatically" are 3 different things.
1 Opens Excel, initializes it and loads a default template
2  Opens Excel, initializes it and loads the desired workbook that was double clicked
3  Opens Excel and nothing else.  

My desire is to make my code work like #2.
ideally there would be a method like Newapp.initialize that I could call.

But, I am probably wasting everbody's time with this.  I will probably close the problem soon.

rberke

@qlemo Author needs to open

 NewApp.Workbooks.Open NewApp.StartupPath & "\" & "global.xla" 'allow EFG macros to work in the newApp.
 NewApp.Workbooks.Open NewApp.StartupPath & "\" & "personal.xls" 'allow EFG macros to work in the newApp

this is enough ?????

 Set app= CreateObject("Excel.Application")

Norie. Good idea !

 retval = Shell("Excel  myfile.xls",vbNormalFocus) opens Excel just like a double click with all initialization.


But, to convert my routine is not quite that straight forward because I have more code.  For instance:


 newapp.ActiveWorkbook.Saved = True   ' <=== not too important, but handy for workbooks with volatile functions.
 newapp.Application.WindowState = xlNormal  but sometimes xlMaximized ' <=== important.

Some other issues are too complicated to explain.
Nonetheless, I think all the issues can be overcome eventually, but it will have to wait until I have some more time.

I am closing the problem, and thanks for everybody's help.

Excel 2013 and Excel 2016 work great on dual monitors.  You can easily put a workbook on monitor 1 and drag a different workbook to monitor 2.

Unfortunately, our company has many computers with Office 2010 which does not support that feature.   The people using office 2010 are a little annoyed, so I wrote SecondMonitor which simulates the feature. The macro closes the activeworkbook and opens it in a new application, which can be dragged.  

Do you know that you can instanciate a new excel application by maintaining the shift key while clicking on the excel icon ?

It might be possible to interact with the new instance created by shell("Excel TheFile.xls").
For instance I could easily modify the following code to pick the handle of the new instance.

Then, if I was smart enough, I could use the new handle to get to the actual NewApp application which could then be manipulated (e.g. newapp.saved = true)

But, I don't have time to pursue it.
Plus, I do not think it will be necessary to go to such lengths. I'll know more next week.

Public Sub AllRunningApps()
    Dim strComputer
    Dim objServices As Object, objProcessSet As Object, p As Object

    strComputer = "."

    Set objServices = GetObject("winmgmts:\\" _
        & strComputer & "\root\CIMV2")
    Set objProcessSet = objServices.ExecQuery _
        ("SELECT Name FROM Win32_process", , 48)

    For Each p In objProcessSet
        If InStr(1, p.name, "excel", 1) > 0 Then
Dim prop As Variant
            For Each prop In p.Properties_
                Debug.Print prop.name & " " & prop.Value
                Exit For
            Next
        End If
    Next
End Sub

Select allOpen in new window

Also, I did know about the shift click trick.  But it is not helpful to the users that want to drag a workbook to a second monitor. They should not need to know anything about instances.

This "trick" isn't about instance, but standard Windows functionality.....

Yes, standard windows functionality that is used to create a new instance. And not just Office, but virtually any program.

But, now that office 2013 and 2016 no longer need a second instance, I doubt if there are many people that ever use the shift click trick.

By the way, a year ago, I told our office 2010 users that dual monitors were easy. "Just close your worksheet, open it in a new instance of excel then drag it."  A few users understood and would use it for a while then stop because it was too annoying.  As far as I can tell, a year later nobody even remembered the concept.  Maybe other companies had different results under office 2010, but not here.  

Then I created the SecondMonitor macro, and all the office 2010 users now use it daily.

I will keep asking the boss to consider upgrading everybody to newer versions of office, but it is a not his priority.

Bob

Well, from what I understood, you can give an optional parameter to your "SecondMonitor" function (with vbNullString as default value), wich will be the path of the desired workbook.

If the parameter's value remain vbNullString, attempt to open global.xla or personnal.xls.
Else, open the desired workbook.
Something like:

Public Sub SecondMonitor(Optional ByVal path As String = vbNullString)
    If (Path = vbNullString) Then
        '// code to open global.xla or personnal.xls
    Else
        NewApp.Workbooks.Open path
    End If
End Sub

Select allOpen in new window