Solved

Excel 2010 - Shared excel file on network

Posted on 2011-09-14
15
404 Views
Last Modified: 2012-08-13
Dear Experts,

Everyday I have to make and share a file for users in Excel 2010, but somehow their experience is that sometimes the file is frozen at their saving, they have to close it and open again and try again.

Could you please advise which kind of settings could be the reason of this?

Basically in the old version of excels it was never problem, also it is my general experience that somehow the new excel checks probably a lot of things at the saving and it is doing it slower than the before version. Maybe these additional checks should be switched off?

thanks,
0
Comment
Question by:csehz
  • 8
  • 6
15 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 350 total points
ID: 36542012
Sharing in Excel is always problematic make sure of following
1) Users have all the latest updated version of Excel with their respective updates.
2) You mention Excel 2010 are some users using an other version ?
3) Check hte permission of these users to the file and make sure they are granted access to read/write/modify/save or if they need to simply lookup the file read only.
4) When you say Share it is meant that users access the file simultanously is this the case and case yes are you able to manage it properly ?? In this case it is always a problem in Excel
5) if by sharing you meant several users will lookup the file but not necessarly at the same time then your best bet would be to ensure all updates of excel are done forthe users and they have the correct permissions to access the file.
6) One last issue make sure that the user has a clear mapping of the drive to access the file this sometime avoids problems in networks. Like you should have for the user in Mycomputer the location of your excel file maped as a network drive and either he access it thru my computer or a shortcut on his desktop lead him to this location.
7) Last but not least DELETE all shortcuts already exiting for this file in user's computer and create new shortcuts this sometimes contribute to create problems.

Rgds/gowflow
0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 150 total points
ID: 36542038
Please take a look at this article:

http://excel-user.blogspot.com/2011/07/sharing-workbook.html

jppinto
0
 
LVL 1

Author Comment

by:csehz
ID: 36542819
Thanks the advices,

Gowflow,
1-2) Yes that is sure all users have the same versions already in the company so Excel 2010
3) Yes the system administrator gives write accesses and the users have
4-5) Basically the Shares means that around 6 users are modifying the file relatively often during the day, I am afraid this is probably the reason as also Jppinto advised with the attached link article, that only the matter of the time to become the file corrupted.
6-7) I did not check the drives and shortcuts but thanks will do and try

So maybe I am closing the topic, maybe just one short question asking your general feeling

- Do you think that the Excel 2010 has more share kind of issue than the before versions?

In our company it is so much spreaded practice this excel sharing, because there is no other way of common work. So will be always needed, and sure we have the experience through years that in Excel 2000 it was very rarely problem. With the same pattern, so around 6 users modifying the same file. But with the new Excel something is different

thanks,
 
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36542854
Well I can tell you that I tried ONCE in all my carreer over 30 years+ to use Sharing in Excel and I didn't finish the day without problems. !!!

My suggestion for you is too look at developpment solution for what you need VBA or others can be possiblly solution for your organisation work. Also for sure you need to look at access or other ways to safley work in sharing more accessing single database from diffrent users at the same time.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36542979
I will be glad to help you in these as it is my expertize in all of VB/Access/Excel VBA
gowflow
0
 
LVL 1

Author Comment

by:csehz
ID: 36542991
Gowflow thanks just again,

Basically which is the strange for me a little bit that everywhere can be hear this that shared Excel files are hard to manage, but we rather did not have this impression.

Until coming the Office 2010 :-)

We worked a lot of with shared excels, this stayed our best practice to enter for example comments of buyers at the ordering department. It worked fine, very rarely was the file corrupt or something.

But lets see will try to do some experiments, in this moment the new office is rather step back from this point of view. But of course in a lot of other point of view it is much better than the old versions.

thanks,
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36544947
OK then I see you have basic requirement then you need to tell me how you moved your file from the eralier version to Excel 2010
step by step
gowflow
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:csehz
ID: 36547450
Gowflow thanks that you are so open to the question, basically the history was like this.

Briefly it is a big coorporation with several plants on multiple countries, meaning that in on one network is everybody, each city has own public servers for users who place their files there. For a long the standard was the Office 2000, this year came the Office 2010 with new excel.
So because of the size of course also the departments have big sizes, I am working on supply chain are in materials management, cooperating with the mentioned buyers who are ordering materials.

For years we had a practice to spare the information between of the factory members (because it is important whether the material will be available for the production), that we use shared excel files, where everybody writes comments to around five columns.
Technically this file comes from Access 2000 with export, originally of course in Excel 2000 format as it knows that one, opening in new office I save it as .xlsx format, after Review/Share.
This file as a standard procedure works like this, so each day new file comes from Access. The previous day comments are coming from the yesterday file, we have a practice making a Prev.xls which contains these, so the the daily file is updated Access calculation + yesterdays comments, and everyday like so on.

So we really have experience that the Office 2000 was better in sharing files, it was almost never frozen. But came the Office 2010 and came the problems, the users often have to close the file with force and reopen.

Basically my question would be that what additional checks are doing the Excel 2010 compare to the old version? Maybe some safety checks? In our company that does not have meaning, we do not need any kind of authorisation check, or for example the new Trust Center options. Superfluous basically should be switched off from this point of view. For example technically I had to list the server folders as trusted places, but on these home servers it is just rather block and additional task.

