In the Script Editor add a new sub (or modify the sub if it already exists): Create a new sub (Bug_Update Hours) as follows. It looks like there are two solutions to the problem – one involving a bit of SQL to join the rows for the change in Status to Closed and the change in Modified date; the other (simpler) solution appears to be the BG_CLOSING_DATE field. AP_FIELD_NAME = 'BG_VTS' From the Defects module Select Columns, Visible Columns = Defect ID, Status, Closing Date. Back to Project Customization | Project Entities to verify the name of the field.Here we’re assuming that BG_USER_01 to BG_USER_04 are the forecasting fields containing an integer representing “number of days”, and BG_USER_05 is the new summed field – replace these with the relevant field names from your own project: Sub Bug_Update Hours On Error Resume Next 'Update Total Hours Bug_Fields(BG_USER_05). To confirm the validity of the two solutions I need to test them. AP_NEW_VALUE as Modified FROM AUDIT_PROPERTIES AP LEFT JOIN AUDIT_LOG AU ON AP. The BG_CLOSING_DATE label name has changed to Due Date in my project, so “Due Date” is the column I need to select.
An examination of the defect history of the non-matches shows that the reason for the differences is due to defects that have been Closed, subsequently reopened, and then Closed again.
The BG_CLOSING_DATE field appears to update with the date that the defect was last set to Closed.
Here I will teach you the first step which you should know before doing any automation related to Quality Center using Excel Macro.
That first step is nothing but connecting to the Quality Center using excel macro. Wait..." ' Create a Connection object to connect to Quality Center Set td Connection = Create Object("TDApi Ole80.
The Test: I extract the following two reports: From the Excel Report Generator SELECT AU. Select Columns, Visible Columns = Defect ID, Status, Due Date.
First Test: do I only have Closed defects with a BG_CLOSING_DATE field completed? I have two defects that are not yet Closed that have dates set.Second Test: do the dates match between the SQL query and the Defects module extract?A quick vlookup between the two extracts shows some non-matches.Login qc ID, qc PWD 'connecting to the domain and project td Connection. Using above code, now you are connected with Quality center for a given Domain and project.Connect qc Domain, qc Project 'On successfull login display message in Status bar Application. QC Connection is done Successfully" Exit Sub err: 'Display the error message in Status bar Application. Object td Connection as defined above can be used to access each and every objects available in the Quality Center, like Test Cases, Requirements, Defects etc.Now I remember why the field label has been changed – for the past month or so we’ve been using the field as the “Expected” closing date for forecasting.