Share via

Microsoft Project, Automating Import Wizard and Subtask Creation w/ VBA

Anonymous
2018-06-26T18:44:33+00:00

Hello,

   I have been working on a few projects that have been delving into the arena of requiring VBA code to achieve the results in the Excel workbooks that I was looking for. But recently I have come into a situation where I need to create a Microsoft query in an Excel spreadsheet, and then import this query after a filter into Microsoft Project using VBA coding, not having the user fill out the Wizard prompt or go through the steps. After this import of the excel sheet's query, I would want to create a set of 6 subtasks for each row that was imported from the Microsoft Query.

   I have a good understanding of how to create queries in Excel and then paste links into project as well as manually import the query and manually create the sub tasks after this, but the plethora of data that will be imported (around 500~ rows) makes a potentially automated macro for the import wizard and subtask creation coded with VBA a very tempting subject/answer to delve into.

   If there is anyone that has insight into my proposition please feel free to respond to this thread. 

Thanks

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

John Project 49,710 Reputation points Volunteer Moderator
2018-07-02T19:53:31+00:00

Andrew,

If all the task details are to be filled in by the user/manager then I don't quite understand what data is being imported from Excel to Project. Are the potential summary lines just general groups or phases of some larger plan, kind of a plan outline?

Yes an example would be helpful. For example, what does the Excel worksheet look like? What does a mock-up of the translation to Project look like? What does your macro look like?

You can send those pieces of information to me at the address below. Then we can go from there.

John

jmacprojataticlouddotdotcom

(remove obvious redundancies)

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. John Project 49,710 Reputation points Volunteer Moderator
    2018-07-02T15:02:58+00:00

    Andrew,

    I did kind of wonder what happened to you.

    The description of what you appear to be wanting to do brings to mind the old horse and cart dilemma. How so? Although the literature identifies summary lines as tasks and the activities under them as sub tasks, neither is true. Summary lines are not tasks at all, they are simply a summary of the performance tasks under them. And sub tasks are not "sub" to anything, they are in fact the tasks that describe some type of effort to be performed in order to realize an output (e.g. performance tasks).

    If I understand what you are describing the idea falls apart from the get go. Although the data in Excel may represent a top level desire of what is to be performed, attempting to start with that in Project is fraught with issues. You mention 5 or 6 sub tasks under each imported summary but without some substantive information as to exactly what those "sub tasks" are describing the whole thing is pretty much meaningless. What are the sub tasks (i.e. active verb description)? What effort is required to complete them (i.e. work)? How are they inter-related (i.e. task links)? Who will perform the effort in the sub tasks (i.e. resources)?

    So how can you get a meaningful plan by putting the cart ahead of the horse(s)? Enlighten me.

    John

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-07-02T12:47:50+00:00

    John, 

    Sorry I have been out of office. Thanks for your replies. The data is formatted in a basic excel table via an excel query. Header row and then each row of data points in four columns, and then filtered to show only rows assigned with an "Active" in the fifth column. What I would like to do is pull the data from this filtered query into project as summary tasks from the excel sheet, and then add 5-6 sub-tasks under each of the summary tasks that were imported into project using a macro.

    I have fooled around with having excel macros put the data I want into a project formatted sheet and then trying to import that excel sheet into project but I have had trouble with project rejecting some data etc or replacing empty cells with current dates, so I am looking for a way to do the task creation in project instead of setting it up in Excel after importing a basic MS query into project automatically, unless your macro has a way to do that whilst avoiding the errors I have received in the past.

    If you need any more information please feel free to ask any question I can explain anything that you are uncertain about or need more detail on.

    Andrew

    Was this answer helpful?

    0 comments
  3. John Project 49,710 Reputation points Volunteer Moderator
    2018-06-26T20:32:08+00:00

    Andrew,

    I guess I'm one of the guys Dale is referring to. We can help you either via this forum or the Project customization forum Dale referenced but simultaneous posting of the same question on multiple forums is discouraged. Why? Because those of us who respond on these forums are all volunteers who give freely of their time to help other users. If you post the same question on multiple forums, one volunteer may be answering on one forum while some other volunteer is responding on the other forum, a redundancy of volunteer time.

    That being said, your question is rather general in nature. Exporting data from Project to Excel is very straightforward since Project has a fixed format whereas Excel's format is whatever the user wants to make it. Going the other way (Excel to Project) can be equally straightforward, IF the data in Excel is pre-formatted to promote easy consistent transfer of data.

    I have macros that export/import data from Project to Excel and Excel to Project. The former is easy to modify since Project's data format is fixed. The latter is set up for a specific Excel spreadsheet format and may or may not be easily editable depending on the data to be transferred.

    If you can provide more specifics on exactly what you want to do and how the data is formatted in Excel, we should be able to help you get what you need.

    John

    Was this answer helpful?

    0 comments
  4. Dale Howard [MVP] 29,860 Reputation points MVP Volunteer Moderator
    2018-06-26T19:32:42+00:00

    Andrew --

    In addition to this forum, which is more of a general forum for Microsoft Project questions, I would strongly encourage you to post your question in this forum as well:

    https://social.technet.microsoft.com/Forums/projectserver/en-US/home?forum=project2010custprog

    By posting in the Project Customization and Programming forum on TechNet, you expose your question to our developer friends, who can probably give you a definitive answer to your question.  Hope this helps.

    Was this answer helpful?

    0 comments