• State Not Answered
  • Replies replies
    4
  • Subscribers subscribers
    8
  • Views views
    767
  • Users 0 members are here
Related Discussions
Options

VBA to fetch defect from HP-ALM to Excel

MigrationDeletedUser

Hi All,

Thanks for reading this and Thanks in advance if you can resolve my problem.

I am very new to scripting thing. I am developing and excel vba to fetch defect data from QC.

QC link, Uid, Pswd, Detected_in_cycle will be passed as parameter through customization sheet in my excel.

Please look at the below code and let me know where am i going worng. I would be very much thankful if any1 could rectify this code.

Many Thanks

 

Sub defect_dump()

    Dim time1
    time1 = Timer
    Application.Calculation = xlManual
    Dim url As String
    Dim qcConnectionObj
    url = ActiveWorkbook.Sheets("Customization").Cells(4, 4)
    Set qcConnectionObj = CreateObject("tdapiole80.tdconnection")
    qcConnectionObj.InitConnectionEx (url)
    qcConnectionObj.login ActiveWorkbook.Sheets("Customization").Cells(5, 4), ActiveWorkbook.Sheets("Customization").Cells(6, 4)
    qcConnectionObj.Connect ActiveWorkbook.Sheets("Customization").Cells(7, 4), ActiveWorkbook.Sheets("Customization").Cells(8, 4)
    MsgBox "Quality Centre Connected"


Call ExportDefects

'qcConnectionObj.Disconnect
'qcConnectionObj.Logout
'qcConnectionObj.ReleaseConnection

End Sub

Function ExportDefects()
Dim BugFactory, BugList, BgFilter
Set BugFactory = qcConnectionObj.BugFactory
Set BgFilter = BugFactory.Filter
BgFilter.Filter("BG_DETECTED_IN_RCYC") = ActiveWorkbook.Sheets("Customization").Cells(14, 4)
Set BugList = BugFactory.NewList("") 'Get a list of all the defects.

Dim Bug, Excel, Sheet
Set Excel = ActiveWorkbook.Sheets("Sheet2") 'Open Excel
'Excel.WorkBooks.Add() 'Add a new workbook
'Get the first worksheet.
Set Sheet = Excel.ActiveSheet
Sheet.Name = "Defects"

With Sheet.Range("A1:H1")
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 10
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 15 'Light Grey
End With

Sheet.Cells(1, 1) = "Summary"
Sheet.Cells(1, 2) = "Detected By"
Sheet.Cells(1, 3) = "Detected on Date"
Sheet.Cells(1, 4) = "Status"
Sheet.Cells(1, 5) = "Subject"
Sheet.Cells(1, 6) = "Severity"
Sheet.Cells(1, 7) = "Priority"
Sheet.Cells(1, 8) = "Assigned To"

'Call PrintFields(BugFactory)

Dim Row
Row = 2
'Iterate through all the defects.

'For Each Bug In BugList and Bug.Filed("BG_USER_01").value="time"
'Save a specified set of fields.
Sheet.Cells(Row, 9).Value = Bug.Field("BG_BUG_ID")
Sheet.Cells(Row, 1).Value = Bug.Summary
Sheet.Cells(Row, 2).Value = Bug.DetectedBy
Sheet.Cells(Row, 3).Value = Bug.Field("BG_DETECTION_DATE")
Sheet.Cells(Row, 4).Value = Bug.Status
Sheet.Cells(Row, 5).Value = Bug.Field("BG_SUBJECT")
Sheet.Cells(Row, 6).Value = Bug.Field("BG_SEVERITY")
Sheet.Cells(Row, 7).Value = Bug.Priority
Sheet.Cells(Row, 8).Value = Bug.AssignedTo
Row = Row 1
'Next

Excel.Columns.AutoFit

