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

Normally, when I open Excel,  the startup automatically opens every workbook that is in  C:\Users\Bob\AppData\Roaming\Microsoft\Excel\XLSTART\



But, I have an "SecondMonitor" macro that launches a second instance of excel by using  Set NewApp = CreateObject("Excel.application")  .

 
The second instance does not open xlstart files.  In fact, I suspect there are other startup steps are skipped, but that is conjecture.

One way to fix this problem for SecondMonitor to loop through the files using Dir or similar code, and I do not need any help with that code.

But I was wondering if there is a way to tell the second instance to do everything that Excel do if there was a normal startup?
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
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?
0
Fabrice LambertFabrice LambertCommented:
Hi,

Launching Excel "by hands" and "programatically" are 2 different things.
The former launch Excel and create a default workbook.
The later only launch Excel (without default workbook).

To reproduce launching Excel "by hands", you should open the default workbook as well (guess it comes from a template).
Dim app As Object
Set app = CreateObject("Excel.application")

Dim wb As Object
Set wb = app.Workbooks.Add("path to your default Template")

Open in new window

0
Vijaya KumarCommented:
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")

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
rberkeConsultantAuthor Commented:
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

Open 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
0
Vijaya KumarCommented:
@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")
0
NorieVBA ExpertCommented:
If you want/need to open Excel as if you've double clicked its shortcut perhaps you should try using Shell?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:
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.
0
Fabrice LambertFabrice LambertCommented:
I'm not sure that you'll be able to interract with the opened workbook if you open it via the shell.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No, you can't, besides using SendKeys or similar.
0
Fabrice LambertFabrice LambertCommented:
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 ?
0
rberkeConsultantAuthor Commented:
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

Open in new window

0
rberkeConsultantAuthor Commented:
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.
0
Fabrice LambertFabrice LambertCommented:
This "trick" isn't about instance, but standard Windows functionality.....
0
rberkeConsultantAuthor Commented:
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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then you'll need to run the startup stuff yourself after opening, as shown above, or start an own instance with the document, having less control.
0
Fabrice LambertFabrice LambertCommented:
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

Open in new window

0
rberkeConsultantAuthor Commented:
Good news and bad news.
The good news first: I can get "normal initialization" by using  Shell("Excel " & chr(34) & desiredfullname & chr(34), vNormalFocus)  

The bad news is that I now realize I DO NOT WANT NORMAL INITIALIZATION !!.

I had forgotten that my global.xla modules use Onkey to allow for some important shortcut keys at our installation.

The second instance always gives an error when it hits that instance because the other instance has priority.  

I now have a solution to my original question, but I will not use it.  I will continue to use my first approach.  I will award points and close this problem. Thanks to all for their comments.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.