Calling Excel Macro From Python - "Unable to get sort property of range class"

Strother 0
Viewed 33

I am running a script from python which works fine except i noticed that the macro was not sorting unless i called the sub from excel. When its called from python it just skips the sort line. I tried activating the worksheet or calling sort from the worksheet but nothing is working.

Again the script works and sorts when i open it from excel directly but when called from python it runs but the range doesnt have a sort property.

I have tried to force it using a specified range "B5:E74694" but when i do that it says that unable to get sort property of range class.

When called from outside application do the objects not have all their methods?

    Dim Array_Prices As Variant, i As Long
    Dim Range_Prices As Range, Row_Last As Long
    
    Row_Last = Sht_History.Cells(Sht_History.Rows.Count,2).End(xlUp).Row
    If Sht_History.AutoFilterMode = True Then 
    Sht_History.AutoFilterMode = False
    Sht_History.Activate
    Set Range_Prices = Sht_History.Range("B5:E" & Row_Last)
    
    Range_Prices.Sort key1:=Range_Prices.Cells(1, 1), order1:=xlDescending, key2:=Range_Prices.Cells(1, 2), order2:=xlAscending, Header:=xlYes

Here is the python code that that runs then the VBA code is triggered by a Workbook_BeforeClose event

    excelapp = win32.Dispatch('Excel.Application')
    excelapp.Visible = True
    excelapp.DisplayAlerts = False
    wb_strip = excelapp.Workbooks.Open(path_wb_strip, False, False
    wb_strip.Close(True)
    excelapp.Quit()
sorting vba python
1 Answers

As you say that vba works fine, when called from Excel, I will focus on the Python part.

A few comments on your Python script:

  1. It seems you are not running the macro from your python script (remember to add missing info and remove []):
    xl.Application.Run('[your_workbook_name].xlsm![Module_name].[our_macro_name]')

  2. Remember to import import win32com.client. If not installed: pip install pywin32 REF

  3. Delete instance of excel application at the end of your script using del method

  4. This might be self explanatory, but if you use path_wb_strip, remember to assign it a value at the beginning of your code.
    For example: path_wb_strip = 'C:\Users\workbook.xlsm'

Example

Consider following example from pythoninoffice.com. It worked for my testing script - just update names and paths.

import win32com.client #see comment 1
xl = win32com.client.Dispatch("Excel.Application") 
wb = xl.Workbooks.Open(r'C:\Users\workbook.xlsm') #your path_wb_strip
xl.Application.Run('workbook.xlsm!Module1.my_macro') #comment 2
wb.Save()
xl.Application.Quit()
del xl #comment 3
simpatak0
Related