01 June, 2011

Excel Cannot Connect to SharePoint List

As I am working in SharePoint support, I come across so many issues on day 2 day basis and always try to share with all of you as soon as the issue gets resolved.

Today, We have received one support ticket in which the user has created a list which consist of few items. When he selects list Actions--Export to spreadsheet--Excel opens up and Displays 'Security' dialog box by giving the option of 'ENABLE' and 'DISABLE'.

When user selects the Enable button then 'Import Data' dialog comes up, and then user clicks OK. As soon as the OK action performs then suddenly it throws an error message: Excel Cannot Connect to SharePoint List

Resolution: 
Generally we start our troubleshooting from the SharePoint side as what is wrong with my SharePoint site, Is there any problem in the SharePoint list,
Does the Office which is installed on the machine is causing the problem?
Is there any site collection feature turned off?
Is there any problem in the SP-Database?
Problem with the IIS?

so things are there by which we can think off but don't waste your time in doing all these things as I have done all these things and the resolution is quite simple which you can implement without any help.

1)   Open the Internet Explorer
2)   Tools
3)   Internet Options
4)   Advanced Settings
5)   Scroll downwards and reach towards the section named as "Security"
6)   CHECK FOR SERVER CERTIFICATE REVOCATION--which is by default checked
7)   Unchecked it
8)   Apply and Ok
9)   Close all the internet explorers (IE) and open your SharePoint site in a new IE i.e. Browser/Explorer

You should be able to export the spreadsheet without any problems/issues.

Please let me know in case of any further queries/questions.

I would be more than HAPPY to help you as well as RESOLVES your issues, Thank you. 

