Batch Import Macro - Errors in "File Not Found" and "No sheet specified"

[eperego]

Hi,

I am having trouble getting a batch import macro to work, as I keep getting error messages. 

The first error message I get is, if the control parameter is FullPath, "no sheet specified, you must specify a sheet". But if you look at the macro, I specify the sheet is "TPG GL Download". 

Then if I move the control parameter to file name, I get the error "File not found". 

Please see the files below for my macro and the workflow I am trying to complete.

Thanks in advance for your help!

[BenMoss]

So if the sheet name is always 'TPG GL Download'

Then if you look in your action tool you are overwriting the entire filepath, which actually includes the sheet name, alteryx builds your file path and sheet name into a full string.

C:\Users\eperego003\Desktop\DA\Alteryx\Rollforward\New folder\Copy of AGV Recon - 01-18.xlsx|||`TPG GL Download$`

You should change this and only overwrite the filename...

C:\Users\eperego003\Desktop\DA\Alteryx\Rollforward\New folder\Copy of AGV Recon - 01-18.xlsx

Thus Alteryx will then automatically keep the sheetname suffix.

Ben

[patrick_digan · Accepted solution]

@eperego Ben beat me to it. If you wanted to try a different method, I love using the Reference shortcut method. You would 1) delete your action tool altogether, 2) Copy your filepath currently in the input tool into the Workflow-configuration workflow tab for your control parameter, and 3) change your input tool to reference the filename by typing in

%Question.ControlParam.Control Parameter (3)%

I like this method because you can now verify that everything is working very easily. The value you entered into step 2 serves as a test value. When you run your macro, it will substitute all that stuff between the percentiles with your test value. Once you have that working, then you macro would be all setup and tested. I've attached what should be a working copy. Here are pictures of 2 and 3 above.

[eperego]

Thank you both!! The first solution worked but I'll keep in mind the second solution as I am planning on running this batch import macro multiple times for different monthly files. 

Thanks again!

[eperego]

Hi Patrick, 

When I implemented Ben's solution, I didn't realize the program was pulling data from the first month 6 times - so I got 6 times of January data, but no data from Feb-June.

So I attempted to try your solution. However, I cannot edit any area of the Configuration section - I can only add a "user" line. I have version 11.7.4.37815. Is this a limitation from that version? My company hasn't allowed the 2018 program on our laptops yet. 

Thanks!

Emily 

[patrick_digan]

@eperego The good news is your version isn't limiting you.

Here is your original macro:

Because a control parameter is on the canvas, the workflow configuration panel has the corresponding Question constant on the 7th row. You can place test values in the third column called value. The value column is the only one you can change (and only for question and user constants). The test values are used when using the reference shortcut method that I talked about.