'Save the newly created workbook and close Excel.
Excel.ActiveWorkbook.SaveAs ("C:\" & sProject & "_DEFECTS.xls")
Excel.Quit
End Function

 

  • 0

    Try this.

    Function ExportDefects()

    Dim BugFactory, BugList
    Dim BgFilter As TDFilter


    Set BugFactory = ConnectionMethods.OQCConnection.BugFactory
    Set BgFilter = BugFactory.Filter


    BgFilter.Filter("BG_STATUS") = "Open"
    Set BugList = BgFilter.NewList 'Get a list of all the defects.


    Dim Bug, Excel, Sheet
    Set Excel = ActiveWorkbook.Sheets("Sheet2") 'Open Excel
    'Excel.WorkBooks.Add() 'Add a new workbook
    'Get the first worksheet.
    Set Sheet = ActiveSheet
    Sheet.Name = "Defects"

    With Sheet.Range("A1:H1")
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 10
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Interior.ColorIndex = 15 'Light Grey
    End With

    Sheet.Cells(1, 1) = "Summary"
    Sheet.Cells(1, 2) = "Detected By"
    Sheet.Cells(1, 3) = "Detected on Date"
    Sheet.Cells(1, 4) = "Status"
    Sheet.Cells(1, 5) = "Subject"
    Sheet.Cells(1, 6) = "Severity"
    Sheet.Cells(1, 7) = "Priority"
    Sheet.Cells(1, 8) = "Assigned To"

    'Call PrintFields(BugFactory)

    Dim Row
    Row = 2
    'Iterate through all the defects.

    For Each Bug In BugList
    'Save a specified set of fields.
    Sheet.Cells(Row, 9).Value = Bug.Field("BG_BUG_ID")
    Sheet.Cells(Row, 1).Value = Bug.Summary
    Sheet.Cells(Row, 2).Value = Bug.DetectedBy
    Sheet.Cells(Row, 3).Value = Bug.Field("BG_DETECTION_DATE")
    Sheet.Cells(Row, 4).Value = Bug.Status
    Sheet.Cells(Row, 5).Value = Bug.Field("BG_SUBJECT")
    Sheet.Cells(Row, 6).Value = Bug.Field("BG_SEVERITY")
    Sheet.Cells(Row, 7).Value = Bug.Priority
    Sheet.Cells(Row, 8).Value = Bug.AssignedTo
    Row = Row 1
    Next


    Excel.Columns.AutoFit

    'Save the newly created workbook and close Excel.
    'Excel.ActiveWorkbook.SaveAs ("C:\" & sProject & "_DEFECTS.xls")
    'Excel.Quit
    Call ConnectionMethods.DisConnectALM

    End Function

     

    I have taken out some of the excel stuff, changing sheets, closing workbook, not my forte really, but the defect grabbing code should now work as desired. Just a couple of small changes.

  • 0
    in reply to irtuk

    I am new to VBA scripts.

    Could you please help me with the code that successfully gets the defect from ALM. Would like to pass the ALM details as a parameter from excel as it varies each time for the project.

     

  • 0
    in reply to mahe19

    Hi,

    Please have a look at the API references - you could either use the "classic" OTA or the "modern" REST API to fetch defects from Excel. There are good code samples in both references. You can access the references in the ALM Online Help at Advanced Help > API Reference

    If you only need defects in Excel you could also export them manually from the defect grid - or use the Business Views?

    Regards,
    Dirk

    Developer of the ALM Octane Developer Tools & GDPR, POPIA, CCPA Content Packs (Marketplace)

  • 0
    in reply to mahe19

    You can use the export defects option to get defects into an excel sheet. Then use excel formula and/or pivot tables to get the desired information out of it.

     

    Always worth giving it a try versus using VBA code especially if don’t have much expertise with VBA code.

Resources

Support
Documentation
Training
CyberRes Academy
Partner Portal
Contact us
Compliance
Help
Company
Privacy Policy
Terms of Use
Accessibility
Anti-Slavery Statement
Support
How To Buy
Careers
Investor Relations
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.