Good morning gentlemen, and in advance: thanks for your valuable time !
It’s the second time I’m posting on “dev. support forum”, but since I have received all the help I needed (and beyond) on my last request, I’m confident to say I may find a solution to my new struggle :-).
Please allow me to set the scene to make sure I’m not missing any important information:
I’m currently doing some development using Automation Anywhere (RPA): basically creating bot that execute repetitive tasks,
In parallel, I’m trying to set-up a dashboard that follow-up on every bot that is running (e.g.: The RPA bot that is updating Sharepoint Whatever ran 23 times; the RPA bot that is sending Email to XXX ran 11 times; etc).
I hence tried to implement a VB script directly into my RPA code that will increment by one (+1) a cell value every time a given bot is running.
The given bot structure is hence as follow: 1) “Bot main action” (see above: Update Sharepoint, send email, etc) > 2) “Increment specific cell in the dashboard” (not the same depending on which bot is running.
Here is my struggle:
I cannot do the incrementation using RPA instead of some “quick code”, because at some point, when 100+ bots will be running, there will be opening conflict to update the Dashboard (cannot open the excel at the same time).
The embodied VB Script sound like a solution if it’s running quickly in the background (plot twist: it doesn’t)…
Same goes with the Python code but I have very little knowledge here.
The VB below code, is “working” when I’m running it locally on a local Excel session (the “ScreenUpdating = false” is not working thought, the action is not happening in the background…); but it’s not working at all, when I run it in my RPA code.
Sub IncrementDashboard()
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open("\\path\Book1.xlsx")
Set ws = wb.Sheets("Sheet1")
ws.Range("B3").Value = ws.Range("B3").Value + 1
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
I have hence considered using Python to do it too (since it’s the other coding tool that is available to me), but I’m not even a beginner there: Would anyone have a code sample that would do the job and that I could re-use for each bots?
You could have each bot do something that’s faster and causes fewer conflicts than updating the spreadsheet, such as updating a count that’s in a text file, or appending to a text file, retrying if necessary until successful, and have a program periodically read the file and update the spreadsheet.
Alternatively, you could have a program open a socket to which the bots could write to tell the program to increment the count.
Hi Matthew, thanks for the feedback and sorry about the delayed answer !
The first plan you mention is indeed an option I had in mind but I was looking at something more dynamic / live: X update a day instead of 1 a day.
And I find it too prone to error: What is someone delete one of the .txt file?
I’m all listening to that idea to “program a socket”: Any hints/draft I could start with? Any counterpart you can think of?
Start the server script and then start a few instances of the client script. Each client will tell the server to increment the count by 100 at a rate of 1 per second and the server will display the updated count every 5 seconds.
# Server script
import queue
import socket
import sys
import threading
import time
# A port on the local host.
HOST = ''
PORT = 50007
def listener(inc_queue):
'''Listens for new client_workers and creates a worker for each client_worker.'''
with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as sock:
sock.bind((HOST, PORT))
while True:
sock.listen(1)
conn, addr = sock.accept()
threading.Thread(target=client_worker, args=(conn, inc_queue), daemon=True).start()
def client_worker(conn, inc_queue):
'''Accepts data from a client and puts it into a queue.'''
with conn:
while True:
data = conn.recv(1024)
if not data:
break
inc_queue.put(data)
# A queue for the data coming from the clients.
inc_queue = queue.Queue()
count = 0
print('Current count is', count)
# Update the count every 5 seconds.
UPDATE_PERIOD = 5
# Start listening for clients.
threading.Thread(target=listener, args=(inc_queue,), daemon=True).start()
print('Listening')
while True:
increment = 0
# We'll collect increments for a period before updating time main count in
# case they are frequent.
start_time = time.time()
while time.time() - start_time < UPDATE_PERIOD:
try:
data = inc_queue.get(block=True, timeout=1)
except queue.Empty:
break
if not data:
break
# We're going to assume that each client will be sending a byte each
# time it wants the counter incremented, so n bytes means "increment by
# n".
increment += len(data)
if increment > 0:
print('Increment by', increment)
count += increment
print('Current count is', count)
sys.stdout.flush()
# Client script.
import socket
import time
# The port on the local host.
HOST = '127.0.0.1'
PORT = 50007
with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as sock:
sock.connect((HOST, PORT))
# Ask the server to increment the counter a period of 100 seconds at a rate
# of 1 per second.
for i in range(100):
print('Sending increment number', i + 1)
sock.send(b'\x00')
time.sleep(1)
