Open, close, and save Excel in Alteryx

[catmar]

Is there a way to simply open, close, and save an excel document with Alteryx? 

I am working on a two part workflow and I am facing challenging with existing Excel formulas. 

Part I performs data manipulations and drops new data in excel columns ABC. Excel has existing formulas in columns DEF referencing the data in columns ABC. 

Part II is a separate workflow which reads from the file I just created with data in ABC and formulas in DEF. The formulas won't update until the workbook has opened. Is there a way to bypass manually opening the excel document? 

In reality there a lot more formulas than DEF. I am trying to avoid adding the formulas in Alteryx and simply leverage what is on the existing Excel document. 

[cjaneczko]

You could do it with the Run Command tool by creating a bat file. Easier way would probably be to add the formulas to the alteryx workflow and just have Alteryx do the DEF formulas. 

@Echo off
start excel.exe "C:\path\to\your\file.xlsx"
timeout /t 5 /nobreak  > NUL
taskkill /F /IM excel.exe

[ddiesel]

Hi @catmar !

I second @cjaneczko suggestion to use run commands.

I also wanted to add that you can execute run commands using the events tab before or after your workflow runs, which is often how I apply them.

And a friendly word of caution: Running executable commands can be powerful, but it is important to be careful, especially if you are not very experienced with them. Don't ask me about how I learned this the hard way 😁

[danilang]

Hi @catmar 

Another point to be aware of is that batch commands that invoke Excel.exe don't necessarily work on the gallery.  This is because most server environments don't have the Office Suite installed.  There can also be issues involving deadlocks and unstable execution when running MS Office apps in unattended mode.

Dan 

[Qiu]

@catmar 
I happened to have an idea before for the weekly challenge but give up anyway.
It reads the input of Kanji from Data Stream 1 and output to one Excel and gets the Hiragawa of Japanese then read back.

Pretty much the same as your intention here. 

Take a look.

[catmar · Accepted solution]

Hi all, 

Thank you for the input. I must admit that I am a bit a bit intimidated by the execute run commands tool and created all formulas in the workflow instead. I'll revisit this topic at a later point. 

[schuprov]

@catmar i have recently started using a Python module with openpyxl package and I found that to be much more user-friendly and easier than running BAT files.

[catmar]

@schuprov Do you happen to have an example code where you open and close the file using openpyxl ? 

[schuprov]

@catmar 

import os
from openpyxl import load_workbook

# Define a list of network paths to the shared folders
shared_folder_paths = [
# your folder path here enclosed by ' ' and separated by comma if multiple#
]

# Iterate through each shared folder path
for shared_folder_path in shared_folder_paths:

# Initialize variables to keep track of the most recent file and its modification time
most_recent_file = None
most_recent_mod_time = 0

# Recursively search for Excel files in subdirectories
for root, dirs, files in os.walk(shared_folder_path):
for file in files:
if file.lower().endswith('.xlsx'):
file_path = os.path.join(root, file)
mod_time = os.path.getmtime(file_path)
if mod_time > most_recent_mod_time:
most_recent_mod_time = mod_time
most_recent_file = file_path

# Check if any Excel files were found
if most_recent_file:
print(f"The most recent Excel file in {shared_folder_path} is: {os.path.basename(most_recent_file)}")

# Load the workbook
workbook = load_workbook(most_recent_file)

# Perform operations on the workbook here

# Save and close the workbook
workbook.save(most_recent_file)
workbook.close()

print(f"{os.path.basename(most_recent_file)} has been saved and closed.")
else:
print(f"No Excel files found in {shared_folder_path}.")

[catmar]

Thank you @schuprov I will play with it.