How to export updated data from a Forms Excel to a new Excel using VBA

Question

Hello,

I'm looking to use a Form to submit task requests in my company, and then be able to analyse the data received to look at things like time taken to complete tasks, what's coming up this week etc. To do this, I need to be able to pull data live from the Forms Excel to another sheet or Workbook, something which I've been informed is not possible with MS Forms, despite the fact Google Forms has allowed this for YEARS!

Instead, I've been recommended to set up a VBA to pull data from the Form Excel to another sheet every time the VBA is run. I have never ever used VBA, I have no clue what it does or how it works, but I really want this to work so that all of this is linked up through my Teams channel for the department, so I want to learn how to do this.

I would like to be able to press a button or a link on the new sheet/workbook, and it then automatically pulls the latest data in from the Forms Excel to the new one, where my formulas can then update all my tracking data. Can someone please explain how I would do this please? Please remember, I am brand new to VBA, so I need this in absolute moron speak "square peg in square hole" style!

TIA.

Answer 1

Dear @Alex Tyler,

Thank you for contacting Microsoft Q&A Support. 

To assist you more effectively, I would like to provide the following additional information regarding your request: you want to retrieve and analyze the data within an online environment (Excel for the web) or using the desktop version of Excel. 

If you use Excel for the online web, you can do like this: 

If you use desktop version, you can combine with Excel’s Power Query (Manual Refresh) because Power Query can pull data from the Form Excel file. 

1. Open your analysis workbook. 
2. Go to Data > Get Data > From File > From your Workbook. 
3. Select the Form-linked Excel file. 
4. Choose the sheet or table with the responses. 
5. Load it into your workbook. 
6. Click Refresh anytime to pull the latest data. 

For more instructions about Power Query in Excel: 

• Import data from data sources (Power Query) - Microsoft Support 
• Power Query Excel connector - Power Query | Microsoft Learn 

I hope that this information can assist you further in this Excel experience. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. Thank you for contacting Microsoft Q&A Support.