The Sheets collection has to be accessed using Item just like the Cells collection.
For example, your line of code should change to this:
sheet = Excel.Workbooks.open(file).Sheets.Item["Sheet1"]
Hope that helps.
Here's the function I use to open an excel spreadsheet for a specific sheet and grab all the columns and their values. It uses the ACE Driver instead of opening Excel itself as an OLE Object. That way you can read from Spreadsheets without needing to have an Excel license on each Test Host. You can download the ACE Driver here
def read_excel_to_dict(file_path, sheet_name):
"""
Function will open an Excel file at the given sheet, then put the values into a dictionary with the
key being the column name. The top row in the Excel file is considered the Column Name. The top row
is only considered a Column if a registry key is set (it normally is by default).
See http://support.smartbear.com/viewarticle/70340/
Uses ACE driver (Excel runtime not required, but can't modify the spreadsheet)
Parameters
--------------
file_path : string
File path to the excel spreadsheet
sheet_name : string
Name of the specific sheet to be opened
Returns
---------
return_dict : dict
Dictionary object with each column (first row) mapped to its respective value (second row)
"""
DDT.ExcelDriver(file_path, sheet_name, True)
driver = DDT.CurrentDriver
driver.First()
return_dict = dict()
for i in range(DDT.CurrentDriver.ColumnCount):
return_dict[driver.ColumnName[i]] = aqConvert.VarToStr(driver.Value[i])
return return_dict
Hi ghuff2,
Thank you for your reply. I am sorry I didnot make it clear. I need to modify the sheet. Maybe import python library to handle this. I just think Testcomplete may has its own solution.
The Sheets collection has to be accessed using Item just like the Cells collection.
For example, your line of code should change to this:
sheet = Excel.Workbooks.open(file).Sheets.Item["Sheet1"]
Hope that helps.
