Absent Member.
Absent Member.
757 views

Excel Extract from ALM 11

Jump to solution

Hello,

 

I'm trying to develop an extract that will pull out data from the test lab.  Its getting the following error "path/file access error" on the 

Set SQLResults = oCommand.Execute

command.  Anyone have any ideas?  Is my formatting incorrect? This works in the Analysis - Extract to Excel...I just want to be able to updated my Excel spreadsheet to support my custom pivot tables.

 

Sub Query()
 
 Dim qcServer, qcDomain, qcProject, qcUser, qcPassword, sSql
     
     qcServer = "http://xxxxxx:8080/qcbin/"
     qcDomain = "xxx"
     qcProject = "xxx"
     qcUser = "xxx"
     qcPassword = "xxx"
 
 
 Set tdc = CreateObject("tdapiole80.tdconnection")
 
 
 'Check to see that the tdc object exists
 If tdc Is Nothing Then
 MsgBox "The tdc object is empty"
 End If
 
 'Establish the connection and log in
 tdc.InitConnectionEx qcServer
 tdc.Login qcUser, qcPassword
 tdc.Connect qcDomain, qcProject
 
 MsgBox "Authenticated"
 
 'Create the tdc Command Object
 Set oCommand = tdc.Command
 
 'Build the query
  sSql = "SELECT " & _
            "CYCL_FOLD.CF_ITEM_NAME as 'Test Set Folder Name', " & _
            "CYCLE.CY_CYCLE as 'Test Set Name', " & _
            "TEST.TS_NAME as 'Test Case Name', " & _
            "TESTCYCL.TC_STATUS as 'Test Case Status' " & _
        "FROM CYCLE " & _
            "JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID " & _
            "JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID " & _
            "JOIN CYCL_FOLD ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID " & _
        "WHERE CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAAIAAA%' " & _
        "ORDER BY CYCL_FOLD.CF_ITEM_NAME ASC"
                 
 MsgBox sSql
 
 'Set the SQL command to the Test Coverage query
 oCommand.CommandText = sSql
         

 'Prepare the worksheet
 Worksheets("Results").Range("A:D").ClearContents
 
 'Set the header row titles.
 Worksheets("Results").Range("A1") = "Test Set Folder Name"
 Worksheets("Results").Range("B1") = "Test Set Name"
 Worksheets("Results").Range("C1") = "Test Case Name"
 Worksheets("Results").Range("D1") = "Test Case Status"
 
 MsgBox "Formated Results Sheet"
 
 'Start populating data on row 2 (leaving the header information above).
 iExcelRow = 2
 
 'Execute the query and store in the SQLResults resultset.
 
 Set SQLResults = oCommand.Execute
 
 
 'Iterate through the query results and populate the worksheet.
 For iRecord = 1 To SQLResults.RecordCount
                         
     'Write the values to the worksheet
      Worksheets("Results").Range("A" & iExcelRow) = SQLResults.FieldValue("Test Set Folder Name")
      Worksheets("Results").Range("B" & iExcelRow) = SQLResults.FieldValue("Test Set Name")
      Worksheets("Results").Range("C" & iExcelRow) = SQLResults.FieldValue("Test Case Name")
      Worksheets("Results").Range("D" & iExcelRow) = SQLResults.FieldValue("Test Case Status")
     
     'Increment the iteration
      iExcelRow = iExcelRow + 1
      SQLResults.Next
 Next
 
 
 'Disconnect from Quality Center
 If tdc.Connected = True Then
 tdc.Disconnect
 End If
 
 'Log off the server
 If tdc.LoggedIn Then
 tdc.Logout
 End If
 
 'Release the TDConnection object.
 tdc.ReleaseConnection
 
 'Adjust the column width
 Worksheets("Results").Columns("A:D").EntireColumn.AutoFit
 
 Set SQLResults = Nothing
 Set oCommand = Nothing
 Set tdc = Nothing
 
 MsgBox "Done"
 
 End Sub

   I would also like to add that I'm the TDAdmin at the project level.

Tags (2)
0 Likes
1 Solution

Accepted Solutions
Absent Member.
Absent Member.

I resolved my own issue...lol

 

It seems that ALM did not like the AS statement in the select.  I adjusted my query to 

 

 sSql = "SELECT " & _
            "CYCL_FOLD.CF_ITEM_NAME, " & _
            "CYCLE.CY_CYCLE, " & _
            "TEST.TS_NAME, " & _
            "TESTCYCL.TC_STATUS " & _
        "FROM CYCLE " & _
            "JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID " & _
            "JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID " & _
            "JOIN CYCL_FOLD ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID " & _
        "WHERE CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAAIAAA%' " & _
        "ORDER BY CYCL_FOLD.CF_ITEM_NAME ASC"

it it works perfect.

View solution in original post

0 Likes
1 Reply
Absent Member.
Absent Member.

I resolved my own issue...lol

 

It seems that ALM did not like the AS statement in the select.  I adjusted my query to 

 

 sSql = "SELECT " & _
            "CYCL_FOLD.CF_ITEM_NAME, " & _
            "CYCLE.CY_CYCLE, " & _
            "TEST.TS_NAME, " & _
            "TESTCYCL.TC_STATUS " & _
        "FROM CYCLE " & _
            "JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID " & _
            "JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID " & _
            "JOIN CYCL_FOLD ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID " & _
        "WHERE CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAAIAAA%' " & _
        "ORDER BY CYCL_FOLD.CF_ITEM_NAME ASC"

it it works perfect.

View solution in original post

0 Likes
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.