Repository: robocorp/rpaframework
Issue: read_from_cells AttributeError: 'str' object has no attribute 'Cells'

**General information:**

Windows 10 64bits
Python 3.8.6
rpaframework==6.5.0

**Steps to reproduce:**

from RPA.Excel.Application import Application

app = Application()
app.open_application(visible=True)
app.open_workbook(r'C:\\pry\\rpaframework\\challenge.xlsx')
app.set_active_worksheet(sheetname='Sheet1')
last_row = app.find_first_available_row()

for x in range(last_row[0]):
    row = app.read_from_cells(worksheet="Sheet1", row=x, column=1)
    print("A: " + row)


**Error:**

Traceback (most recent call last):
  File ".\\test_rpa.py", line 23, in <module>
    row = app.read_from_cells(worksheet="Sheet1", row=x, column=1)
  File "C:\\proyectos\\rpaframework\\.venv\\lib\\site-packages\\RPA\\Excel\\Application.py", line 222, in read_from_cells
    cell_value = worksheet.Cells(row, column).Value
AttributeError: 'str' object has no attribute 'Cells'


[challenge.xlsx](https://github.com/robocorp/rpaframework/files/5512063/challenge.xlsx)


Changed files:
packages/main/src/RPA/Excel/Application.py

diff --git a/packages/main/src/RPA/Excel/Application.py b/packages/main/src/RPA/Excel/Application.py
index 1e1966610..0d7711bf4 100644
--- a/packages/main/src/RPA/Excel/Application.py
+++ b/packages/main/src/RPA/Excel/Application.py
@@ -1,11 +1,38 @@
 import logging
 import platform
+import struct
+from itertools import count
 from pathlib import Path
 from typing import Any
-
+from contextlib import contextmanager
 
 if platform.system() == "Windows":
+    import win32api
     import win32com.client
+    import pywintypes
+
+
+def _to_unsigned(val):
+    return struct.unpack("L", struct.pack("l", val))[0]
+
+
+@contextmanager
+def catch_com_error():
+    """Try to convert COM errors to human readable format."""
+    try:
+        yield
+    except pywintypes.com_error as err:  # pylint: disable=no-member
+        if err.excepinfo:
+            try:
+                msg = win32api.FormatMessage(_to_unsigned(err.excepinfo[5]))
+            except Exception:  # pylint: disable=broad-except
+                msg = err.excepinfo[2]
+        else:
+            try:
+                msg = win32api.FormatMessage(_to_unsigned(err.hresult))
+            except Exception:  # pylint: disable=broad-except
+                msg = err.strerror
+        raise RuntimeError(msg) from err
 
 
 class Application:
@@ -58,8 +85,7 @@ class Application:
         self.logger = logging.getLogger(__name__)
         self.app = None
         self.workbook = None
-        self.workbook_name = None
-        self.active_worksheet = None
+        self.worksheet = None
 
         if platform.system() != "Windows":
             self.logger.warning(
@@ -74,32 +100,46 @@ class Application:
         :param visible: show window after opening
         :param display_alerts: show alert popups
         """
-        self.app = win32com.client.gencache.EnsureDispatch("Excel.Application")
-        self.logger.debug(self.app)
-        if hasattr(self.app, "Visible"):
-            self.app.Visible = visible
+        with catch_com_error():
+            self.app = win32com.client.gencache.EnsureDispatch("Excel.Application")
+            self.logger.debug("Opened application: %s", self.app)
 
-        # show eg. file overwrite warning or not
-        if hasattr(self.app, "DisplayAlerts"):
-            self.app.DisplayAlerts = display_alerts
+            if hasattr(self.app, "Visible"):
+                self.app.Visible = visible
+
+            # Show eg. file overwrite warning or not
+            if hasattr(self.app, "DisplayAlerts"):
+                self.app.DisplayAlerts = display_alerts
 
     def close_document(self, save_changes: bool = False) -> None:
         """Close the active document (if open)."""
-        if self.app is not None and hasattr(self.app, "ActiveDocument"):
-            self.app.ActiveDocument.Close(save_changes)
+        if not self.workbook:
+            return
+
+        with catch_com_error():
+            self.workbook.Close(save_changes)
+
+        self.workbook = None
+        self.worksheet = None
 
     def quit_application(self, save_changes: bool = False) -> None:
         """Quit the application."""
-        if self.app is not None:
-            if self.workbook:
-                self.workbook.Close(save_changes)
-            self.close_document(save_changes)
+        if not self.app:
+            return
+
+        self.close_document(save_changes)
+        with catch_com_error():
             self.app.Quit()
-            self.app = None
+
+        self.app = None
 
     def add_new_workbook(self) -> None:
         """Adds new workbook for Excel application"""
-        self.workbook = self.app.Workbooks.Add()
+        if not self.app:
+            raise ValueError("Excel application is not open")
+
+        with catch_com_error():
+            self.workbook = self.app.Workbooks.Add()
 
     def open_workbook(self, filename: str) -> None:
         """Open Excel by filename
@@ -108,19 +148,22 @@ class Application:
 
         :param filename: path to filename
         """
-        if self.app is None:
+        if not self.app:
             self.open_application()
-        excel_filepath = str(Path(filename).resolve())
-        self.workbook_name = Path(filename).name
-        self.logger.info("Opening workbook: %s", excel_filepath)
-        try:
-            self.workbook = self.app.Workbooks(excel_filepath)
-        except Exception as e:  # pylint: disable=broad-except
-            self.logger.debug(str(e))
-            self.logger.info("trying to open workbook by another method")
-            self.workbook = self.app.Workbooks.Open(excel_filepath)
+
+        path = str(Path(filename).resolve())
+        self.logger.info("Opening workbook: %s", path)
+
+        with catch_com_error():
+            try:
+                self.workbook = self.app.Workbooks(path)
+            except Exception as exc:  # pylint: disable=broad-except
+                self.logger.debug(str(exc))
+                self.logger.info("Trying to open workbook by another method")
+                self.workbook = self.app.Workbooks.Open(path)
+
         self.set_active_worksheet(sheetnumber=1)
-        self.logger.debug("Workbook: %s", self.workbook)
+        self.logger.debug("Current workbook: %s", self.workbook)
 
     def set_active_worksheet(
         self, sheetname: str = None, sheetnumber: int = None
@@ -130,10 +173,14 @@ class Application:
         :param sheetname: name of Excel sheet, defaults to None
         :param sheetnumber: index of Excel sheet, defaults to None
         """
-        if sheetnumber:
-            self.active_worksheet = self.workbook.Worksheets(int(sheetnumber))
-        elif sheetname:
-            self.active_worksheet = self.workbook.Worksheets(sheetname)
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
+        with catch_com_error():
+            if sheetnumber:
+                self.worksheet = self.workbook.Worksheets(int(sheetnumber))
+            elif sheetname:
+                self.worksheet = self.workbook.Worksheets(sheetname)
 
     def add_new_sheet(
         self, sheetname: str, tabname: str = None, create_workbook: bool = True
@@ -142,19 +189,25 @@ class Application:
         it does not exist.
 
         :param sheetname: name for sheet
-        :param tabname: name for tab, defaults to None
+        :param tabname: name for tab (deprecated)
         :param create_workbook: create workbook if True, defaults to True
         :raises ValueError: error is raised if workbook does not exist and
             `create_workbook` is False
         """
         self.logger.info("Adding sheet: %s", sheetname)
-        if self.workbook is None:
+
+        if tabname:
+            self.logger.warning("Ignoring deprecated argument 'tabname'")
+
+        if not self.workbook:
             if not create_workbook:
                 raise ValueError("No workbook open")
             self.add_new_workbook()
-        self.active_worksheet = self.app.Worksheets(sheetname)
-        if tabname:
-            self.active_worksheet.Name = tabname
+
+        with catch_com_error():
+            last = self.app.Worksheets(self.app.Worksheets.Count)
+            self.worksheet = self.app.Worksheets.Add(After=last)
+            self.worksheet.Name = sheetname
 
     def find_first_available_row(
         self, worksheet: Any = None, row: int = 1, column: int = 1
@@ -167,8 +220,7 @@ class Application:
         :return: row or None
         """
         cell = self.find_first_available_cell(worksheet, row, column)
-        self.logger.debug("First available cell: %s", cell)
-        # Note. keep return type for backward compability for now
+        # Note: keep return type for backward compability for now
         # return cell[0] if cell else None
         return cell
 
@@ -182,16 +234,18 @@ class Application:
         :param column: starting column for search, defaults to 1
         :return: tuple (row, column) or (None, None) if not found
         """
-        empty_found = False
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
         if worksheet:
             self.set_active_worksheet(worksheet)
 
-        while empty_found is False:
-            cell_value = self.active_worksheet.Cells(row, column).Value
-            if cell_value is None:
-                empty_found = True
-                return (row, column)
-            row += 1
+        with catch_com_error():
+            for current_row in count(int(row)):
+                cell = self.worksheet.Cells(current_row, column)
+                if cell.Value is None:
+                    return current_row, column
+
         return None, None
 
     def write_to_cells(
@@ -213,18 +267,24 @@ class Application:
         :param formula: possible format to set, defaults to None
         :raises ValueError: if cell is not given
         """
-        worksheet = worksheet if worksheet else self.active_worksheet
-        if row is None and column is None:
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
+        if row is None or column is None:
             raise ValueError("No cell was given")
-        else:
-            row = int(row)
-            column = int(column)
-        if number_format:
-            worksheet.Cells(row, column).NumberFormat = number_format
-        if value:
-            worksheet.Cells(row, column).Value = value
-        if formula:
-            worksheet.Cells(row, column).Formula = formula
+
+        if worksheet:
+            self.set_active_worksheet(worksheet)
+
+        with catch_com_error():
+            cell = self.worksheet.Cells(int(row), int(column))
+
+            if number_format:
+                cell.NumberFormat = number_format
+            if value:
+                cell.Value = value
+            if formula:
+                cell.Formula = formula
 
     def read_from_cells(
         self,
@@ -239,18 +299,26 @@ class Application:
         :param column: target row, defaults to None
         :raises ValueError: if cell is not given
         """
-        worksheet = worksheet if worksheet else self.active_worksheet
-        if row is None and column is None:
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
+        if row is None or column is None:
             raise ValueError("No cell was given")
-        else:
-            row = int(row)
-            column = int(column)
-            cell_value = worksheet.Cells(row, column).Value
-            return cell_value
+
+        if worksheet:
+            self.set_active_worksheet(worksheet)
+
+        with catch_com_error():
+            cell = self.worksheet.Cells(int(row), int(column))
+            return cell.Value
 
     def save_excel(self) -> None:
         """Saves Excel file"""
-        self.workbook.Save()
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
+        with catch_com_error():
+            self.workbook.Save()
 
     def save_excel_as(self, filename: str, autofit: bool = False) -> None:
         """Save Excel with name if workbook is open
@@ -258,20 +326,30 @@ class Application:
         :param filename: where to save file
         :param autofit: autofit cell widths if True, defaults to False
         """
-        if self.workbook:
+        if not self.workbook:
+            # Doesn't raise error for backwards compatibility
+            self.logger.warning("No workbook open")
+            return
+
+        with catch_com_error():
             if autofit:
-                self.active_worksheet.Rows.AutoFit()
-                self.active_worksheet.Columns.AutoFit()
-            excel_filepath = str(Path(filename).resolve())
-            self.workbook.SaveAs(excel_filepath)
+                self.worksheet.Rows.AutoFit()
+                self.worksheet.Columns.AutoFit()
+
+            path = str(Path(filename).resolve())
+            self.workbook.SaveAs(path)
 
-    def run_macro(self, macro_name: str = None):
+    def run_macro(self, macro_name: str, *args: Any):
         """Run Excel macro with given name
 
         :param macro_name: macro to run
+        :param *args: arguments to pass to macro
         """
-        if self.app is None:
-            raise ValueError(
-                "Open Excel file with macros first, e.g. `Open Workbook <filename>`"
-            )
-        self.app.Application.Run(f"{self.workbook_name}!{macro_name}")
+        if not self.app:
+            raise ValueError("Excel application is not open")
+
+        if not self.workbook:
+            raise ValueError("No workbook open")
+
+        with catch_com_error():
+            self.app.Application.Run(f"{self.workbook.Name}!{macro_name}", *args)