Input the same sheet from multiple excel files

I've seen a lot of information around this, but haven't been able to find the solution that I am looking for. The closest post I could find to this is at the link below, but I need help in setting up the macros. All files are .xlsx and are in the same folder - the sheet I need to bring in is called Data on all of the files. However, since it is a shared folder, I will need to filter out the actively open versions (i.e. contains ~$ in front of the file name). I appreciate any guidance you can provide.

Solved: How to: Import one specific sheet from multiple fi... - Alteryx Community

Input

Macros

Best Practices

Batch Macro

Optimization

Accepted answers

james9311

Actually, I think I finally solved it! I removed the second Action and the Formula tool from the 'Import Data from Selected Sheets' macro. I had gotten that from the post that I referenced in OP, but couldn't figure out what it was for. I removed it, and voila! I still included the workflow and macros in case someone else needs to reference, or if anyone sees possible changes to increase optimization. I plan on adding a data cleansing tool to the 'Import Data from Selected Sheets' macro to remove leading and trailing spaces.

Workflow

WorkflowImporting all sheet names macroImport data from selected sheets macro

Below is the 2nd macro from the post that I referenced in OP. I removed the Formula Tool and extra Action tool to create my 'Import Data from Selected Sheets' macro.2nd macro from referenced community post

All comments

gabrielvilella

Hey @james9311, based on the article you provided, the only step that you need to add is a filter to get rid of those files that have ~$ before the macro. Here is one example of expression to insert on the filter:

!Contains([FileName],'~$')

james9311

@gabrielvilella thank you for the prompt reply! I got that part figured out. I need help setting up the macros shown in the post I referenced. It shows an error for the first macro (Importing all sheet names) in the WF "Can't find the file: *full file path*\<list of sheet names>". for all four files.

Then for the second macro (import data from selected sheets), I'm not sure how they set up the update value actions, input data, and formula.

Luke_C

Hi @james9311

You typically need to update the fullpath before feeding into a macro or dynamic input. A formula like the below would work:

REPLACE([FullPath],'<List of Sheet Names>','Data')

james9311

I did this, but now when it gets to the second macro to input data from selected sheets it says "Parse Error at char(0): Unknown variable "Z" (Expression #1)". The files are located in a shared drive (Z), so that is the first character in the full path v_wstring that feeds into the control parameter.

Luke_C

@james9311 Can you share a screenshot of the configurations of the tool that is causing the error?

jbichachi003

Hi @james9311,

If all the files are .xlsx and they are all in the same folder, hopefully you are using the Directory tool to load the files into Alteryx. If you are, you will be able to utilize the "FullPath" field to feed into your macro to open up each file.

If a file is actively open, the FullPath field (as well as the FileName field), will contain the "~$" symbols to indicate that it is open. To not capture these downstream, you could do the following:

Remove the "~$" characters (ReplaceChar([FullPath], '~$', '') to make active files appear non-active
Attach a Unique tool to capture the duplicates (anything that appears in the Duplicates output would indicate that the file is currently open).
Join the list of Duplicates to the output of Step 1 (keep the output of step 1 as the Left input, and the list of duplicates as the Right input).
Anything that is coming out of the Left output (not the Join or Right output), is a list of non-active files.

The Directory tool also has a column titled "AttributeHidden" which indicates if a file is hidden (True if it is, False if it isn't). Whenever you open an Excel file, file with "~$" opens in the background and is hidden. You may not, however, want to rely on this field, since all hidden files (including the .bak files created by Alteryx in recent version of the software) will also have a True value for "AttributeHidden".

See the screenshot below:

The Directory tools pulls in all the Excel files (active and non-active) from a specified folder.
1. In this particular case, I have 2 Excel files, Book1.xlsx and Book2.xlsx.
2. Book2.xlsx is open, which means it is generating a temporary file with a prefix of "~$".
3. This is why you can see there are three records leaving the Directory tool.
The Multi-Field Formula can be ignored. It's purpose is only to redact my username so I could share this photo on Alteryx.
The Formula tool updates the FullPath field and replaces the "~$" from the FullPath with nothing ('').
1. This means the FullPath for the active file should match the FullPath for the non-active file.
The Unique tool is configured to only have the [FullPath] selected.
1. The Duplicate output will contain one record containing the file path for Book2.xlsx (the active file).
The Join is configured to join based on [FullPath].
1. We don't need to right input fields, so I've deselected them.
2. All records coming out of the Left output will be the list of non-active files (Book1.xlsx, in my example). This is what you'll use to feed into your Macro.

Active workbook example

james9311

Workflow

WorkflowImporting all sheet names macroImport data from selected sheets macro

james9311

@jbichachi003 Thank you for taking the time to clearly write out detailed instructions. You are a Rockstar!

jbichachi003

@james9311, happy to help!

Just keep in mind that the solution you posted gets rid of those temporary files, but it will still run for the active files. If you want to completely remove them, from the downsteam process (both the temporary file and it's matching permanent file), you can do the set up I had, and then run them through your macro.

Glad you got it working!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703