Forms and excel sheets

Question

Hi

I want to get Microsoft form data into another excel sheet to use a customer database. So if people complete the form online, I want to auto add that data to an existing excel table. Now I can open the excel sheet the form produces, and copy and paste, but I want it to be automatic.

That means connecting a query, or table to the Microsoft form data.

Also, I made the form originally in forms, not via excel online (insert form). That means I cant see the underlying excel book for the form. Can I copy this form to a new version, or new location and change it to be the format of forms that allows you to see the underlying xcel sheet, or do I have to make the form all over again (which will take some time)

If I re-arrange the columns in a workbook that's linked to a form, does it break the link to the form, or will the data still sync?

If I add data to table in workbook linked to a form, I know it wont sync both ways, so it wont sync to the form online in 'responses'. But will the next entry visa the form append to the end of the table in excel even if there is extra data there that was added manually in excel?

Answer 1

Hi TQM,

Thanks for posting on our forum.

I could understand your feeling.

To better help you, I have done the test, here is my test result, please check it one by one:

1. Create the form on Microsoft forms and copy this form to a new version, or new location.

You click Open in Excel (in the Responses tab), an offline, standalone workbook will be downloaded to Excel desktop. You can save this workbook to any location.

But the Excel file is not linked to the form, when the form receive any response, the Excel file will not update.

2. If I re-arrange the columns in a workbook that's linked to a form, does it break the link to the form, or will the data still sync?

About the question, we can still receive the data when receive response, but if you rearrange the columns, it will not be identified.

For example:

If you rearrange the question 1 and question 2 column on Excel file, it will not identify response.

When you receive response, the answer of question 1  will type on question 2 column.

3. If you add the data on Excel, it will not display in Forms, as Excel file is only used for review the result of Forms’ responses, we can’t edit the Excel file to affect Forms.

Given the situation, we suggest you vote for your requirement to UserVoice, which will help better our products and services.

Besides, about automatically copy data from one workbook to another workbook, it may be meet via VBA coed.

As this is related to custom code, I suggest you post a thread on the following category which is a specific channel about this kind of questions:

Office>Excel>Microsoft Office Programming>Office 2016

Thanks for your understanding.

Qing

Answer 2

Hi Victor

No, sorry, I was talking about Microsoft Forms - its a specific product from Microsoft available in office 365

Answer 3

Hi,

Thanks for contact us.

Check out the following references to see if it help you:

https://support.microsoft.com/en-us/help/291073...

https://support.office.com/en-us/article/overvi...

https://docs.microsoft.com/en-us/office/vba/exc...

Regards,

Victor