(1, 2) Names the sheet "MacroSheet", so users can change the name from PROJXTAB.XLM.
(1, 3) 1
(1, 4) 322
(1, 5) 6
(1, 6) 88
(1, 8) OK
(2, 2) Creates a new Microsoft Excel spreadsheet; adds titles and formatting for the titles.
(2, 3) 21
(2, 4) 309
(2, 5) 83
(2, 6) 100
(2, 7) 50
(2, 8) r3c11:r6c11
(2, 9) 2
(2, 10) Days
(2, 11) Minutes
(2, 12) Work
(3, 3) 8
(3, 4) 269
(3, 5) 105
(3, 6) 140
(3, 9)
(3, 10) Weeks
(3, 11) Hours
(3, 12) Cumulative Work
(4, 0) Spreadsheet
(4, 2) Name the new sheet "Spreadsheet".
(4, 3) 21
(4, 4) 109
(4, 5) 83
(4, 6) 100
(4, 7) 50
(4, 8) r3c12:r6c12
(4, 9) 2
(4, 10) Months
(4, 11) Days
(4, 12) Cost
(5, 3) 8
(5, 4) 69
(5, 5) 105
(5, 6) 140
(5, 9)
(5, 10) Years
(5, 11) Weeks
(5, 12) Cumulative Cost
(6, 3) 5
(6, 4) 213
(6, 5) 85
(6, 8) Timescale:
(6, 12) Percent Allocation
(7, 3) 5
(7, 4) 213
(7, 5) 107
(7, 8) To:
(7, 12) Overallocation
(8, 3) 2
(8, 4) 322
(8, 5) 30
(8, 6) 88
(8, 8) Cancel
(9, 3) 14
(9, 4) 10
(9, 5) 7
(9, 6) 228
(9, 7) 49
(9, 8) Report Type
(10, 3) 11
(10, 9) 1
(11, 3) 12
(11, 8) Tasks by Resource
(12, 3) 12
(12, 8) Resources by Task
(13, 3) 5
(13, 4) 10
(13, 5) 85
(13, 8) Work Units:
(14, 3) 5
(14, 4) 10
(14, 5) 107
(14, 8) From:
(15, 3) 5
(15, 4) 10
(15, 5) 63
(15, 8) Type of Information:
(16, 3) 21
(16, 4) 197
(16, 5) 62
(16, 6) 212
(16, 7) 80
(16, 8) r3c13:r8c13
(16, 9) 1
(27, 2) Redraws the spreadsheet.
(30, 0) DDEChannel
(30, 2) Gets the name of the active project in Microsoft Project on the system channel, and closes the system channel.
(31, 0) ActiveProj
(31, 2) If there is no project open, the macro ends here.
(40, 2) Opens DDE communication on the active project ("ActiveProj" from the last section). Gets general project information and places it on the sheet (project name, project start date, project finish date).
(41, 0) NumTasks
(41, 2) Checks to see that there are both tasks and resources in the project.
(42, 0) NumResources
(54, 0) ProjStart
(54, 2) Fills in the project data on the report.
(55, 0) ProjFinish
(68, 2) Displays dialog box asking user for their preferences.
(74, 2) Sets Default Work Units in Preferences in Microsoft Project.
(75, 2) Sets report dates. If the user didn't change them, they are the project dates.
(78, 2) This section sets up the timescale across the top of the sheet. Depending on what timescale unit is chosen, the start date for the timescale is calculated and is prior to the project start date.
(79, 2) DATA.SERIES fills the timescale to match the time period specified.
(88, 2) Redraws the spreadsheet.
(91, 2) Sets up Microsoft Project with the proper view.
(92, 2) Change_Timescale is a string that is sent to Microsoft Project using SendKeys to set up the appropriate timescale.
(101, 2) This gets the list separator from Microsoft Project.
(103, 0) ListSep
(107, 2) Redraws after returning from Microsoft Project
(110, 2) Creates the Tasks by Resource report.
(111, 0)
(111, 2) Creates an array called ProjResources of all of the resources in the active project . The array contains the Unique ID and the Name for each resource.
(114, 2) Unique_Tasks is a placeholder to get the list of all of the tasks to which the resource is assigned. Transpose changes the array into a list that is sent back to Microsoft Project in the next Request
(115, 0)
(115, 2) Creates an array--Res#-- for each resource containing the Names and ID's for every task to which the resource is assigned. The ID is needed for EditGoTo in Microsoft Project.
(119, 0) Rows
(119, 2) Populates the spreadsheet with the array data.
(136, 2) Redraws the spreadsheet.
(139, 2) This section cycles through the resources and tasks, copying and pasting the resource usage data on a resource by task basis.
(163, 2) Creates the Resources by Task report.
(164, 0) ProjTasks
(164, 2) Creates an array called ProjTasks of all the tasks in the project. The array contains the Unique ID and the Name for each task.
(167, 2) Unique_Tasks is a placeholder to get the list of all of the tasks to which the resource is assigned. Transpose changes the array into a list that is sent back to Microsoft Project in the next Request
(172, 2) Populates the spreadsheet with the array data.
(189, 2) Redraws the spreadsheet.
(192, 2) This section cycles through the resources and tasks, copying and pasting the resource usage data on a resource by task basis.
(.txt)