(1, 2) Names the sheet "MacroSheet", so users can change the name from TRACKING.XLM.
(1, 3) 1
(1, 4) 286
(1, 5) 6
(1, 6) 88
(1, 8) OK
(2, 2)
(2, 3) 21
(2, 4) 160
(2, 5) 55
(2, 6) 100
(2, 7) 50
(2, 8) r3c11:r6c11
(2, 9) 2
(2, 10) Days
(2, 11) Minutes
(2, 12) All Resources
(3, 2) Creates a new Microsoft Excel spreadsheet; adds titles and formatting for the titles
(3, 3) 21
(3, 4) 160
(3, 5) 31
(3, 6) 100
(3, 7) 50
(3, 8) r3c12:r6c12
(3, 9) 2
(3, 10) Weeks
(3, 11) Hours
(4, 2)
(4, 3) 5
(4, 4) 10
(4, 5) 54
(4, 8) Timescale:
(4, 10) Months
(4, 11) Days
(5, 2)
(5, 3) 2
(5, 4) 286
(5, 5) 30
(5, 6) 88
(5, 8) Cancel
(5, 10) Years
(5, 11) Weeks
(6, 0) Spreadsheet
(6, 2) Names the new sheet "Spreadsheet".
(6, 3) 5
(6, 4) 10
(6, 5) 32
(6, 8) Work Units:
(7, 3) 5
(7, 4) 10
(7, 5) 10
(7, 8) Resource:
(8, 3) 21
(8, 4) 100
(8, 5) 8
(8, 6) 160
(8, 7) 108
(8, 8) ResourceList
(8, 9) 1
(29, 2) Redraws the spreadsheet.
(32, 0) DDEChannel1
(32, 2) Gets the name of the active project in Microsoft Project on the system channel, and closes the system channel.
(33, 0) ActiveProj
(33, 2) If there is no project open, the macro ends here.
(42, 0) DDEChannel2
(42, 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).
(43, 0) NumTasks
(43, 2) Checks to see that there are both tasks and resources in the project.
(44, 0) NumResources
(56, 0) ProjStart
(56, 2) Fills in the project data on the report.
(57, 0) ProjFinish
(74, 0) ListSep
(81, 0)
(85, 0)
(88, 2) Displays dialog box asking user for their preferences.
(90, 0)
(90, 2) Changes ProjStart and ProjFinish to user dates, if they changed them. Also sets Default Work Units, based on user input, and defines ProjResources as either all resources or selected resources.
(96, 0)
(96, 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.
(99, 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.
(100, 2) DATA.SERIES fills the timescale to match the time period specified.
(101, 2) This sets the titles for the Work columns.
(111, 2) Redraws the spreadsheet.
(114, 2) Sets up Microsoft Project with the proper view.
(116, 2) Change_Timescale is a string sent to Microsoft Project using SendKeys to set up the appropriate timescale.
(126, 2) Redraws the spreadsheet.
(129, 2) Sets up 2 arrays for each resource -- Res# and ResAssmtData#.
(131, 0)
(132, 0)
(132, 2) Res# is an array of the Names and ID's for every task to which the resource is assigned. The ID is needed for EditGoTo in Microsoft Project.
(133, 0)
(133, 2) ResAssmtData# is an array containing the Task and Resource Unique ID's, Work, and Actual Work.
(137, 0) Rows
(137, 2) Populates the spreadsheet with the array data. Also adds formulas to calculate total actual work and work.
(158, 2) Redraws the spreadsheet.
(161, 2) This section cycles through the resources and tasks, copying and pasting the resource usage data on a resource by task basis.
(163, 2) After the information is in the spreadsheet, the data that is not actual workis erased, so that the resource usage data in Microsoft Excel reflects actual work only.
(165, 2) At the end, the DDE conversation is terminated.
(197, 2) Formats the spreadsheet.
(237, 2) Creates the Update button on the spreadsheet.
(241, 2) This section locks the cells with formulas in them.
(247, 2) Copies all of the resource arrays to the spreadsheet since they are needed for the Update section of the macro.
(254, 0)
(263, 2) Update section. This removes protection from the document, finds out if Microsoft Project is running, and if the file that created the worksheet is available. It then initiates a DDE conversation with the source spreadsheet.
(264, 2) Unprotects the spreadsheet and starts a DDE conversation with Microsoft Project.
(267, 0) DDEChannel
(273, 2) Moves the resource arrays to the macro sheet.
(285, 0) Rows2
(285, 2) This cycles through the cells, comparing the last value from Microsoft Project with the value in the cells. If they are not equal, the new values are poked to Microsoft Project.
(.txt)