In my keyword test, I need to retrieve the data from multiple sheets, using single DDT driver object from the same excel file. I am using 'Sheet' variable to specify the sheet value at run time. Sheet value is getting change at runtime but value is not retriving from the related sheet.
The DDT.ExcelDriver object can only operate on one sheet at a time out of the box. So, if the sheet name is changing during runtime, that will most definitely "break" any use of DDT.ExcelDriver or the Data Driven operation of Keyword Tests.
Think of each "sheet" as the results of a single SELECT query against a database. So, by default, you only work with one sheet at a time.
That said, if you're using code, you can access the CommandText of the query like so:
var driverObject = DDT.ExcelDriver('C:\\MyPath\\MyFile.xls', 'MySheet', true);
driverObject.ADOCommandObject.CommandText = <fill in your select query>
So, you COULD technically write a query to combine data from multiple sheets into your driver object.
Also take a look at DDT.DriverByName and DDT.CurrentDriver. You might be able to use those without relying on the same driver for everything.
https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/drivers.html
You can also setup connections to excel sheets as an ADO connection to select things from.
I did something similar for the framework webinar back in November:
https://github.com/cunderw/smartbear-november/blob/master/Scripts/NOV_DATA.js
