topic Re: Formatting for Batch Macro Excel Output Sheets in Alteryx Designer Desktop Discussions

Formatting for Batch Macro Excel Output Sheets

macst245 — Fri, 01 Apr 2022 13:07:30 GMT

Hello, I have a batch macro which outputs each new table to a new excel sheet. I am looking to format each of the sheets with the below formatting, is there a way to include the reporting tools in a batch macro so all the different excel sheets are formatted? I have attached the Alteryx Package for this, and please let me know if more information is needed. Below is the formatting I would need done:

Can the report be formatted so we don’t have to spend time opening columns in excel? Can we make sure the columns are spaced out so the headers are all showing?

Can we have the header rows highlighted?

Can we have first column highlighted?

Can we have the Metrics and Thresholds as percentages?

Thanks in advance!

Re: Formatting for Batch Macro Excel Output Sheets

messi007 — Fri, 01 Apr 2022 13:51:32 GMT

@macst245,

Yes you can add table formating tool inside of the batch macro. And with a table tool you can the formating that you're looking for :

However in order to generate the Excel files you have to use a render tool.

Hope this helps !

Regards,

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Fri, 01 Apr 2022 14:00:40 GMT

Thanks, I did try that, however when I added the table tool and the render tool, I kept getting an error. Are you able to edit the workflow with the formatting table added in?

Thanks again!

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Mon, 04 Apr 2022 13:10:05 GMT

@messi007 Are you able to help with the above question? I tried using the steps above but could not get the file to output.

Re: Formatting for Batch Macro Excel Output Sheets

JagdeeshN — Mon, 04 Apr 2022 13:14:58 GMT

@macst245 can you please elaborate more on what is the error you are getting.

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Mon, 04 Apr 2022 13:19:06 GMT

@JagdeeshN It's not an error that I'm getting, but I am just not able to output a file. Are you able to download the package and see if you are able to do the above that @messi007 suggested? Thanks in advance if you are able to try!

Re: Formatting for Batch Macro Excel Output Sheets

JagdeeshN — Mon, 04 Apr 2022 13:27:33 GMT

@macst245 Understood.

I think you are using the normal output tool within your batch macro (In the attached package)

Please refer to this post below. @Christine_Assaad and @pedrodrfaria have explained in detail about the possible solutions.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formatting-Excel-Output-File/td-p/686734

Hope this helps!

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Mon, 04 Apr 2022 13:53:08 GMT

@JagdeeshN I have read through those posts, but they are using a single input file. Should I replace my regular output file within my macro with a render tool?

Re: Formatting for Batch Macro Excel Output Sheets

JagdeeshN — Mon, 04 Apr 2022 14:05:15 GMT

@macst245 You can actually structure the batch macro to accept/process one file at a time.

And as described in the post i shared earlier you would need to use a combination of the Table tool and the Render tool to get the formatted output.

Hope this helps!.

Best,

Jagdeesh

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Mon, 04 Apr 2022 14:30:37 GMT

@JagdeeshN Thanks for the push on this, I tried putting the table and render tool inside the batch macro, but am now getting the below error when running my overall workflow.

Re: Formatting for Batch Macro Excel Output Sheets

JagdeeshN — Mon, 04 Apr 2022 15:28:11 GMT

@macst245 Interesting!

The below post has different way in which this can be resolved depending on the cause.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/The-Designer-x64-reported-InboundNamed...

Specifically check out @Jackie7890 's answer:-

'I had this same error (The Designer x64 reported: InboundNamedPipe GetOverlappedResult: The pipe has been ended) and here is how I was able to fix it.

The problem started when I changed my output file name in a Render Tool using the text box directly, instead of browsing to the folder and typing in the name. In Alteryx, you have two options for entering your output filename, either by entering the full file path as a string, or by navigating to the folder and typing just the name of the output file, selecting the datatype, and clicking Save. in this case I was outputting a Report to an .xlxs, and had changed the output string directly to the same name as an existing file, desiring the Report Output to overwrite the existing file.

I resolved the problem by navigating to the desired folder using the Disc/Save button, then typing in the box, and the error disappeared. Even overwriting an existing report, I had no error. '

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Mon, 04 Apr 2022 15:54:51 GMT

@JagdeeshN I really appreciate you pointing me to that thread, however I had tried the solution you had posted and others in that thread and am still getting the same error.

Re: Formatting for Batch Macro Excel Output Sheets

macst245 — Tue, 05 Apr 2022 20:20:13 GMT

@JagdeeshN I figured this out and thanks again for your help. I had to add a layout tool in my workflow as well as the render tool (both of those were outside the macro).

Thanks again