I need to run a VBA file located on a network drive in Alteryx server. The output from the file. There are formula based on the data, and we want the formula to be refreshed in excel as well. Openpyxl does not does that.
Currently, we have to manually open the file and then save it, so that excel formulas are refreshed. We want to automate it
I have tried
1. Python Tool - I have used Xlwings and win32com.client. Both of them works on Alteryx designer, but they do not work on Server.
a. win32com.client
import win32com.client
def execute_vba_macro(file_path, macro_name):
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True
workbook = excel.Workbooks.Open(file_path)
excel.Application.Run(macro_name)
workbook.Close(SaveChanges=True)
excel.Quit()
b. xlwings
import xlwings as xw
def execute_vba_macro():
workbook = xw.Book('your_excel_file.xlsm') # Make sure the file path is correct
workbook.macro('YourMacroName')() # Replace 'YourMacroName' with the actual macro name
workbook.save()
workbook.close()
execute_vba_macro()
2. RunCommand and .bat file - It is working on Designer but again fails on server and does not execute the CMD and keeps the workflow running for indefinite time.
@echo off
set filePath="C:\Path\To\YourWorkbook.xlsm"
set macroName="MyMacro"
REM Start Excel and run macro
start excel.exe /e %filePath% /macro %macroName%
Is there any way we can make the formula refresh in Excel files as well after Output?
