How To Modify Excel File Use Python Without Influence The Excel VBA Script

Sometimes we need to use Python source code to write data to Excel files that contain VBA script code, and I also want it does not affect the normal execution of VBA code that exists in the Excel files.

At first, I used the functions in Python’s openpyxl module to write the data into the xlsm file. After writing the data, I found that the button for executing VBA code disappeared.

After some investigation, I finally found that the Python openpyxl module does not support VBA, and only the python xlwings module is friendly to support VBA script code. This article will show you an example of how to use the xlwings python module to read/write data from/to excel worksheet.

1. Python xlwings Module Example.

1.1 Install The Python xlwings Module.

  1. Check whether the python xlwings module has been installed or not.
    (MyPythonEnv) C:\Users\zhaosong>pip show xlwings
    WARNING: Package(s) not found: xlwings
  2. If the python xlwings module is not installed, then install it with the command pip install xlwings.
    (MyPythonEnv) C:\Users\zhaosong>pip install xlwings
    Collecting xlwings
      Downloading xlwings-0.25.0.tar.gz (806 kB)
         |████████████████████████████████| 806 kB 78 kB/s
    Requirement already satisfied: pywin32>=224 in c:\users\zhaosong\anaconda3\envs\mypythonenv\lib\site-packages (from xlwings) (228)
    Building wheels for collected packages: xlwings
      Building wheel for xlwings (setup.py) ... done
      Created wheel for xlwings: filename=xlwings-0.25.0-py3-none-any.whl size=818582 sha256=1b37a8c9354a36d762581e4dda63d6010ac1cd789969b0f223ca03e82192f749
      Stored in directory: c:\users\zhaosong\appdata\local\pip\cache\wheels\81\e4\3d\4e33a67c8dc1b10426af95ab2638cdd428e54eea1add2c1e2d
    Successfully built xlwings
    Installing collected packages: xlwings
    Successfully installed xlwings-0.25.0
  3. Run the command pip show xlwings again to verify that the python xlwings module has been installed successfully.
    (MyPythonEnv) C:\Users\zhaosong>pip show xlwings
    Name: xlwings
    Version: 0.25.0
    Summary: Make Excel fly: Interact with Excel from Python and vice versa.
    Home-page: https://www.xlwings.org
    Author: Zoomer Analytics LLC
    Author-email: [email protected]s.com
    License: BSD 3-clause
    Location: c:\users\zhaosong\anaconda3\envs\mypythonenv\lib\site-packages
    Requires: pywin32
    Required-by:

1.2 Read Data From Excel.

  1. By default, cells with numbers are read as the float type, cells with dates are read as datetime.datetime type, Convert empty cells to None.
  2. Data can be read in the format specified by the options() method.
  3. Below is an example.
    # import the xlwings python module.
    import xlwings as xw
    import os
    
    # Create the App application
    
    # visible indicates whether excel is visible when the program is running, true indicates visible, false indicates invisible.
    # add_book indicates whether you want to create a new workbook.
    app=xw.App(visible=True,add_book=False)
    
    # define the excel file name.
    file = "excel_file_1.xlsm"
    
    # open the above specified file.
    wb=app.books.open(file)                    
    
    # get the reference to the first worksheet.
    ws = wb.sheets["Sheet1"]  
                     
    # activate the worksheet.
    ws.activate()
    
    # The value of B2 is read by default, which is a floating-point type
    b2_value = ws["B2"].value
    print(type(b2_value))
    print(b2_value)
    
    # Read the value of B3. The null value here should display None by default.
    b3_value = ws["B3"].value
    print(type(b3_value))
    print(b3_value)
    
    # Set the value of B2 to an integer.
    b2_value_int = ws["B2"].options(numbers=int).value
    print(type(b2_value_int))
    print(b2_value_int)
  4. Another way to get excel cell values.
    import xlwings as xw
    import os
    
    # Create the App object.
    app=xw.App(visible=True,add_book=False)
    
    # Define the excel file name.                
    file = "excel_file_1.xlsm"
    
    # Open the above excel file.
    wb=app.books.open(file)
    
    # Get the first Excel worksheet.
    ws = wb.sheets["Sheet1"]
    
    # Get the B2 cell value by using the worksheet range() method.
    b2_value = ws.range('B2').value
    
    # Print out the B2 cell value.
    print(b2_value)

1.3 Write Data To Excel.

  1. Below is the code example that will write data to the excel file.
    import xlwings as xw
    import os
    
    # Create the xlwings App object.
    app=xw.App(visible=True,add_book=False)
    
    # Define the target excel file name.                
    file = "excel_file_1.xlsm"
    
    # Open the above target excel file.
    wb=app.books.open(file)
    
    # Reference the first worksheet.
    ws = wb.sheets["Sheet1"]
    
    # Define 3 integer value variables. 
    # This value will be placed in column B.
    b = 6799
    # This value will be placed in column C.
    c = 2345
    # This value will be placed in column D.
    d = 1000
    
    # Get the excel worksheet used range.
    info = ws.used_range
    
    #print(info)
    
    # Gets the maximum used row number in the worksheet table
    nrows = info.last_cell.row
    # Print the row number.
    print(nrows)
    
    # If the maximum used excel worksheet row's value is None. 
    if ws['B'+str(nrows)]==None:
        # Assign the values to the current row related Excel worksheet column.
        ws['B'+str(int(nrows)-1)].value=b
        ws['C'+str(int(nrows)-1)].value=c
        ws['D'+str(int(nrows)-1)].value=d
    # If the maximum used excel worksheet row's value is not None. 
    else:
        # Assign the values to the next row related Excel worksheet column.
        ws['B'+str(int(nrows)+1)].value=b
        ws['C'+str(int(nrows)+1)].value=c
        ws['D'+str(int(nrows)+1)].value=d
      
    # Save the above data to the Excel file.  
    wb.save()
    
    # Close the workbook.
    wb.close()
    
    # Exit the Excel file.
    app.quit() 
    

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.