Unable to open file for write error on output tool within Batch Macro

[DanielG]

Hi,

So I have a batch macro that is supposed to fill a bunch of named ranges inside an Excel template file.  Let's say the template is located on my LAN at folder X.  

When I run the macro, I get "unable to open file for write" but instead of displaying the correct folder path it is showing my macro folder location instead which is lets say Folder Y.  

I can not for the life of me figure out why it is trying to output there, as I am overwriting the ENTIRE FILE PATH with the file path I pass into it from the Macro Input.

Any thoughts on why that would occur?  It is driving me nuts.

Thanks

[DanielG]

This is the exact error message.  Makes no sense, as I dont know where it would ever reference that folder path.

[danilang]

Hi @DanielG 

Can you post the macro and a simple calling workflow?  If not some screen shots of the configuration of the tools. 

If not, you can add some debugging information into the macro to help you see what's going on.  Add a Message tool just before your output tool and give it a formula that includes the path that you're writing to.  This will insert a row into the results window when you run the workflow.  In the main workflow configuration on the Runtime tab, check show all macro messages

After you run the main workflow look for a row that was output by the Message tool.  This will show you actual path that was passed to Output tool.

Dan

[DanielG]

@danilang 

thanks for responding.  unfortunately i cant share the actual workflow/macro, as it is too specific to our company and I am not allowed.  To create a generic version would take me a long time to complete.  I can include some screenshots though.  Hopefully that would be enough to make some sense.  Also, I added the message tool as you suggested and it gives me the correct location not the generic "macro" location which shows in the error.

 

Here is the settings in the output configuration

 

Such a weird error.  It doesnt make any sense to me.

Thanks for any guidance you can provide.  🙂

[danilang]

Hi @DanielG 

Does the original error you posted occur when you run the macro directly or when you run the main workflow with the macro embedded? 

Dan

[DanielG]

@danilang -- it actually happens regardless if I am running the workflow that calls the macro or just opening and running the macro as a workflow.

[danilang]

@DanielG 

It's certainly doesn't look like an obvious problem.

Copy/paste all the tools from the macro into a new batch macro and try running that one.  If it doesn't work, deleted the Output tool from the new macro and replace it a new one.  This is to try and determine if there's a config problem in the macro file itself

Dan

[DanielG]

@danilang   -- OK.  I will give that a shot. 

Too add to the confusion, and I apologize I didnt catch this earlier, the results actually show in this order, the success write out to the message tool of the address, then it shows it successfully wrote the results to the first named range in the "file" result -- "100 records were written to "correct file location address" but then the next item is the above referenced error.

Not sure if that clarifies anything, or makes it more confusing...  🙂  but definitely needed to mention that finding.

I will let you know how I do with a complete copy/paste/deletion of the macro to see if there is a bad config stuck in there somewhere.

Thanks

[DanielG]

@danilang  - i copied/pasted to new workflow and deleted the old macro from its location and the main workflow.  then I saved the new workflow down as a macro and used it in the main macro.  Unfortunately I am still getting the same issue.  

I have opened a support ticket with Alteryx and will see if they know what is going on.  Once resolved I can get it posted out here in case anyone ever comes across this issue in the future.  

Thanks for your help in trying to figure this one out.

[DanielG]

So just to close the loop on this one.  I apparently didnt realize that earlier in the workflow not all of the rows were getting the proper path attached (some were getting "|||SheetName" attached to a null value.  So the tool was outputting the rows that had the correct address but then failing on the rest.  It was buried in the dataset, so I didnt realize it until I dove in deeper.  What I think was my main source of confusion was that I didnt think the Output tool would send anything when it found an error with the dataset it was trying to output.  But in this instance it just output a portion of the data.

What it boils down to is I am a bonehead. 

The moral of the story, boys and girls, is double-check everything when random errors appear.  The tools work when used PROPERLY.  

🙂 🙂 🙂 🙂 🙂

And thanks again @danilang 

Have a great weekend.