Updating a specific cell without opening the workbook

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?

Again, in advance, many thanks for any help !

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?

Thanks for the help !

Here are a couple of example scripts.

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)

Hi Matthew,

As much as I would like to say Thanks for the code sample, this looks like Chinese to me :slightly_smiling_face:

I was hoping to have a code structure/format pretty much similar to what we can have in VBA:

  1. Open excel,
  2. Fetch specific cell,
  3. Increment cell.

On a side note, I just found out that Javascript is also an option.

Hi papou,

Try this:

import win32com.client
import os

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = 1

wb = excel.Workbooks.Open(os.path.abspath("sample.xlsx"))
ws = wb.Worksheets('Sheet1')
ws.Range("B3").Value += 1
wb.Close(SaveChanges=True)
excel.Quit()

You need to do pip install pywin32.