How to get the Excel sheet names using OLE object python testcomplete

This will help you https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/excel/working-via-com.html

I had the same issue, the forum topics such as https://community.smartbear.com/t5/TestComplete-Functional-Web/how-to-check-if-sheet-in-excel-driver-exist-or-not/td-p/104645 gave me a pointer or two but didn't really answer my question.  Though a bit of trial and error I arrived at:

def Main():
  #Routine to get list of worksheets from a work book
    
  #Location of workbook
  myResult = "C:\\Test_Development\\TR3462\\TR3462.xls"
  testCase = "TEST70204"
  
  #Open the new Excel workbook and sheet
  excel = Sys.OleObject["Excel.Application"]
  workBook = excel.Workbooks.Open(myResult)
  
  try:
    workSheet = workBook.Sheets.Item[testCase]
  except (RuntimeError):
    Log.Warning("Worksheet " + testCase + " 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.Quit();

I hope that will help

I am using - "openpyxl" module, we can get the sheet name (but you have to install - openpyxl)

import openpyxl

def getExcelSheetName():

    wbo = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

    wso = wbo.get_sheet_names()

    for shname in wso:

        Log.Message(shname)