If sheet does not exist go to next Python

Question

I'm writing a code that allows you to select the tables in a workbook sheet and send it by email. But it can happen that a sheet does not exist because there is no data. I would like to know how I have the absence of a sheet and move to the other sends and also create a bouble for each sheet of the workbook instead of executing the same code on all the sheets. I hope I have been precise.

I tried to continue without the error but the code (#import warnings #warnings.filterwarnings("ignore", category=DeprecationWarning)) does not work like on "error resume next" in vba Do you have another solution.

import win32com.client as win32
import xlwings as xw
import pandas as pd 
import openpyxl

#import warnings
#warnings.filterwarnings("ignore", category=DeprecationWarning)

#wb = xw.Book()
wb = r"D:/Users/Desktop/Infos/MasterFile.xlsx"
data = xw.Book(wb)
Mylist_205 = data.sheets('Sheet_205')

selection_205=data.sheets('Sheet_205').used_range
Mylist_205.used_range.copy()

outlook = win32com.client.Dispatch("Outlook.Application")
# Create a new MailItem object
msg = outlook.CreateItem(0)
msg.To='[email protected]'
msg.Subject = 'Subject'
msg.GetInspector.WordEditor.Range(Start=0, End=0).Paste()
msg.Display()
msg.Send()

Error:

Traceback (most recent call last):
  File "C:/Users/sheets.py", line 17, in <module>
    Mylist_205 = data.sheets('Sheet_205')
  File "C:\Python\Python310\lib\site-packages\xlwings\main.py", line 4893, in __call__
    return Sheet(impl=self.impl(name_or_index))
  File "C:\Python\Python310\lib\site-packages\xlwings\_xlwindows.py", line 877, in __call__
    return Sheet(xl=self.xl(name_or_index))
  File "C:\Python\Python310\lib\site-packages\xlwings\_xlwindows.py", line 208, in __call__
    v = self._inner(*args, **kwargs)
  File "C:\Users\AppData\Local\Temp\gen_py\3.10\00020813-0000-0000-C000-000000000046x0x1x9.py", line 36625, in __call__
    ret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index
pywintypes.com_error: (-2147352567,', (0, None, None, None, 0, -2147352565), None)

Answer 1

The first problem is that you are using () for .sheets. It is supposed to be []. I refactored it a bit because it is kind of confusing. I didn't test the email part of it, so I will only post what I tested.

I'm not sure what you are trying to put into your email, but after my code you can convert it to a dataframe or whatever.

One more thing about xlwings. Many times if you are working in a terminal, close the Excel application and then open the app again and try to run code against it, you might get the same pywintypes error. Try using the importlib module before you go nuts trying to debug it.

import importlib
import xlwings as xw

importlib.reload(xw)

Dummy file named MasterFile.xlsx:

import xlwings as xw

file_str= r"D:/Users/Desktop/Infos/MasterFile.xlsx"
wb = xw.Book(fpath)
dummy_sheets = [
    'Sheet_201', 'Sheet_202', 'Sheet_204', 'Sheet_205', 'Sheet_206'
]
data = []
for sht in dummy_sheets:
    try:
        sht = wb.sheets[sht]
        data.append(sht.used_range.value)
    except:
        # Do something else here.
        print('Missing sheet.')