THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Running Excel 2013 in a separate instance #excel #powerpivot

The new Excel 2013 preview is publicly available and it introduces many new features, like an integrated version of PowerPivot and Power View. These two add-ins are automatically installed but not enabled by default. You just have to go in Excel Options / Add-Ins and choose COM Add-ins from the Manage combo box, then press the Go… button; at this point, you can enable both PowerPivot and Power View add-ins. There are many other posts in the blogosphere that describe the feature and the capabilities of these two tools, and I will cover some deeper consideration about specific new features in the future. In this post, I just want to highlight a new behavior of Excel that is not directly related to these add-ins, but still may affect their behavior.

One important new feature of Excel 2013 is that it no longer supports MDI (Multiple Document Interface) and it now uses the SDI (Single Document Interface) paradigm, just following a trend that other Office applications, like Word, started a few years ago. With this new behavior, every Excel document opens a window handled by the same Excel process. This is not different from previous versions of Excel and corresponds to the behavior you experienced whenever you loaded more documents within the same Excel process. However, until Excel 2010 it was very easy to create a separate Excel process so that it was possible to manipulate a document during a complex calculation in another workbook. Fundamentally, every time you opened a new Excel window, you always obtained a new Excel process and the same Excel process would have been used only by opening an existing document when another Excel document were already opened.

Usually having multiple documents within the same process can save several resources (RAM in particular). However, this approach can also block user interaction whenever a long and complex calculation is requested to Excel. In order to solve this issue, you may decide to explicitly open a new Excel instance, so that a long running operation on one Excel document does not block user interaction with other documents.

With Excel 2013, the default you have is to create a new window within the existing Excel process. In order to force the creation of a separate instance of the Excel process, you have these options:

  • From the command prompt, run EXCEL /X and you will open Excel window as a new instance. The /X command switch forces the creation of a new instance.
  • Right click on the Excel Tab in Windows taskbar keeping the ALT key pressed. And without releasing the ALT key, click on “Microsoft Excel 2013” option from the context menu. Excel will ask you if you require opening Excel as a new instance. You might press YES.
    • Please not you cannot use this technique to open an existing document – you have to create the new Excel process first, and then open the existing workbook from there

Knowing this technique can be particularly important if you are a PowerPivot user, especially if you use a 32-bit version of Excel. All PowerPivot data of different workbooks are loaded within the same virtual address space, which is limited to 2 or 3Gb in a 32-bit application. If you want to avoid being short on RAM with complex models and you want to be able to isolate a long running calculation without blocking you from using Excel with another workbook, knowing how to create a separate Excel instance is an important skill you need in Excel 2013.

Published Tuesday, July 24, 2012 7:04 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Hager said:

Sounds like an important Windows app, if autoamted. Thanks for sharing!

July 24, 2012 5:01 PM
 

Andrew Sears said:

Dim app as New Excel.Application could be a 3rd option to create an instance of Excel in VBA.

Would be nice if the loading as separate process feature was exposed as a preference option...  Are parameters supported yet in PowerPivot?   How about workbooks > 4GB?  These are a couple of things holding back adoption with one of my customers.

PowerView in Excel is an interesting one... guess that means they support Analysis Services cubes now!

Looking forward to your post on PowerPivot functionality.

July 25, 2012 8:39 AM
 

Sam said:

Thankyou this was driving me crazy - with Excel crashing a lot.

While running VB Macro's, the call was getting confused between the different workbooks and sheets.

August 6, 2012 12:19 AM
 

Gerard said:

Slight easier method for the 2nd approach:

While holding ALT, middle-click on the Excel taskbar icon.  Keep holding ALT until prompted whether to start a new instance.

The trick is that you want to be holding ALT while Excel starts up, so it prompts you whether to start a new instance.

November 6, 2012 3:49 PM
 

manjappa said:

I have got activated the Office 2013 but excel and word not working

Please help me.

Thanks in advance

April 4, 2013 1:42 AM
 

Lauran said:

Great article, with this method parallel running of code is possible in excel 2013 (so all cores of multi-core processors can be used)

May 27, 2013 3:45 PM
 

Xerxel said:

Nice tip - this is exactly what I needed.

June 18, 2013 5:15 AM
 

Mitranim said:

Big thanks to the author of the article and to Gerard (in comments). You just saved me a lot of headache.

June 20, 2013 2:37 AM
 

Shane said:

Thanks Marco, just the info I was looking for.

October 15, 2013 10:58 AM
 

Scott W said:

Wow, just spent nearly 2 hours searching for a way to do this and finally found this.  THANK YOU!

October 16, 2013 1:40 PM
 

Marco Russo (SQLBI) said:

Thanks to everyone for the feedback - It's really nice to know it helped you!

October 16, 2013 6:33 PM
 

Enrico said:

Thanks! I was so disappointed when Excel 2013 did not seem to support different instances anymore (which I really needed in my work with some macros running for two hours) so I am so happy that it's possible still! Saved my day! :-)

January 28, 2014 9:41 AM
 

Михаил said:

Марк, спасибо.

February 12, 2014 1:45 PM
 

Zalifah MD said:

Thanks a lot. I was trying to find the same and asked the IT personnel but they seem not to know about this instance thingy.

March 25, 2014 8:46 PM
 

James Pyle said:

Been looking for this.

I tried adding /X to my shortcuts for certain Excel docs though and it doesn't seem to work?

How to make it work for shortcuts?

March 29, 2014 2:31 PM
 

Marco Russo (SQLBI) said:

James, I think you cannot add parameters to a shortcut for a document - you should try creating a shortcut that runs Excel - see http://superuser.com/questions/358565/adding-command-line-switches-to-windows-shortcuts

March 30, 2014 7:04 AM
 

Andy said:

Perfect!  Retains the info in the status bar when i click on separate excel window now; thanks so much for sharing this :)

June 2, 2014 6:09 AM
 

Don Ruchkall said:

Great information,  Thanks!

June 22, 2014 10:57 AM
 

Kaghaz said:

Useful tips. Thanks Marco.

July 22, 2014 5:22 AM
 

Jessica said:

Thank you very much, this was bugging me to no end. I have to run some big marcros and when I did, it would lock all excel, now I can open a new instance and keep working.

July 31, 2014 2:49 PM
 

Cameron said:

Thanks for the info but this doesn't appear to work properly for me with Windows Server 2008 RT running Excel 2013. For Excel 2007 it worked perfectly and I would run about 8 instances each running macro code and doing a lot of work. This would push the CPU to operate near 100% of its capacity which is what I want. (Confirmed by Resource Monitor). But now with Excel 2013 I am doing the same thing and the CPU is only running on about 4%.

Any ideas on what the problem could be? Thanks a lot.

August 11, 2014 5:46 AM
 

Marco Russo (SQLBI) said:

Cameron, I usually run Excel on client side (Windows 8.1) and I don't have it installed on a server to test - it seems strange, but I don't have a similar scenario to repro (win server 2008 R2 + Excel 2013). I hope someone else will do more tests.

August 11, 2014 5:54 AM
 

Susan said:

As an aside, know that this also works to create multiple sessions as online and offline when using SAP BPC EPM10 Add-in (which essentially turns the SQL database you've connected to in BPC / EPM 10 into a pivot table in Excel).

August 13, 2014 5:32 PM
 

Marco Russo (SQLBI) said:

Susan, this is good to know - thanks for the feedback!

August 13, 2014 5:37 PM
 

Application.ScreenUpdating issue said:

October 23, 2014 9:47 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement