How do you data drive dependent UFT test scripts with Excel files from ALM?

Question

We have this really great and flexible automation architecture that we've developed over the last several months. Let me outline how the system is designed as a part of staging my question.

So we have these master-driver scripts that call a bunch of child scripts to do each of the pages/tabs on our website. Each page has it's own child script/action that way they can be interchangeable, turned on or off easily, and each of these child scripts/action has it's own data sheet that drives it in an Excel workbook. Even the master-driver script has it's own sheet that drives how it should behave when calling these child scripts/actions.

All of the sheets for the child scripts and the sheet for the master driver are in the same workbook, which is in an excel file, I'm gonna call it Lambert.xls. We have multiple copies of this file that contain different data sets that we want to drive our scrips with.

So currently the users should delete Lambert.xls and copy one of the other files and rename the copy to Lambert.xls. But this activity is prone to user-error, such as deleting the wrong file by accident, misspelling in the renaming, forgetting to copy the original file before renaming, etc...

Example file list:

Lambert.xls
Lambert_Maximum.xls
Lambert_Minimum.xls
Lambert_Professional.xls
Lambert_Amateur.xls

We have the case that multiple testers might need to execute different master-driver scripts at the same time, and these master-driver scripts would in many cases be executing the same scripts at the same time. Which if they are all referencing the same Lambert.xls then that would be a problem. So I found a way around this by creating a Configuration.xls file that lists all the names of all the master driver script names, and the paths to all of their corresponding copies of Lambert.xls that exist in different paths on the server.

Example:

MasterDriver1 \\LambertServer\Development\TestData\Test1\Lambert.xls
MasterDriver2 \\LambertServer\Development\TestData\Test2\Lambert.xls
etc...

Now MasterDriver1 calls another action in another test script by calling LoadAndRunAction and passing it a path. But before it does this the master driver script will do the following:

Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False

'...Code opens the Configuration.xls, passes the name of the current Master Driver, e.g. "MasterDriver1" and gets the path to the Lambert.xls that it should use.
'...Code closes the Configuration.xls file and sets the object pointer to nothing.

Set oMasterDriverWorkbook = oExcel.Workbooks.Open(strMasterWorkbookfileAndPath)
Set oMasterDriverSheet = oMasterDriverWorkbook.Worksheets(strMasterDriverSheetName)

If CInt(Environment("TestIteration")) = 1 And CInt(Environment("ActionIteration)) = 1 Then
     Call initGlobalDictionary()
     GlobalDictionary.Add "LambertAutomationDataObject", oMasterDriverWorkbook
End If

'...Code gets the master row count from the Master-driver sheet on the current version of Lambert.xls, and sets this up for the iteration loop.

For intCurrentRow = 2 To intMasterRowCount Step1
     GlobalDictionary.Add "CurrentRowNumber", intCurrentRow
     LoadAndRunAction "\\LambertServer\Development\Scripts\LambertChild1\", "LambertChild1", oneIteration
     LoadAndRunAction "\\LambertServer\Development\Scripts\LambertChild2\", "LambertChild2", oneIteration
     LoadAndRunAction "\\LambertServer\Development\Scripts\LambertChild3\", "LambertChild3", oneIteration
     GlobalDictionar.Remove "CurrentRowNumber"
Next 'intCurrentRow

The above was needed because running "LambertChild2" depends on running "LambertChild1" before it, and "LambertChild3" requires running "LambertChild1" & "LambertChild2" as a prerequisite. I'm trying to simplify the code a little and changing the names because it would be significantly more complex if I copied and pasted. So understand that there are many more child scripts and many more dependencies, but this is enough for the purposes of my question.

Now for an example child script.

Set oDataWorkbook = GlobalDictionary("LambertAutomationDataObject")
Set oDataSheet = oDataWorkbook.Worksheets(strDataSheet)

...Get a bunch of data from the sheet, which will be passed into the call to Lambert1Tab

intCurrentRow = GlobalDictionary("CurrentRowNumber")

Call Lambert1Tab(...)
Call WriteReporterEvent(...)

Set oDataSheet = Nothing
Set oDataWorkbook = Nothing

We decided to keep all the code that gets all the data from each of the sheets because it maintains the modular structure and keeps the Master Driver script from getting massively over-polluted with unnecessary code that would need to be re-written and maintained for each Master Driver script.

Therefore ALL the scripts, Master Driver scripts and child-scripts both depend on being able to load the Lambert.xls file as an Excel object:

Set oExcel = CreateObject("Excel.Application")

Now when I move all of these scripts into ALM, I don't have any troubles converting the references to the function libraries into ALM paths, or converting the reference to the Object Repository to an ALM path. But where I start to have problems is when I try to convert the Lambert.xls path into an ALM path.

If I leave that Lambert.xls paths the way they are, pointing as described above to:

\\LambertServer\Development\TestData\Test1\Lambert.xls

Then everything works just fine. However, when I change it to the path that I have uploaded to ALM:

[ALM] Resources\Lambert\TestData\Test1\Lambert.xls

It throws an error that the file cannot be found. If I upload the Lambert.xls file as a Data Table then UFT expects for it to behave as a Data Table not an Excel.xls file, and therefore I cannot get access to the object and pass the object around between child scripts. Furthermore if I put this Data Table for each of the child scripts then LambertChild1 script would run through all of it's iterations, then LambertChild2 would run through all of it's iterations...but recall that this is NOT the behavior that I need as LambertChild2 iteration1 depends on running LambertChild1 iteration1 as a prerequisite. So if LambertChild1 has run through iterations 1-30, then LambertChild2 runs, it was dependent on LambertChild1 iteration1, but the last one to run will have been LambertChild1 iteration30 which is no good at all, especially in such a complex and dependent environment as what we have developed.

If I upload the Lambert.xls file as a Test Resource then of course I get the above situation where an error is thrown that the file cannot be found.

The actual error message is:

'[ALM] Resources\Lambert\TestData\Test1\Lambert.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

So my question is does anybody know of any way through this blockade of ALM/UFT issues for reading Excel files in as objects from an ALM path? And one thing I thought was the name of the resource should match the name of the file so I tried that as well, but that didn't help.

Perhaps there is a way of loading an Excel file from a DataTable ALM Resource? If so what would the code for doing this look like and how would I make it work? Details please? Please be as precise with your answer as I have been describing the problem. I may not understand vague answers that refer to terms/processes that I am unfamiliar with.

System & Version Info: Windows7; IE8 (business requirements); UFT 12.0 Build 0511; ALM OTA Client 11.52.444.0; ALM UI 11.52.444.0; ALM WebGateClient 11.52.444.0; ALM Test Run Scheduler 11.52.444.00; ALM Sprinter 11.50.444; ALM Analysis Extension 11.50.444; ALM QC v11.50.444; ALM Lab Extension for functional & performance testing 11.50.1178

Sorry for being so long winded, but I wanted to be explicit in my question, rather than vague and have to answer a lot of questions, even if I overly complicated the issue.

Thanks in advance to anybody who gets through this novel! I appreciate every bit of help I can get as we've been stuck on this issue for several weeks now. I've been researching this but it's difficult to cut through the clutter of questions to get a straight answer on this specific issue.

Answer 1

Nice solution. The key here is that you are using Excel to load your spreadsheets, not QTP/UFT. Excel knows nothing about ALM and its file system. What you'll need to do is download the file locally (perhaps to a temp folder) and then pass the local file path to Excel to load the file.

From within QTP/UFT, you can use the 'QCConnection' object to interact with the active connection to ALM while running your test. Refer to the OTA API documentation for how to download a file from the Resources module.