Excel Online: replacement for VBA macros...

[James_Price]

Hi team

Like lots of others, I have a wide range of Excel macros that are not going to work in Excel Online.  On the web I've read I can use scripting, JavaScript and Python.  
Does anyone know if these are really alternatives and maybe give a recommendation with the reason why. 
Many thanks for this.
James

[#2 · Accepted solution]

@James_Price 

Let's explore the options you mentioned:

1. Scripting: In Excel Online, you can use the built-in scripting capabilities provided by Microsoft. Scripting allows you to automate repetitive tasks and perform custom operations on your Excel data. It provides a set of JavaScript-based APIs that you can use to manipulate the Excel workbook, worksheets, ranges, and more. With scripting, you can automate tasks, create custom functions, and build interactive user interfaces within the Excel Online environment.
2. JavaScript: JavaScript is a widely used programming language and can be used in combination with Excel Online to automate tasks and enhance functionality. You can use JavaScript to interact with the Excel Online APIs and perform operations such as reading and writing data, formatting cells, creating charts, and more. JavaScript offers flexibility and compatibility across different platforms, making it a good choice for extending Excel Online's capabilities.
3. Python: Although Python is not directly integrated into Excel Online, you can leverage it in conjunction with other tools to automate Excel-related tasks. For example, you can use libraries such as pandas and openpyxl to read and write Excel files, perform data analysis, and generate reports. Python offers a wide range of libraries and a large community, making it a popular choice for data manipulation and analysis tasks.
4. VBA: Although you can't create, run, or edit VBA (Visual Basic for Applications) macros in Excel for the web, you can open and edit a workbook that contains macros. Any existing macros will remain in the workbook, and you can open the workbook in the Excel desktop app to view and edit the macros.

Ultimately, the choice between scripting with JavaScript or using Python or VBA depends on the specific requirements of your macros and the level of integration needed with Excel Online.

Whatever the choice of torment is yours :).

[#3]

Nice answer Nicolino. Can I ask one more favour: If I use Scripting for example, how do I run the macro, as there is no developer tab?
Many thanks
James

[#4]

@James_Price 

Office Scripts in Excel

In Excel Online, you can run a script by adding a button to the worksheet, which triggers the execution of the script when clicked.

Here's how you can do it:

1. Open your Excel workbook in Excel Online.
2. Select the worksheet where you want to add the button.
3. Go to the "Home" tab in the Excel Online ribbon.
4. Click on the "Insert" dropdown menu in the ribbon.
5. Choose "Button" from the menu. This will insert a button shape onto your worksheet.
6. Resize and position the button as desired.
7. Right-click on the button and select "Assign Script" from the context menu.
8. In the dialog box that appears, you can choose an existing script or create a new one.
9. Select the script you want to assign to the button or create a new script.
10. Click "OK" to assign the script to the button.

Once you have assigned the script to the button, you can click on the button in Excel Online to run the script.

Note that scripting in Excel Online is based on JavaScript and uses the Office JavaScript API. You can write your custom functions and logic using JavaScript and interact with the Excel workbook, ranges, and other objects through the provided API.

It's worth mentioning that scripting in Excel Online has some limitations compared to VBA macros in the desktop version of Excel. Therefore, not all macros can be directly converted to scripts. However, you can achieve many automation tasks and customizations using scripting in Excel Online.

[#5]

@NikolinoDE You're a star.  Many thanks for this. 

James 

[#6]

I am glad that I could help you with your project.
I wish you continued success with Excel!

[#7]

How can I do web scraping in Vb selenium which versus for chrome

[#8]

@NikolinoDE I want to do web scrapping using vba macro which version of selenium use with chrome it’s not working properly with me can u please guide

[#9]

@kartikeya_sharma 

If you're looking to perform web scraping using VBA macro, you can indeed use Selenium with VBA to automate interactions with a web browser such as Chrome. Here's a general guide on how to set it up:

1. Download SeleniumBasic: First, you'll need to download and install SeleniumBasic, which is a set of bindings to use Selenium with VBA. You can find the latest release on GitHub: FlorentBreheret/SeleniumBasic.
2. Install ChromeDriver: ChromeDriver is a separate executable that WebDriver uses to control Chrome. Make sure you download the correct version of ChromeDriver that matches your Chrome browser version. You can download ChromeDriver from the official site: ChromeDriver - WebDriver for Chrome.
3. Reference Selenium Library: In the VBA editor of Excel, go to Tools > References, then browse to the SeleniumBasic.dll file that you downloaded and add it as a reference.
4. Write VBA Code: Now you can write your VBA code to automate Chrome using Selenium. Here's a simple example to open Chrome, navigate to a webpage, and extract some information:

Vba code is untested, please backup your file.

Sub WebScrapingWithSelenium()
    Dim bot As New WebDriver
    bot.Start "chrome" ' You can also use "firefox" or "edge"
    bot.Get "https://example.com"

    ' Find elements and interact with them
    Dim element As WebElement
    Set element = bot.FindElementById("some_element_id")
    Debug.Print element.Text

    ' Perform other actions as needed

    ' Quit the browser when done
    bot.Quit
End Sub

Make sure to replace "https://example.com" with the URL of the webpage you want to scrape, and "some_element_id" with the ID of the HTML element you want to interact with or extract data from.

5. Run the Macro: Run the WebScrapingWithSelenium macro from Excel, and it should automate Chrome to perform the actions specified in your VBA code.

By following these steps and customizing the VBA code to suit your specific scraping needs, you should be able to perform web scraping using Selenium with VBA in Excel.

NOTE: My knowledge of this topic is limited. The text and the steps are the result of various AI's. Maybe it will help you further in your project, if not please just ignore it.

[#10]

@NikolinoDE thanks a ton pal i will get back to you after performing this right now im out for a fortnight can i also hv your personal email id