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

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!

Batch Import Macro.yxmc

Rollforward.yxzp

Input

Batch Macro

Accepted answers

patrick_digan

@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.

Batch Import Macro.yxmc

All comments

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

%Question.ControlParam.Control Parameter (3)%

Batch Import Macro.yxmc

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703