45 comments:

  1. Amol, Great to see this post.. i guess we have seen this issue in Baxter too?

    ReplyDelete
  2. yeah,we worked on this issue earlier but that was a different env. Please let me know in case of any further queries/questions... Thanks for your kind words :-)

    ReplyDelete
  3. Am still facing the same problem on MOSS 2007 with office 2007 with Forms based authentication.Every thing works fine as long as you are in Intranet But when you try to access from Extranet it fails with above exception.

    If you have any solution please let me know.

    ReplyDelete
  4. 1) Did you checked the above mentioned resolution in extranet?

    2) is it happening on one specific machine or multiple machines ? Please check the machine configuration too

    3)In addition to point2-Please check the explorer version also, try the same action Mozilla firefox too

    Please check and let me know the results, Thank 4 your patience

    ReplyDelete
  5. To optimize Excel spreadsheets processing in sharepoint, we offer HarePoint Workflow Extensions software ( http://www.harepoint.com/Products/HarePointWorkflowExtensions/ ) - about 200 new workflow activities, including free ones.

    ReplyDelete
  6. Hi Amol, I am trying to creating a link on one excel and pulling value from another excel and both are in sharepoint but after I put = then gone to the other file and select the cell it doesnt feed into this file please let me know how to tackle this.

    ReplyDelete
  7. Please correct me if i am wrong. As per the provided details, it seems that you are trying to fetch the values from another sharepoint list. yes-you can do this by means of 'lookup column' that will fetch the value from any list which exsist on that site.

    If you are doing anything else then please let me know in details, Thank you

    ReplyDelete
  8. Hi Amol, sorry for not get back to you for long since i was on holidays. i have different files on same share point also one summary file on same share point. so what i am doing is for summary just linking the individual file on the SP. so when I do this I am not able to link the worksheet to summary file. thanks in advance

    ReplyDelete
  9. Hi Amol, also when I am opening the summary and individual files I am getting following error.
    'Unable to open https://central.com/company/summary.xls The internet site reports that the item you requested could not be found (HTTP/1.0404)' and a OK button after pressing so many ok button only the file opens up. And when I do this all the values remain same and didn't change with latest values from other files. The other issue is when I do the same from a different system this is working alright without any problem or any error. so not sure what is the problem on my machine alone. please help me. thanks babu

    ReplyDelete
  10. one thing is very clear that this is machine specific issue as it is working from different machine. Probable reasons would be IE problems, office problems or OS problems.

    1-If its an IE problem then try this:
    -Open an new IE
    -Internet options
    -tools
    -advanced tab
    -RESET
    -apply and Ok

    after this:
    -------------
    -Open a new IE once again
    -Tools
    -Internet options
    -security options
    -sites tab
    -add your site here (dont click apply and ok completely)
    -click ok
    -come back to the main window where u can see the custom tab
    -go towards the bottom and make sure "automatic logon with current username and password is selected"
    -apply and Ok

    2-if its a office problem then try this:
    Click the Microsoft Office Button, and then click Excel Options.
    Click Resources.
    Click Diagnose, click Continue, and then click Start Diagnostics.

    If its an OS problem then you need to contact your local helpdesk to detect the problems

    ReplyDelete
  11. Hi Amol, Thanks for your kind reply. Its not a IE problem even when I saved it local I am getting the error while opening. I have tried the Diagnose on excel as per your reply but that still didnt fixed my issue. It's just telling same kind of error with some old link which we cant find it on that excel at all. Let me know if you can give me any other way to fix this issue.

    ReplyDelete
  12. If the same file is working fine from other systems/machines then we need to go for office repair/might be the best option to uninstall & install the office again. If you do not have rights then please involve your local desktop support and check the results, Thank you

    ReplyDelete
  13. Hi Amol, I am trying to sync the excel with the sharepoint list. In the excel sheet i have created a table excatly like the fields in the sharepoint list. In excel, design->Publish and allow sync, i gave the sharepoint server name, list name and description and clicked on publish button. But, asks for user-id and password but gives the error as "cannot connect to the server at this time. your table cannot be published". When i clicked on debug, it pointed to below VBA code.

    retUrl = ActiveCell.ListObject.Publish(Array(ServerName.Value, ListName.Value, ListDescription.Value), False)

    In the internet explorer, i am able to login to the share point server and add a new record in the list. But when i tried to publish from excel, it is showing error as described above.

    ReplyDelete
  14. Kiran,

    I can suggest you multiple ways to workaround this problem so lets try one by one:

    This problem occurs because of an issue between Windows SharePoint Services and Excel that prevents the Excel spreadsheet from being imported correctly to Windows SharePoint Services when you create a new list. This problem may occur if the Web site is configured to use Anonymous access.

    Workaround no#1:
    ----------------------------
    Try to install the below mentioned hotfix on your machine and then try the same process that you are doing presently. check the results and let me know.
    http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx
    http://support.microsoft.com/kb/951996

    Workaround no#2:
    -----------------------------
    When we click on publish and sync option, in that list name, provide a new name so that new list will be created. please check this.

    once new list will be created then you can move the list items by means of content and structure.

    Workaround no#3:
    -----------------------------
    Datasheet view options is an great asset in SharePoint. Copy the data which is present in your excel sheet, open the list in datasheet view & paste. simple n easy.

    Please check and let me know in case of any further queries, Thanks for your patience

    ReplyDelete
  15. Hi Amol,
    I have an issue that I hope you can help me with.

    I'm trying to sync Sharepoint with Excel in reference to the following document.
    http://msdn.microsoft.com/en-us/library/office/bb462636(v=office.11).aspx

    This has worked on my machine in the past (until my machine crashed and XP was re-installed in my machine) and it works on my colleague's machine. The issue I face is when I click the Publish and Allow Sync button, it creates the table in Sharepoint, then it deletes the table on Excel and it tries to create a linked table from Sharepoint. The third step is where it hangs on my machine. The status bar says "connecting database..." and then it just hangs there. This only happens on my machine though.

    Any thoughts?

    Thanks,
    Jirain

    ReplyDelete
  16. based on the provided description- it seems that the problem is machine specific as it is working on your colleague machine too.

    right now i can suspect that problem might be IE specific/office specific

    which IE version and Office version you are using on your machine ?

    Did the same versions present before the machine crashed?

    can you please try same process in different IE i.e. Mozilla/chrome ?

    keep me posted about the results, thanks for your patience

    ReplyDelete
  17. Hi Amol,

    Thanks for the response. Yes, I believe it's machine specific as well. I've I have using Version: 8.0.6001.18702 Update Versions:0

    I have Office 2007 (+ Excel 2003) installed on my machine.

    Yea, the same version was there before the crash. I did have Excel 2010 as well though. Although I just tested it on another machine that doesn't have Excel 2010 and it works.

    I'm going to get my IT people to run windows update on my machine. Will keep you updated!

    Thanks,
    Jirain

    ReplyDelete
  18. Okay. Keep me posted and let me know in case of any queries so that we can discuss and proceed further, Thank you.

    ReplyDelete
  19. vittal: sir i tried to do the above mentioned things but the error is same cannot connet to the server at this time your table cannot be published

    please help me sir

    regards
    vittal

    ReplyDelete
  20. Can you please explain as what excatly you are doing and brief problem description so that i can guide you?

    I am asking these details as in your questions you have mentioned regarding TABLE. Thank you

    ReplyDelete
    Replies
    1. i am trying to configure random quote webpart in sharepoint 2010, what i did is i have an excel sheet of quotes in excel 2007 trying to import to sharepoint list, but it was giving error cannot connect to the server at this time your ctable cannot be published.

      regards
      vittal

      Delete
  21. 1. It seems that this is machine specific. Could you please try from other machine and check the results?

    2. Could you please provide the following information?
    - IE Version used?
    - Operating system
    - Office version? Please check for any mismatch also

    3. Also, could you please try to follow the same process from any other browser? Example: Mizilla Firefox/ Google chrome

    ReplyDelete
  22. helpful article .. thanks for sharing ..

    ReplyDelete
  23. when i click on export excel option from my list , it says connecting to datasource then "An unexpected error has occured.Changes to your data cannot be saved."

    this is happening for all lists.

    ReplyDelete
    Replies
    1. - which explorer you are using? pls name the version...

      - Try to reset it back to manufacturer settings and then check the results

      How to do this?

      1- open the internet explorer
      2- Tools
      3- Internet options
      4- Advanced
      5- 'RESET'

      In case of any queries then pls let me know, thank you

      Delete
  24. Your article has given me exact guidelines to troubleshoot this issue :) :) Thank you Amol...

    ReplyDelete
  25. Thanks for the Solution it worked in one of my VM, But interestingly it is not working on another VM with exact cofiguration (Win2012 server,IE10), Any suugestion?

    ReplyDelete
    Replies
    1. There might be a version conflict, make sure you are using 32-bit IE. can you pls cross checked once and update?

      Delete
  26. Hi Amol,
    Thanks for the article.
    One of our client is facing similar Issue when trying to use the Export to Excel feature.
    They are using form based Authentication and trying to export Discussion board data to a Excel Sheet
    Client OS: Windows 7 and Office version is 2007
    Error is: Excel cannot connect to SharePoint List
    One of the reason of the error might be, When Login into the Site, Client Uses Administrator Credential and after the file is Downloaded on the local Machine it might be taking the Windows Authentication.
    Thanks in advance.

    ReplyDelete
    Replies
    1. Is this happening across all user base / specific to only one user?

      Can we do some testing from another machine if possible? because if the client has proper permissions then this might be machine specific.

      let me know your thoughts, thank you

      Delete
    2. Thanks Amol for your swift reply. below are the answers to your questions.
      >>Is this happening across all user base / specific to only one user?
      It is happening to all user.
      >>Can we do some testing from another machine if possible? because if the client has proper permissions then this might be machine specific.
      Yes, we tried doing it from another machine, but we received same error.

      While downloading the ".iqy" file from the SharePoint site [Form based authentication], user has to login with different credential and when user opens the file from his or other machine the same error is displayed "Excel cannot connect to SharePoint list".
      One more thing I want to mention is that the SharePoint Server is in different domain and the domain where the file is downloaded is in another domain.

      Can we have an option where when the user opens the ".iqy" file it should ask for credential.
      Client has also tried opening the file in Office version 2007 and 2010

      Thanks

      Delete
    3. Can we have an option where when the user opens the ".iqy" file it should ask for credentia- not possible

      I am suspecting this is happening becoz of FBA. we can do one test if possible- Just extend the web application using windows authentication and check the results. it has to work

      pls chk and let me know, thanks

      Delete
  27. I was able to export list before. This is an InfoPath document library that I was having an issue publishing an updated form because there are close to 50000 items. It has add the exiting columns about 5 times. I check the security setting on explorer and that is not the case from the resolution above.

    Any idea what happened or how I can fix it?

    ReplyDelete
    Replies
    1. Henry,
      I need some more details on this as "export to excel" option associated with list and you mentioned about document document library. are you sure that you were able to get the option for the document library?

      or you are facing some different error? If it is the same error then could you please elaborate the action you are doing and after that you are getting this error message. Thank you

      Delete
    2. From the Document library, I click on actions then try to 'Export to Spreedsheet'. I new box opens and ask if I want to open, save, or cancel. Whether I open or save I still get same message. This opens up Excel and within excel it is asking me to open file from temp location and 'This file contains queries to external data. If you trust the source of this file, click Open. That is when I get the error message.

      I have published an infopath form to this location about 4 or 5 times and now I have 4-5 duplicate columns in the library. I wonder if this is causing the issue.

      Delete
    3. two things we can try to isolate the issue:-
      1- check the same behavior on different machine & check the results
      2- Is this happening with this library only? can we test with other list/libraries?

      Delete
    4. This is occuring on multiple machine. It is the specific library only. I think it may have to do when I publish the info path form. It has an issue with updating list/library when I tried to publish it.

      Delete
    5. Try to publish the same form on new form library and check the results. By this way, we can isolate the issue.

      Delete
    6. I published to new library no issue. This is library is in SharePoint 2007 and I am running Windows XP/Excel 2003. I tested this library in Windows 7/Excel2010 and I was able to get data in excel. I dont know what is different between the two environment.

      Delete
    7. It seems that the problem is with Excel 2003!

      Lets do one thing-

      The machine where office 2003 is installed, lets try to repair this version

      Run the Detect and Repair Tool from Add or Remove Programs

      -Click Start, and then click Control Panel.
      -Double-click Add or Remove Programs, click Microsoft Office 2003, and then click Change.
      -In the Microsoft Office 2003 Setup dialog box, click Reinstall or Repair, and then click Next.
      -Click either Reinstall Office or Detect and Repair errors in my Office installation, and then click Install.

      please keep me posted, thanks

      Delete
  28. Whoa! This blog looks exactly like my old one!
    It's on a entirely different topic but it has pretty much the same page layout and design.
    Superb choice of colors!

    Visit my site ... uninstall google chrome

    ReplyDelete
  29. Hi there everyone, it's my first pay a quick visit
    at this website, and piece of writing is actually fruitful in support of me, keep up posting such content.



    Also visit my blog ... download video from youtube download youtube videos

    ReplyDelete
  30. It works for me! Thanks Amol!

    ReplyDelete
  31. Hey,
    I'm using sharepoint 2013
    Getting same error even after settings done in IE.
    No success..:(

    ReplyDelete

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

Note: Only a member of this blog may post a comment.