If you're like me, you've got your Excel 2007 or 2010 icon pinned to the Windows 7 task bar. It's no secret that when you right click the icon you are able to left click on any of the recent or frequent files that appear. But what happens when this right click functionality stops working? What do you do when you right click the Excel icon in the task bar and don't get a list of recent files?

Fear not, for here is the answer:

1. Click START, and in the RUN command line enter the following:

%AppData%\Microsoft]Windows\Recent\AutomaticDestinations

2. Find the following entry and delete it:

9839aec31243a928.automaticDestinations-ms

3. Close the page.

No when you right click the Excel icon in the task menu you should see a list of your recent items the way you used to!

Normally copy/paste operations in Excel are pretty instantaneous. You copy data from one place and then paste into a cell in Excel. Pretty straightforward. But what do you do if you notice that copy/paste seems to take an excesseively long time when doing a paste in Excel? There are several things you can look for to resolve a situation like this.

 1. If you are copying from a web page and pasting straight HTML into Excel there can sometimes be a delay during the paste operation. It depends on how much data you are pasting. the cause of the slowness is the formatting of the data itself. One thing you can do is paste the data into a notepad document , then copy and paste the data into Excel. This will strip out formatting and paste raw ASCII into Excel. Excel will then make a determination on the format of the data based on what gets pasted. You can also choose to Paste Special > Values into Excel which just pastes in the value of the data and none of the formatting.

2. Excessive amounts of custom styles can also cause the copy/paste to be slow. If you click on the Home tab in the Ribbon and look under Styles you will see a drop-down (see below pic) that when clicked will display all of your styles. Custom styles show up at the top and can be removed by right clicking the style and left clicking Delete.

3. Memory over-allocation is another cause of slow copy/paste. To check this, in your workbook hit the END button on your keyboard and then the HOME button. If your cursor drops down past the millionth row or to the last column this is an indication of memory over-allocation. To work around this problem you would need to remove all blank and unused rows and columns. After removal, click File > Save to save the changes to the workbook. This is necessary for the new memory allocation to take effect.

4. Finally, a large number of shapes, conditional formatting and pivot tables can also result in slow copy/paste behavior. If you have a large number of autoshapes or shapes in the workbook you can try removing a portion of these to see if the copy/paste time improves. A large number of conditional formatting can also cause slow copy/paste behavior. To check, click the 'Conditional Formatting' drop down under the Styles section of the Home tab and then click Manage Rules. This will open the Conditional Formatting rules manager. You can try deleting unused rules or removing a number of rules to test making the workbook more responsive. Lastly, a large number of pivot tables can cause copy/paste slowness. To work around that problem you can try moving a portion of your pivot tables to separate sheets or even other workbooks.

Any veteran of Excel has probably seen this error dialog:

And dealing with this can be a major pain. What makes resolving this particular problem difficult is that there are multiple causes for the problem, and thus multiple solutions. This article attempts to break the file locking problem down categorically so that it's easy to diagnose and fix. Please feel free to leave any feedback!

Things to do to resolve the problem

I. Install the latest rollup and updates

Install the update in the following rollup and then install the three updates referenced in the rollup article (total of four updates).

