How to Open, Save, Close an Excel file

[raghusrealm]

I am trying to connect an Excel file (this file is downloaded from a website using another workflow) to input tool but it is not reading the sheet name. Refer to image 1.

But, when I Open, Save and Close the Excel file and connect to input tool, I am able to get the sheet name. Refer to image 2. This is after the excel file was just opened, save (CTRL+S) and Closed.

I need help to figure a way to Open, Save and Close the excel file using a workflow may be using a Run command or something.

Any help in the form of a workflow would be highly appreciated.

Regards,
Raghu

[schuprov]

i am dealing with the same issue, also downloaded by UIPath, have you been able to find a workaround?

[bertal34]

@schuprov 

no workaround....I tried to figure out a way to programmatically open & save the workbooks but no luck.  I opened an Idea case below, please like it.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Input-Excel-File-with-prefix-quot-x-...

[schuprov]

@bertal34 i was able to find a way to get around this issue finally. I am using a python tool within Alteryx with openpyxl package installed. I was able to get it scheduled and ran on all the files that Alteryx need to work on. It overrides the saved by signature and lets the rest of my workflows run.

If you are still having an issue with this I can share my Python script and set up

[bertal34]

@schuprov - that is amazing to hear I spent so much time trying to find solutions for this problem.  I would love for you to share the script and help me set it up.  Is it straightforward or would it be best to meet and review?

[schuprov]

@bertal34 it is quite straightforward. I will share the details below, let me know if you have any questions.

For me first step was to set up the packages in the Alteryx folder with my IT, you will need admin access to the command prompt.

https://knowledge.alteryx.com/index/s/article/Install-Python-packages-via-command-prompt this link has how to do it. what you need to put it for last step is pip install openpyxl

once you have the package installed you can use it with your script at all times going forward.

next you want to open a new workflow and drop a Python tool onto the designer canvas and set it up.

if you are planning to schedule the workflow on the Alteryx Gallery you will need to include this in step 1 and I believe you will need to talk with your server admin about having the package being available on your company Alteryx Server.

As for the script here it is: 

import os
from openpyxl import load_workbook

# Define a list of network paths to the shared folders
shared_folder_paths = [
#insert your file path here, you can put as many as you want just make sure they are enclosed by ' ' and have a comma to separate the folders#
]

# 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}.")

you copy the script as below: 

after all of that is set up you should be able to just click run and have the python tool perform the operation for you.

It should be fairly straightforward to run this on your machine, however there might be some hickups with scheduling this on the server, especially if you are the first person to run any sort of Python Alteryx workflows. for that you will have to work with your server admin to get it set up.

Let me know if you have any other questions or issues with the script and i can take a look