Call Javascript from VBA on Excel on a Mac

Question

I want to create a VBA macro on excel which at a click of button would open a browser (chrome or safari) login to a website, extract the desired float value, then populate a given cell in the sheet with that value.

There are examples online on how to achieve this using internet explorer but this is not available as on a mac. I have also seen guides using Selenium but this doesn't appear to work on mac.

The javascript itself is along the lines of (after opening a browser at a certain website):

document.getElementById("username").value = "username"
document.getElementById("password").value = "password"
document.getElementsByClassName("button")[0].click()
value = parseFloat(document.getElementsByClassName("value")[1].innerText.slice(1))

So what I would recommend, is writing a program in a different language, and then executing it with VBA like so: https://stackoverflow.com/a/12320294/2446254 My preference would be node.js with puppeteer but you can use anything! (It might be very difficult for you to get up to speed on how this would all be done) — Cody Ｇ, Oct 01 '18 at 12:40
Consider perhaps python with xlwings. There is an add-in for mac 2016 or greater I believe. http://docs.xlwings.org/en/stable/addin.html — QHarr, Oct 01 '18 at 12:50
@QHarr you cannot return values in a python function on a mac with xlwings — Paddy Roddy, Oct 02 '18 at 21:18
PSA even if xlwings on mac doesn't support UDFs, it's still possible to run Python code that manipulates the spreadsheet. — villasv, Mar 02 '21 at 01:17

score 1 · Accepted Answer · answered Oct 04 '18 at 11:07

I've solved this by using a combination of python-selenium and xlwings. My VBA calls RunPython ("import python_script; python_script.fun()")

python_script.py

import xlwings as xw
from selenium import webdriver

def fun():
    # Creates a reference to the calling Excel file
    wb = xw.Book.caller()

    # opens chrome
    chrome_driver_path = '/usr/local/bin/chromedriver'
    driver = webdriver.Chrome(chrome_driver_path)

    # open website and login
    driver.get('url')
    driver.find_element_by_id('username').send_keys('username')
    driver.find_element_by_id('password').send_keys('password')
    driver.find_element_by_name('buttonId').click()

    # finds member price sum
    table_body = driver.find_element_by_xpath("//*[@class='classname']").text
    price = float(table_body.split()[2][1:])

    # closes chrome
    driver.quit()

    # changes cell value
    sheet = wb.sheets['sheetname']
    sheet.range('cell').value = price

Call Javascript from VBA on Excel on a Mac

1 Answers1