Forms responses missing when Excel is opened

[Stephanie Piazza]

I have created a Form in Office 365 Business and now have 81 responses.  When I try to open the responses in Excel, it only shows that I have 1 response.  Please advice.

[#2]

@Zhongzhong_Li 

I followed the steps to 'Sync all responses to a new workbook' (https://support.microsoft.com/en-us/office/how-to-get-missing-data-in-forms-9fb98299-4dcc-41a4-bb29-...

I got a new workbook with all the responses. However, when we submit a new response, it is not updating the Excel file anymore. When I open the Forms from the Excel file, I see all the responses. 

Please, could you help me on this issue? 

File was created using 'Forms for Excel' on SharePoint Online. 

Thank you

[#3]

@Stephanie Piazza 

I posted a solution back in May on this thread but it seemed to have disappeared. Here is a summary.

I had the same problem with 50% of the responses missing.

The best way to maximize your chances to have all your responses in your Excel is to have two excel documents. One document for the data and one for the processing of the data.

The reason is that Excel tries to calculate every formula each time forms add a response. It can take a long time especially if you have a lot of formulas (I had 1000 complex formulas referencing the data). Forms is like a user, it tries to add data but if the document is calculating or blocked by another user it gives up. You then end up with missing data. 

Having a document for data allows forms to input data without being stuck by excel calculating or another user modifying the document. Excel will only try to calculate formulas when you open the processing document containing the formulas. It's also a great way to change your data document if needed. 

With this, I nearly never had missing data.

Hopes it helps.

[#4]

Having a similar issue as described.

I realised that the link between my Microsoft Form and Microsoft Excel Workbook broke when I added a macro to one of the sheets and it saved the file as an .xlsm macro enabled workbook. I got rid of the macro and tried saving as an .xlsx again but it made no difference.
 
Have tried changing the False to True as described several times earlier, have also tried syncing all responses to a new workbook and also opening in Excel from the Forms Responses tab but no luck.

Raised the issue with our IT support 3 weeks ago who raised a support ticket with Microsoft, unfortunately I have had no response other than one offer of a callback one week ago which I agreed to, but Microsoft never bothered to call. 

Yesterday I escalated to our IT support again and they have escalated to their own IT support who are closer to Microsoft.

I am hoping for an answer promptly as it has been broken for 3 weeks and I need to have this resolved tomorrow as I need to use the data for end of month reporting.

Any help would be much appreciated.

[#5]

@Chris-Smith  The user is a member of the SharePoint site or the owner of OneDirve to sync to a new workbook, Please first make sure this is true. Then try "Sync all responses in a new workbook" as in below link, it should work usually. https://support.microsoft.com/en-us/office/how-to-get-missing-data-in-forms-9fb98299-4dcc-41a4-bb29-...

If "Sync all responses in a new workbook" still not helping,  please collect network trace and escalate to support team. 

And we suggest NOT to change the workbook Forms sync to, like formula, macro, etc. It may cause live sync broken.

[#6]

@longdingI have tried the Sync all responses to a new workbook, but either I am unable to find where the new workbook is going.

How do I collect network trace?
How do I escalate within the support team? (I have been trying to contact Microsoft and have a ticket raised for over 3 weeks and have not had any response)

While I understand not changing the workbook, sometimes additions are made as time goes by to automate tasks, so there should be some way to be able to choose where the responses are sent.

It appears there are common issues of the workbook and form losing their connection and no obvious way to reconnect them. I would thought that having the ability to re-link the spreadsheet and form would be important after 2 years.

[#7]

@Erik Wright 

My form is not communicating with my excel sheet the link just stop working.