How to Replace VBA with Python(Step-By-Step Tutorial)



How to Automate Excel with Python

In this video, I’m going to show you how to get started using Python with Excel.

We will cover how to:
* Install Python and all the relevant libraries
* Install the IDE(code editor)
* Run a script(3 different ways and it’s important to understand each)
* Read and write Excel workbooks using Python
* How to trigger Python scripts from Excel events

#ExcelPython #xlWings

SUBSCRIBE TO THE CHANNEL:

DOWNLOAD THE SOURCE CODE FOR THIS VIDEO:

Related Training
The Excel VBA Handbook Course(
Webinar Archives – 60+ Hours of VBA training(

Free Excel VBA Resources
Excel VBA Articles (

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 – Introduction
03:23 – Running Python code
08:05 – How to use OpenPyxl to read files
14:00 – How to use Pandas for data analysis
19:58 – How to use xlWings
24:43 – Installing the xlWings Addin
27:06 – Call Python from VBA
29:00 – Call VBA from Python
30:18 – Summary

source

45 thoughts on “How to Replace VBA with Python(Step-By-Step Tutorial)”
  1. When I push the button to run the "PythonCalledFromVBA" python script (which happens @28:25 in the video) I get this error window:

    —————————

    Error

    —————————

    Traceback (most recent call last):

    File "<string>", line 1, in <module>

    AttributeError: module 'xlwings.utils' has no attribute 'prepare_sys_path'

    Press Ctrl+C to copy this message to the clipboard.

    —————————

    OK

    —————————

    Not sure what I am missing…

  2. @ 27:48 I can see you Intellisense working, but it doesn't work for me :/
    (xlwings is ticked in the References)
    – Figured it out, used Ctrl+Space
    In the video you Imported Xlwings @ 20:04, but for me it doesn't work. I get the error "Import "xlwings" could not be resolved"

  3. I have been using VBA for our production reporting for years, usually extracting data from other spreadsheets or ascii files but in recent years contractors have started to use pdf files for their outputs which is less than convenient, however I have been able to use Python to create a utility to extract the statistics we need and output to an excel spreadsheet. I am now working on integrating my Python more closely with Excel so that the data can be transferred in and out of xlsx files.

    I have been trying to run through the demonstrated scripts and with mnor tweeks for path/filenames I have run through your demonstration scripts with no problems except… trying to call VBA from Python, when I try to run the 'RunVBACode.py' script I get the following error:

    Quote [
    Traceback (most recent call last):
    File "c:UsersJonDesktopPython-Excel-Macro-MasteryRunVBACode.py", line 6, in <module> wk = xw.books.open(r'VBACode.xlsm')
    File "C:UsersJonAppDataLocalProgramsPythonPython310libsite-packagesxlwingsmain.py", line 4836, in open
    impl = self.impl(name)
    File "C:UsersJonAppDataLocalProgramsPythonPython310libsite-packagesxlwingsmain.py", line 4961, in impl
    return apps.active.books.impl
    AttributeError: 'NoneType' object has no attribute 'books'
    ] Unquote

    The only change I made to the script was the filename, I am not sure if this is a problem with my set up as I'm no expert on Python but it looks as though the problem is in the xlwings code. Would be grateful for any assistance.

  4. Great video, Thank you. Please I tried to run my xlsm file (with goalseek function) using python and import xlwings, but I have a run time error '1004' reference isn't valid, if I run the xlsm directly without python it works. Please what is the error?

  5. How do i string or json from vba to Python for parsing and after parsing python will write the Parsed value to excel sheet. I believe parsing json is easy on python than vba.

  6. Just for example:

    """

    vba:

    Set new_w = Workbooks.open('workbook_path')

    Set new_s = new_w.Worksheets('sheet_name')

    new_s.Range('A1').Value = 'test'

    python pywin32 library:

    new_w = Workbooks.open('workbook_path')

    new_s = new_w.Worksheets('sheet_name')

    new_s.Range('A1').Value = 'test'

    """

  7. Hi, I'm finding that I get an error when trying to run this script in Visual Studio Code (Latest download) using Windows 10 64 Bit – import openpyxl

    book = openpyxl.load_workbook(r'C:\Users\User\Desktop\Python\Replace VBA with Python\Resources\New Zips\marks.xlsx')

    sheet = book["ClassA"]

    print(sheet["Ä10"].value)
    The error is – you don't have an extension for debugging plain text.
    I've tried downloading the software suggested & what I thing I need but I'm still getting the same error. Any suggestions?

  8. Hello Friends ,

    I am getting this error while trying to run after setting the dataframe …

    File "C:UsersjkrishnaAnaconda3libsite-packagespandas__init__.py", line 16, in <module>

    raise ImportError(

    ImportError: Unable to import required dependencies:

    numpy:

    IMPORTANT: PLEASE READ THIS FOR ADVICE ON HOW TO SOLVE THIS ISSUE!

    Importing the numpy C-extensions failed. This error can happen for

    many reasons, often due to issues with your setup or how NumPy was

    installed.

  9. I have a question. I tried runnign the line import openpyxl, and an error popped up that says "Import "openpyxl" could not be resolved from sourcePylancereportMissingModuleSource"

    What can I do to solve this issue?

    EDIT: I could move one from this issue, but now i have another one. The problem comes when trying to run the xlwings code.

    here is my code:

    import xlwings as xw

    wk = xw.books.open(r'C:UsersJuan Pablo HornDropboxExcel Macro MasteryHow to Replace VBA with Python(Step-By-Step Tutorial)marks.xlsx')

    sheet = wk.sheets("Sheet1")

    rg = sheet.range("A1:C2")

    print(rg.value)

    The error says as follows:AttributeError: 'NoneType' object has no attribute 'books'.

    EDIT 2: I have solved the prior issue as well. Now the issue is when i try to run the VBA Macro to Run Python From VBA. This is what the error says:

    —————————
    Error
    —————————
    Traceback (most recent call last):

    File "<string>", line 1, in <module>

    File "C:UsersJuan Pablo HornDropboxExcel Macro MasteryHow to Replace VBA with Python(Step-By-Step Tutorial)PythonCalledFromVBA.py", line 5, in main

    wb=xw.Book.caller()

    File "C:UsersJuan Pablo HornAppDataLocalProgramsPythonPython39libsite-packagesxlwingsmain.py", line 763, in caller

    raise Exception('Book.caller() must not be called directly. Call through Excel or set a mock caller '

    Exception: Book.caller() must not be called directly. Call through Excel or set a mock caller first with Book.set_mock_caller().

    Press Ctrl+C to copy this message to the clipboard.
    —————————
    OK
    —————————

Leave a Reply

Your email address will not be published.

Captcha loading...