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
Solved! Go to Solution.
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],'~$')
@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.
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')
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.
@james9311 Can you share a screenshot of the configurations of the tool that is causing the error?
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:
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:
Active workbook example
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
Workflow
Importing all sheet names macro
Import 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
@jbichachi003 Thank you for taking the time to clearly write out detailed instructions. You are a Rockstar!
@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!
User | Count |
---|---|
106 | |
82 | |
70 | |
54 | |
40 |