cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot select specific excel sheet in Python with OLE Object

SOLVED
Contributor

Cannot select specific excel sheet in Python with OLE Object

Hi,

 

I followed the link below but not successful when trying to open an excel file with Sheet2 enable.

 

https://community.smartbear.com/t5/TestComplete-General-Discussions/Having-Trouble-switching-to-diff...

 

My Codes:

 

def openExcel():
    Log.Message(os.getcwd())
    sheet1 = "Testcase1"
    sheet2 = "Testcase2"
    fileName = "C:\\Works\\myTestcases.xlsx"

    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName)
        mySheet = workBook.Sheets.Item["sheet2"]

    except (RuntimeError):
        Log.Warning("Worksheet " + sheet2 + " was not found.");

 

Result:

Got the warning result saying: "Worksheet Testcase2 was not found.

 

I don't know what I did wrong. Please shed some light! Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Contributor

Re: Cannot select specific excel sheet in Python with OLE Object

It took time and I finally found the solution.

To select a specific sheet of an excel file, an Activate method must be called. Per Microsoft doc: "Calling this method is equivalent to clicking the sheet's tab".

 

My working codes is below:

def openExcel():
    fileName = "C:\\Works\\Testcases.xlsx"
    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName) 
    except (RuntimeError):
        Log.Warning("File " + fileName + " was not found.");
    numFound = workBook.Sheets.Count
    Log.Message("This workbook has " + str(numFound) + " worksheets.")
    for i in range(0,numFound):
        sheetName = workBook.Sheets.Item[i+1].Name
        Log.Message(str(sheetName))
        excel.Worksheets.Item[sheetName].Activate() #Clicking the tab with Activate Method
        RowCount = excel.ActiveSheet.UsedRange.Rows.Count
        ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
        Log.Message("Row Count = " + VarToStr(RowCount))
        Log.Message("Col count = " + VarToStr(ColumnCount))
        cell = VarToStr(excel.Cells.Item[2, 1])
        Log.Message(cell)
    excel.Quit()
5 REPLIES 5
Community Hero

Re: Cannot select specific excel sheet in Python with OLE Object

Change the following line

 

mySheet = workBook.Sheets.Item[sheet2]

 

sheet2 is the variable name so it should not be listed in quotes.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Cannot select specific excel sheet in Python with OLE Object

Now, I don't see any warning. However, when stopping at a break point, I don't see "sheet2" gets select and active. And all the info printed out are from "sheet1".

 

So sheet2 still not be selected, and its info still not being able to printout. Please help again!

 

def openExcel():
    Log.Message(os.getcwd())
    sheet1 = "Testcase1"
    sheet2 = "Testcase2"
    fileName = "C:\\Works\\myTestcases.xlsx"

    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName)
        mySheet = workBook.Sheets.Item[sheet2]

    except (RuntimeError):
        Log.Warning("Worksheet " + sheet2 + " was not found.")

 

    RowCount = excel.ActiveSheet.UsedRange.Rows.Count
    ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
    Log.Message("Row Count = " + VarToStr(RowCount))
    Log.Message("Col count = " + VarToStr(ColumnCount))
    cell = VarToStr(excel.Cells.Item[2, 1])
    Log.Message(cell)
    excel.Quit() 

 

 

Community Hero

Re: Cannot select specific excel sheet in Python with OLE Object

Well, you set mySheet to = sheet2 but you haven't indicated that sheet2 is your active sheet.  That's something you need to do first before you can use "Activesheet"

 

To do that, before you start working with the sheet, call

 

mySheet.Activate()


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Cannot select specific excel sheet in Python with OLE Object

I try below per your suggestion and sheet2 still not be able to be selected.

 

    mySheet.Activate

    # OR mySheet.Select 

    RowCount = excel.ActiveSheet.UsedRange.Rows.Count
    ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
    Log.Message("Row Count = " + VarToStr(RowCount))
    Log.Message("Col count = " + VarToStr(ColumnCount))

 

Contributor

Re: Cannot select specific excel sheet in Python with OLE Object

It took time and I finally found the solution.

To select a specific sheet of an excel file, an Activate method must be called. Per Microsoft doc: "Calling this method is equivalent to clicking the sheet's tab".

 

My working codes is below:

def openExcel():
    fileName = "C:\\Works\\Testcases.xlsx"
    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName) 
    except (RuntimeError):
        Log.Warning("File " + fileName + " was not found.");
    numFound = workBook.Sheets.Count
    Log.Message("This workbook has " + str(numFound) + " worksheets.")
    for i in range(0,numFound):
        sheetName = workBook.Sheets.Item[i+1].Name
        Log.Message(str(sheetName))
        excel.Worksheets.Item[sheetName].Activate() #Clicking the tab with Activate Method
        RowCount = excel.ActiveSheet.UsedRange.Rows.Count
        ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
        Log.Message("Row Count = " + VarToStr(RowCount))
        Log.Message("Col count = " + VarToStr(ColumnCount))
        cell = VarToStr(excel.Cells.Item[2, 1])
        Log.Message(cell)
    excel.Quit()
New Here?
Join us and watch the welcome video:
6 / 7 API Testing Mistakes Video
Top Kudoed Authors