How can I use a VBA macro to export certain data from Project TO Excel ? RRS feed

  • Question

  • Hello, I've used the "similar topics" results and tried to fish for an answer but found it rather intimidating and I did not find exactly what I was looking for.

    I use Microsoft Project 2010 to manage and see the progression of projects that I'm responsible for. I do this by downloading the information on timesheets and work items from a project management online software where our employees can record the hours worked, the tasks they've completed etc. With the help of people in the Microsoft Project Basic Questions forums, I've managed to create a script that properly opens up a database file (which I download from the online software) and inserts the information into Microsoft Project 2010.

    Here's my problem; I want to go backwards now. I want to read the "Start" and "Finish" dates from Microsoft project (which changes based on the progression of our team) and then inscribe them in a very specific location in an excel spreadsheet which I can then upload to the online software and have my entire team see the new changes in the "start & finish" dates.

    This is how I think the code's logic should work, but I'm not sure how to actually program it - in fact, I havn't the slightest clue, and I'm hoping people here can help me with a VBA script.

    1) I need to make sure that the script is updating the correct task in MS Project, with the correct row in the Excel File. The script should make sure that the UNIQUE ID for the corresponding task in MS Project 2010, is the same value as the row value in column T of my excel spreadsheet. Column T is where I have written the exact same MSProject unique ID # by hand. The column T may or may not contain data.

    2) Once the proper row has been selected, the VBA script should then read the MS Project "Start Date" and "Finish Date" for that particular UNIQUE ID and copy those value into column P and Q respectively of the excel spreadsheet.

    3) I'd like a message box to appear saying everything is done at the end.

    I'm not against writing the script myself, so if you know where I can get all the information to create it myself I'd gladly do so. Now that I think of this I'm sure I've missed some steps in my logic. I think I would be running this script from a Microsoft Project environment through the Macro code, so the macro might have to know which file to open and modify. Anyhow, please let me know how you think I can accomplish this and I'd like to thank you for your help in advance.



    Monday, January 24, 2011 2:21 PM


All replies

  • Hi,


    You've asked way too much to get a full code answer. By the time you've got it working properly on your PCs and solved inevitable problems etc you're probably looking at 8h of time.

    To get you started, you need to use automation to connect to Excel, then use Excel to let you browse to your file, then search for your Project and Task ids. This will provide you with a range object pointing to the correct row that you can use to populate the start and finish cells.

    There is plenty of code on the Internet, you just need to work out how to put the relevant bits together to get what you want. Failing that there are many people on this group (including me) who could do it quickly for a fee. You will just need to send a project file, matching Excel file and a description of exactly what you want to have happen.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by Jim Corbin Tuesday, January 25, 2011 12:40 AM
    • Unmarked as answer by DDNProcess Tuesday, January 25, 2011 1:07 PM
    • Proposed as answer by Jeroen Schalken Tuesday, January 25, 2011 1:18 PM
    Tuesday, January 25, 2011 12:29 AM
  • Do you happen to have a more precise reference place where I can look at examples of code that do something similar ? I'm not really sure where to start with what you've told me.

    I've searched/googled for automation of excel and I'm starting to understand a little on how to start setting up an environment and read the file but I feel like I'm out fishing. I'm sure what I'm trying to do has been created in some form or fashion and I'd like to find similar examples.

    Thanks for any insight

    Tuesday, January 25, 2011 1:14 PM
  • Good news, bad news, good news!

    Good - My book on VBA for Project has everything you need. Bad - all copies have sold out. Good - Edition 2 should be available in about 5 weeks. It will include some Project 2010 specific code and an intro to writing Add-ins with Visual Studio.

    Hope you can wait!

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, January 26, 2011 7:36 AM