My personal feeling is that the new Office is doing some additional steps compare to the old one at the save, because apparently slower has this process than the old.

Thanks,
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36547598
Could be but not necessarely.
1) Can you post a sample of this workbook (Excel 2000 version and the one you converted to 2010 version ?) You may alter the data if confidential
2) Do you have drawings in your original file ? Do you have macros there ? All these can affect 2010 as I understood from earlier questions. I personally have 2007 and not 2010 and in our organisation we are still working on 2003 !!
3) I am very surprised you say your organisation is Big in multiple countries how can they take the decision to jump from 2000 (which is pretty old system to 2010 which is pretty new and not totally debugged !!!)
4) Honestly I cannot answer your questions in the air I need to see the file and look for possible issues that may cause problems.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36547762
ceszh,
I found these to help you maybe troubleshoot your issues.

================================================
Excel 2007 and 2010 Hangs

http://social.technet.microsoft.com/Forums/en-US/excel/thread/2136ec5b-23b0-4de8-8d38-2d2283ab495b/

---------------------------------------------------------------------------------


  This issue occurs when there is a corruption in the windows user account. Try
creating a new windows user account, log on to it and then try performing the same
steps in excel.
 
Click on the Microsoft link below to know how to create new windows user account.
http://windows.microsoft.com/en-US/Windows7/Create-a-user-account
--------------------------------------------------------------------------------

To transfer the data from old user account to new user account follow the steps:
http://support.microsoft.com/kb/811151
--------------------------------------------------------------------------------
Try changing the Default Printer.
Click on start>>Control Panel>>Hardware and Sound>>Devices and Printers>>
And change the default printer to one note.
 
Right click on send to One Note under printers and faxes  and click on set as default
printer.
Then close that window.
Go back to Excel 2007 and try to copy and paste if it works this time it’s clear that
you have to update
the printer driver.
--------------------------------------------------------------------------------

Steps that you can try to resolve the issue:
1.       Clear the temp folder and Excel start up folder
(Refer to the steps on http://support.microsoft.com/KB/919196  this also takes care
of the any issue caused by addins)
 
2.   Temporarily disconnect the network drives on the computer / move the excel file
on the network to local computer
 
3.       Copy the excel file to you local computer  and Un-share the excel file
(Click on Review-uncheck the option ” Allow Changes by more than one user at the same time. This also allows workbook merging”

Note: if the excel file is on your local computer, move it to another location within
your computer and try the step3
 
4.       If none of the steps work, try starting excel in safe mode and then try
copying/ pasting data
 
a.       To start excel in safe mode Start > Run > “Excel /s” (without quotes)
--------------------------------------------------------------------------------

Hope these can help troubleshoot.
gowflow
0
 
LVL 1

Author Comment

by:csehz
ID: 36548204
Gowflow thanks the advices I will read through the links,

Relating your first request so just the shortened attached files because in real they have around 10000 records

- BUYtest.xls this is coming from Access2000 through menu File/Export, there selecting file type Microsoft Excel 97-2000.
-Shared_BUYtest.xls this is the shared version, opening with Excel 2010 but saved in version Microsoft Excel 97-2000

Basically only now I realized that this can be also tricky, because we use Excel 2010 but typically saving the files as Microsoft Excel 97-2000, this is because the Access databases are in 2000 version, and their linked excel tables also should be in Excel 2000.

Maybe a kind of solution could be to have the shared files in .xlsx format, and do a copy of them at the Access runs as .xls one.
BUYtest.zip
0
 
LVL 1

Author Comment

by:csehz
ID: 36548227
Also would like to show two interesting things on the pictures

1) I was not able to reproduce on the small one record example file, but on the 10000 records live version I always have such screen when opening the untouched Access 2000 exported .xls file in format Microsoft Excel 97-2000, but so with opened with Excel 2010

 Point1
2) Today morning was on the server a small file without any extension, it is also sometimes happen, I assume some sign that the user was struggled with saving the shared file
 Point2
thanks,
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36548291
Your problem is clear
1) you have shpaes and drawing that your access maybe are shooting in the file that are delaying as this is known cause in 2010
2) you are saving as Microsoft Excel 97-2000 and opening it directly in 2010 which is also cotributing to a problem.

I suggest try the following:
1) take a fresh Export out of Access saved in .xls (Microsoft Excel 97-2000) version.
2) Open BLANK I repeat a BLANK 2010 Excel and when it is started goto File/Open and choose your .xls Microsoft Excel 97-2000 SAVE AS and choose from the types .xlsx
3) Close it exit Excel
4) Distribute this version to users
5) let them try it

Let me know. I will look into the file you attached later.
gowflow
0
 
LVL 1

Author Comment

by:csehz
ID: 36548320
Gowflow thanks, the next new file is on next week Monday so I will try to do the steps that time like that, and asking the users about the feedback. And of course will come here about the experiences

thanks,
Zsolt
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36548526
Please be aware no one should use the .xls file I suggest you (or whoever is in charge 1 person) to do these step BEFORE anyone uses the file and once the .xlsx version is created to move the .xls version onto an other directory to make sure no one uses it. If it turn out to solve the problem then  I could propose a small automation for you to have this done automatically and safely.
gowflow
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now