Directory and Dynamic Input tool and text in specific cell

[tviz21]

Hi, I'm using the Directory and Dynamic Input tools to import .xlsx files from a Sharepoint directory and am having seveal issues come up.  

First, I keep getting a "schemas don't match" error and am narrowing it down to the fact that there are merged cells in the files. They match between files but still getting the error. Is there a workaround for this? 

Next, I need to pull the value from Column D (F4) Row 3 and create a new field with this value copied down to the end of the results set.  I have successfully done this using a Multi Row formula tool in a separate workflow then joining but am finding that it is appending the value from the first Excel file to ALL of the data and not changing as the file changes. I'm assuming I need to somehow incorporate this process in my first workflow. 

Attached is my workflow - as you can see I'm also creating a start row as the 4 headers I need are below some random text and the merged fields. I think this might be complicating my efforts to pull out that single value within those rows and create the new field.

Any thoughts/suggestions would be appreciated.

Thanks!

Toni

[pcatterson]

I can't open your workflow because I am using an older version of Alteryx, but my suggestion would be to use a dynamic input within a batch or iterative macro.  This will pull all your files regardless of schema and union them afterward based on name or location.  I've attached one I made for myself that unions them by name that I use a lot.  If you prefer unioned by position, change the output mode in the Interface Designer - Properties. Mine in addition to outputing the data (D) also outputs what sheets of excel succeeded (S) and failed (F).

[tviz21]

Hi,

 thanks for the solution, I have not worked with macros yet, trying to replace your input with mine and getting errors - is there any additional instructions you may have posted that explains how I could bring in my Excel files? One error I'm getting is "826498 does not match a sheet or named range in c:\users\thart..."  The 826498 is the text in column 1 row 16 so I'm not understanidng why it is trying to match it as a sheet name. 

Thanks! 

[pcatterson · Accepted solution]

You need to refer to the sheets within the excel file that you want to bring in.  Without you providing the sheet, it searches for information that isn't there.  The structure looks like normal FullPath, a pipe, and the sheet name.  It should look something like this: C:/Folder/Folder/Folder/File.xlsx|Sheet1

I have an improved directory macro that will add sheets for you, but you need both of these attached macros downloaded to make it work.  Click the 'Excel Sheets Separated?' box to get the available excel sheets.  It creates a new field called FullPathAndSheet.  Use filters to remove files & sheets you don't want.

[tviz21]

Got it working! Thank you so much for all of your help, had a challenge bringing in the 2 fields above the data columns if either did not have a value, it would drop the entire record. The original filter on COMP#/Recipient was !Value=NULL. By changing this to [Name]=F4 I was able to correct that.  Let me know if you have any questions about any of the workflow.