How to Link an Excel Spreadsheet to a Microsoft Project Plan
Question details
Users want to automatically retrieve tasks, start dates, and end dates from a local Microsoft Project file into an Excel spreadsheet without manually copying the data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking project schedules and needing up-to-date timeline data in an Excel workbook connected to a local .mpp file.
- Observed behavior
- Microsoft Excel does not offer a simple Power Query or OData connection for local Microsoft Project files, requiring users to rely on fragile Paste Links or complex workarounds.
Ensure both Microsoft Excel and Microsoft Project are installed on your computer, and verify that your target .mpp file is saved in a stable local directory before setting up the connection.
Use an Excel VBA AutoOpen Macro to Import Project Data
Creating a VBA macro that runs automatically when the workbook opens is the most flexible and reliable way to extract data from an .mpp file.
Unlike native Power Query which cannot read local Project files seamlessly, a VBA script can launch Microsoft Project in the background, extract the required task fields, and populate your spreadsheet automatically every time it opens.
Launch your Excel workbook and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Click on 'Tools' in the top menu, select 'References', check the box next to 'Microsoft Project Object Library', and click 'OK'.
Click 'Insert' > 'Module' and write an Auto_Open subroutine. Use the code CreateObject("MSProject.Application") to open your target .mpp file and write a loop to pull task names, start dates, and end dates into your Excel cells.
Close the VBA editor and save your Excel file as an Excel Macro-Enabled Workbook (.xlsm). The data will now update automatically upon opening.

Use the Paste Link Feature to Embed Project Data
Paste Link allows you to establish a basic connection between specific fields in MS Project and cells in Excel, suitable for simple data requirements.
Simplify Project Tracking and Spreadsheets with WPS Office
If handling complex VBA macros and fragile links between Microsoft Project and Excel is frustrating, consider managing your project timelines directly within WPS Spreadsheet. WPS Office is a free, lightweight suite that offers built-in project tracking templates, ensuring you stay organized without the hassle of connecting separate applications.
- 1. Download and Install: Get WPS Office for free from the official website and complete the quick installation.
- 2. Open a Project Template: Launch WPS Spreadsheet, navigate to the Templates gallery, and search for 'Project Management' or 'Gantt Chart'.
- 3. Track Seamlessly: Use the built-in tracking features to manage your project tasks and timelines in one unified file without needing external links.

Frequently Asked Questions
Can I use Power Query to connect to a local Microsoft Project (.mpp) file?
Natively, Excel's Power Query does not support direct connections to local .mpp files. You typically need to export the Project data to an Excel or CSV file first, or use a VBA macro to extract the data automatically.
Why do my Paste Links from MS Project to Excel keep breaking?
Paste Links rely on absolute file paths. If either the Excel file or the Microsoft Project file is moved, renamed, or saved in a different network location, the link will break and require manual re-establishment.
Is it possible to export Microsoft Project data to Excel without using VBA?
Yes, you can manually export Project data by using the 'Save As' feature in Microsoft Project and choosing the Excel Workbook format. However, this is a one-time manual process and will not automatically update your Excel file when the project changes.
How do I fix missing Object Library errors when using VBA?
If you receive an error about missing libraries, open the VBA Editor (ALT + F11), navigate to Tools > References, and scroll down to ensure 'Microsoft Project Object Library' is checked. If you shared the file with someone who does not have MS Project installed, the macro will fail.




