I am doing an assignment where I search in an API, with given values from my Excel file(Elastic Search). Then I have to extract multiple values from the API and paste these values into Excel. I have no trouble accessing the API.
I use a simple for loop to iterate through my values from the Excel file. The problem occurs when I try to write the values from the API into my Excel file with Write To Cells, using the RPA.Excel.Application library. The error I get is: AttributeError: ‘NoneType’ object has no attribute ‘Cells’.
*** Settings ***
Library RPA.Excel.Files
Library RPA.Tables
Library RPA.Excel.Application
Library RequestsLibrary
Variables variables.py
*** Variables ***
${EXCEL} Assignment.xlsx
*** Keywords ***
Get numbers from Excel
${auth}= Create List ${USERNAME} ${PASSWORD}
Create Session url ${URL} auth=${auth}
Get values and write to Excel
RPA.Excel.Files.Open Workbook ${EXCEL}
${table}= Read Worksheet As Table
... header=True
@{number_list}= Get Table Column
... ${table}
... Header
... as_List=TRUE
FOR ${number} IN @{number_list}
${resp}= Get Request url ${URL}${number}
Write To Cells row=${number} column=4 value=${resp.json() ${city}}
END
Close Workbook
*** Tasks ***
Robot
Get numbers from Excel
Get values and write to Excel
Sorry that my reply took this long … your task should work like this.
Modifications:
Remove RPA.Excel.Application library
Change Write To Cells keyword to Set Worksheet Value
*** Settings ***
Library RPA.Excel.Files
Library RPA.Tables
Library RequestsLibrary
Variables variables.py
*** Variables ***
${EXCEL} Assignment.xlsx
*** Keywords ***
Get numbers from Excel
${auth}= Create List ${USERNAME} ${PASSWORD}
Create Session url ${URL} auth=${auth}
Get values and write to Excel
Open Workbook ${EXCEL}
${table}= Read Worksheet As Table
... header=True
@{number_list}= Get Table Column
... ${table}
... Header
... as_List=TRUE
FOR ${number} IN @{number_list}
${resp}= Get Request url ${URL}${number}
Set Worksheet Value row=${number} column=4 value=${resp.json() ${city}}
END
Close Workbook
*** Tasks ***
Robot
Get numbers from Excel
Get values and write to Excel