Note: Before installing these updates you must first install SP1 for Windows 7 or Windows 2008 R2 (http://support.microsoft.com/kb/976932).

Rollup article:

"An enterprise hotfix rollup is available for Windows 7 SP1 and Windows Server 2008 R2 SP1"
http://support.microsoft.com/kb/2775511/EN-US

These are the individual links to the 4 updates to install. You do not need the reg keys mentioned in the article as they are for the specific issues listed with the reg keys which is unrelated.

Update for Windows Server 2008 R2 for Itanium-based Systems (KB2775511)
http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2775511

Update for Windows 7 (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2732673

Update for Windows 7 for x64-based Systems (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2728738

Update for Windows Server 2008 R2 x64 Edition (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2878378

II. Install the latest Cumulative Update for Excel

This is where you go to find cumulative updates that are available from the Microsoft Office team to fix reported problems:

In Excel:

1. Click File > Open
2. Click Organize > Layout
3. Uncheck 'Details Pane' and 'Preview Pane'

If Outlook is involved:

1. Open a new e-mail.
2. In the "Include" group, click Attach File.
3. Click Organize > Layout 
4. Uncheck 'Details Pane' and 'Preview Pane'.

IV. Add the OpLocks registry keys

Add the following two registry keys to the client machines and then reboot the system:

Key: HKEY_CLASSES_ROOT\CLSID\{993BE281-6695-4BA5-8A2A-7AACBFAAB69E}
Name: EnableShareDenyNone 
Type: REG_DWORD 
Value: 1

Key: HKEY_CLASSES_ROOT\CLSID\{993BE281-6695-4BA5-8A2A-7AACBFAAB69E}
Name: NoOpLock
Type: REG_DWORD 
Value: 1

Note: Both of the above keys must be added for this solution to work.

V. Install operating system updates

1. For Windows 7, Windows 7 Sp1, Windows Server 2008 R2 or Windows Server 2008 R2 SP1 -

"ERROR_SHARING_VIOLATION" error message in Windows XP or in Windows Server 2003 when you try to open a file on an SMB share on a server that is running Windows 7 or Windows Server 2008 R2"

http://support.microsoft.com/kb/2625434

2. For Windows 7, Windows Server 2008 R2

"Temporary files do not synchronize correctly to a non-DFS share on a server from a client computer that is running Windows 7 or Windows Server 2008 R2"

http://support.microsoft.com/kb/2434932

3. For Windows 7, Windows 7 Sp1, Windows Server 2008 R2 or Windows Server 2008 R2 SP1 -

"You cannot access a shared file by using the SMB Version 2 protocol because of a race condition in Windows Server 2008 R2 or in Windows 7"

http://support.microsoft.com/kb/2618190

4. Windows Vista, Windows Server 2008 -

"You share some files on a computer that is running Windows Server 2008 or Windows Vista. Some users try to access these shared files. In this scenario, the computer may restart unexpectedly. Additionally, you receive a Stop error that resembles the following: STOP 0x0000004E (0x0000009A,parameter2, parameter3, parameter4 )PFN_LIST_CORRUPT"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;2447725

5. Windows Server 2003 -

"You have a shared file on a computer that is running Windows Server 2003, Two applications access this shared file concurrently. One application has write access to the file, and the other application has read-only access to the file.After both applications run for some time, such as several hours, the application that has write access cannot write data to the shared file, and this application receives the "Delayed Write Failed" error message. This problem causes data loss or data corruption in the shared file."

http://support.microsoft.com/kb/971295

VI. Use MSConfig to stop third party programs and services, including antivirus software

1. Go to START, and from the RUN line, type MSCONFIG and hit ENTER.
2. The System Configuration Utility will launch. On the General tab, click the Selective Startup option. Then uncheck the boxes labeled "Load Startup Items" and "Load System Services".
3. Click on the Services tab. Find the column header at the top labeled "Service" and left click that column header to sort these services in alphabetical order. Scroll down in the list and find a service called "Windows Installer" and click it so that it has a check in the square box.
4. Click Apply and when the changes are made, the OK button will read "Close". Click this button and you will asked to reboot the machine.
5. After the machine has restarted, click OK when the System Configuration Utility information dialog appears. If the System Configuration Utility itself appears, click the red X at the top right corner of the dialog box. If you are prompted to restart the machine, click the button labeled "Exit Without Restart".

This should get the machine into a state where third party and non essential programs are not started when the computer boots. This includes anti-virus software. Try accessing shared files with these programs shut off.

To get the machine back to Normal mode, open MSConfig again as in Step 1 and on the General tab click the

“Normal” startup option. Click Apply, the Close and reboot the machine.

In cases where the wrong user name is displayed in the file lock dialog

Sometimes where it says 'another user' it has the name of someone that didn't open the file or it has no name at all. Just a big ' ' where a name ought to be. Fear not, there is a fix for this problem. The flavor depends on whether you have Excel 2010 or Excel 2007. The below KB Articles contain hotfixes that address several issues, not just the file locked issue. But in the dialog of the article you will see this as one of the problem descriptions:
 
Assume that you have an .xls file that is protected by Information Rights Management (IRM) on a network share. When you open the file in Excel 2007, a File In Use dialog box appears. However, the dialog box does not display the correct name of the user who locked the file. Therefore, you cannot edit the file.
 
Understand that these fixes don't make the File in Use dialog go away, they just correct the problem of showing bogus user name information on the dialog itself.
 
Excel 2007 - http://support.microsoft.com/kb/2598133
 
Excel 2010 - http://support.microsoft.com/kb/2598143

Additionally, we have seen this issue when the owner file is not deleted from the directory upon closing the file. The file looks like ~file name.xlsx and contains the user information for who had the file open. . This can happen if something interrupts the process and breaks Excels connection with the file.

An example of this would be if a user had the file open from a server and the server was then rebooted. This causes the connection between the Excel application and the owner file to be broken. When the user then closes the file, Excel does not see the need to remove the owner file from the directory, and this results in the orphaned file. When another user later tries to open the same file from that same directory with the orphaned (~filename.xlsx) file, Excel refers to the existing owner file for the information to present to the user in the file locked dialog, resulting in the incorrect user information being presented. To resolve this issue, you need only to delete the orphaned ~filename.xlsx file from the directory.

Considerations for NetApps storage devices

Note: If the issue is only occurring when using a NetApp or other third party storage, we would recommend engaging the Support Team for the third party technology for additional troubleshooting and assistance.

We have seen cases involving the file locking issue when moving files to a NetApps storage device. First and foremost - update your NetApps device to the latest version. The file locking problems can be fixed just by updating the NetApps device to the latest version. If that fails or if you have the latest version, there are a couple of other things to try. This information was documented at http://netappsky.com/netapp-storage-management/cifs-performance-is-slow-after-investigating-performance-issues-cifs-considerations/ but has since been taken down.

I. Enable oplocks

Make sure that cifs.oplocks.enable is on. Oplocks (opportunistic locks) enable a CIFS client in certain file-sharing scenarios to perform client-side caching of read-ahead, write-behind, and lock information. A client can then read from or write to a file without regularly reminding the server that it needs access to the file in question. This improves performance by reducing network traffic. CIFS oplocks on the filer are on.  By default: You might turn CIFS oplocks off under the following circumstances otherwise, you can leave CIFS oplocks on. 
 
1. You are using a database application whose documentation recommends that oplocks be turned off.
2. The CIFS clients are on an unreliable network.
3. You are handling critical data and you cannot afford even the slightest data loss.
 
sasfiler*> options cifs.oplocks.enable on
 
Make sure that this option is set before the clients connect to the filer.   If they are already connected, do a cifs terminate and cifs restart on the filer.
 
sasfiler*> options cifs.neg_buf_size 33028

II. Set cifs.oplocks_opendelta

Change the cifs.oplocks_opendelta setting. Under certain workloads setting cifs.oplocks_opendelta to 0 can improve CIFS throughput performance by 3% to 5%. Again, if setting this value to 0 results in client disconnects, reset it to 8 (the default value).  Changing the delay time for sending oplock breaks.  If a client that owns a file oplock sends a file open request, it is temporarily vulnerable to a “race condition” that can occur if the storage system requests an oplock break. To prevent this condition, the storage system delays sending an oplock break according to the delay time value (in milliseconds) specified by the cifs.oplocks.opendelta option.   By default, the default delay time is 0 milliseconds. If your storage system must support some older Microsoft Windows clients, including Microsoft you can set the delay time to another value, such as 8. This means that after the storage system receives or responds to a request to open a file, the storage system will make sure that 8 milliseconds have elapsed before sending an oplock break to that client.   options cifs.oplocks.opendelta time Here, time is the delay in milliseconds.  Setting the cifs.oplocks.opendelta option postpones the sending of oplock break requests to clients that have just opened files. You must consult technical support if you are considering setting this value higher than 35.

sasfiler*> options cifs.oplocks_opendelta 0

sasfiler*> options cifs.oplocks_opendelta 8

For example, try adjusting cifs.oplocks_opendelta to a value of 66 (milliseconds)

You might also want to increase the delay time for sending oplock breaks if you see syslog messages similar to the following:

Mon Jan 21 15:18:38 PST [CIFSAdmin:warning]: oplock break timed out to station JOHN-PC for file \\FILER\share\subdir\file.txt

Considerations for Shared Workbooks 

The primary feature of a shared workbook is to allow multiple users to open and edit the files simultaneously. However, if you have chosen to share your workbooks using the 'Share Workbook' feature in the Review ribbon, you may still experience this type of behavior in particular scenarios due to the way in which Excel opens and saves files.

Examples of scenarios that users may experience a similar message using a [Shared] Excel file simultaneously:

Scenario 1:  User 1 opens the [shared] file. User 2 also opens the [shared] file at the same time as User 1. 
       Result:  A "file is locked" type of message will be provided. 
       Workaround:  An option to "Notify" the user when the file is no longer locked is presented. Choose this option and when the lock is released, the user will be able to then choose to open the file in edit mode. 

Scenario 2:  User 1 saves the [shared] file. User 2 also saves the [shared] file at the same time as User 1. 
       Result:  A "file is locked" or "file is locked, try again later" type of message will be provided. 
       Workaround:  Wait a short period of time to allow the lock to release and try saving the file again.  

Flowchart

The following is a flowchart put together by Kevin Sickler on the Microsoft Excel Support team. It gives direction to this complex issue. Click the picture below to see a larger view:

We want to bring an emerging issue to your attention. We have found that in certain circumstances, Excel will crash when you type data into a cell and press <Enter>. The same crash may occur when you select data and click the Quick Analysis button. We are currently investigating the issue, but it has been confirmed to be related to Flash Fill and Quick Analysis, and it only occurs in 32-bit Excel. It most often occurs when data has been imported from OLAP data sources, and copied to a new location. If you look at the crash report, the crash application will be Excel.exe, and the Module will be listed as ntdll.dll.

We have a couple of workarounds:

Workaround 1:

1. Click on File --> Options --> Advanced.
2. In the Editing Options section, clear the checkbox for Automatically Flash Fill.
3. Click on the General tab.
4. Clear Show Quick Analysis options on selection.

Workaround 2:

1. In your Data tab, click Connections.
2. Click the Properties... button.
3. Clear the checkboxes under OLAP Server Formatting.
4. Import your data.

Either of these should temporarily resolve the issue. We will update this blog as new information becomes available. If this blog helps you resolve crashes in Excel, please leave a comment.

Ever performed a web query in Excel? It's pretty easy. From the Data menu you click 'From Web' in the 'Get External Data' group. Then you enter a hyperlink and choose which parts of the page you want to import. Once you click OK that's it. But what do you do if you should run into an error doing a web query like the following: "Unable to open https://somelocationhere ... Cannot download the information you requested". Fear not. This problem, like many others in life, also has an answer.

In the case of this error Excel is asking URLMON to download a copy of the file given by the web URL (https://www.somelocationhere.com). URLMON has navigated to the URL but the web page contains a header called "pragma: no-cache". This tells WININET, which is used by Internet Explorer, to avoid saving the page to the IE cache. But the file has to be saved to IE for the download to work properly and because it can't, Excel returns the error you see above. And the web query fails. Why would someone set such a thing on a web page? I'm glad you asked. There is an entrie KB Article dedicated to doing this - http://support.microsoft.com/kb/234067.

So what can you do about it? Nothing. You're stuck. Okay, okay that's not true. There are a couple of things you can do.

1. If you have the ability to make changes to the web page, this is by far the easiest method to resolve the problem. Simply remove the "Pragma: No-Cache" header. What does it look like? Look for something like the following:

<%Response.CacheControl = "no-cache" %>

or

<%Response.AddHeader "Pragma", "no-cache" %>

or

<%Response.Expires = -1 %>

2. There is a setting which allows you to bypass the no-cache check for SSL cites on the client. To do this, do the following:

a. Go to START and in the RUN line type REGEDIT.

b. In the registry navigate to

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings

c. Right click Internet Settings and left click New > DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes. Double click
this value and give it a value of 1.

That's it. You may need to restart Excel, but you don't need to restart the machine. You should now be able to perform the web query without a problem.