title: Scraping data from Excel worksheets with different sheet names
body: <p>I have many excel spreadsheets with data in them. I want to find all excel spreadsheets, go to a specific sheet and find a particular cell. Each spread sheet corresponds to a patient, where one worksheet has the dose the patient received in a particular cell.</p>
  
  <p>Then i can plot the how the value from each spread sheet varies over time.  However, in some workbooks the worksheet is called different things, or is at a different index value. Is there any solution that doesn't involve me manually renaming/moving the 1000+ spreadsheet worksheets i have so they are all in the same format?</p>
  
  <p>This is the code i have that works if they are all at worksheet index 5. </p>
  
  <pre><code>#finds all excel files
  import os
  
  path = 'C:\Users\malonc01\Desktop\QC' 
  files = os.listdir(path)
  
  files_txt = [i for i in files if i.endswith('.xlsx')]
  print files_txt
  
  # Gets data from excel files:
  import xlrd
  Data = []
  Time = []
  
  for i in files_txt:
      workbook = xlrd.open_workbook(str(path) + "\\"  + str(i))
      worksheet1 = workbook.sheet_by_index(5) 
      Data.append(worksheet1.cell_value(10,8))
  </code></pre>
  
  <p>The only two solutions i can think of is: </p>
  
  <p>If you search each worksheet for text in a particular cell (each worksheet is formatted the exact same, just the name/index of the worksheet i want is different). Then use the worksheet that meets this criteria.</p>
  
  <p>or</p>
  
  <p>if the worksheet is called X "or" Y then open it and find the cell value. </p>
  
  <p>Both options i have not been able to find a way to make them work. I hope someone can point me in the right direction to the most simple solution. Thanks!</p>