topic Re: Macro for output the data in excel in Alteryx Designer Desktop Discussions

Macro for output the data in excel

akumar2609 — Fri, 05 Jan 2024 13:20:40 GMT

Hi Everyone.

I need someone who can help me in creating a macro for my output in excel. I have the below data set where, I want to write the data based on the intercompany in different excel tab of my output file.

CCY	LE	N Account	S Account	C Center	Inter Company	Product	Trans Type	Ending Balance
USD	xxxx	xxxxxxxxxxx	0	0	1	0	0	-14.1
USD	xxxx	xxxxxxxxxxx	0	0	1	0	0	-30
JPY	xxxx	xxxxxxxxxxx	0	0	2500	0	0	40
JPY	xxxx	xxxxxxxxxxx	0	0	2500	0	0	24
USD	xxxx	xxxxxxxxxxx	0	0	2500	0	0	-3116.25
JPY	xxxx	xxxxxxxxxxx	0	0	2509	0	0	110050
AUD	xxxx	xxxxxxxxxxx	0	0	3080	0	0	-1195
JPY	xxxx	xxxxxxxxxxx	0	0	3500	0	0	1346
USD	xxxx	xxxxxxxxxxx	0	0	3500	0	0	-2616
JPY	xxxx	xxxxxxxxxxx	0	805CN9	3915	0	0	125
USD	xxxx	xxxxxxxxxxx	0	0	7501	0	0	1160
USD	xxxx	xxxxxxxxxxx	0	0	7501	0	0	41.2
USD	xxxx	xxxxxxxxxxx	0	90052D	7606	0	0	-998.81
USD	xxxx	xxxxxxxxxxx	0	0	7802	0	0	418.2
USD	xxxx	xxxxxxxxxxx	0	90052D	7890	0	0	-355.38

My output file is fixed with all the intercompany tab names in it. Below are my requirements:-

1) I want this to achieve by macro only

2) All the possible Intercompany names tab will be there in the output file. There are some formula also in each intercompany tabs, so we need to write the data in specified area only i.e. A1 to K20.

3) There are high possibility that we will not get all the Intercompany every time. Hence, I want alteryx to clear the previous data for those intercompany where we do not have the data in current period.

Re: Macro for output the data in excel

ed_hayter — Fri, 05 Jan 2024 14:50:54 GMT

So first i thought this could be done without a macro with an output tool configured to take table name from field. But i think the fact that you want to remove tabs without fresh data requires a macro.

So the flow has a block until done to overwrite the whole file with the master data sheet and therefore clear all the tabs from previous version of the file. Then we summarize the intercompany values with summarize these will group our batch macro and send each inter company in one at a time.

Within the macro we use the Create new sheet function for each company to get the separate tabs. We get the name of the tabs from the control parameter with the action set to update value and replace specifically Sheet1

Re: Macro for output the data in excel

akumar2609 — Fri, 05 Jan 2024 15:06:32 GMT

Thank you for the reply; however, I am getting the below error message.

Error: Append Sheets (2): Record #5: Tool #8: Unable to create backup of C:\Users\nbkjfze\Desktop\Alteryx Examples\Tabbed_Output workflow\Tabbed_report.xlsx to C:\Users\nbkjfze\Desktop\Alteryx Examples\Tabbed_Output workflow\Tabbed_report.xlsx.bak: Access is denied. (5)

Re: Macro for output the data in excel

ed_hayter — Fri, 05 Jan 2024 15:41:20 GMT

Interesting - this is usually caused by an output tool writing to a file that is already being written to and is therefore locked.

I wonder whether a block until done before the output tool in the macro will help with this?

I got that error with that config

A second block until done before the macro:

seemed to have it running with no errors

Re: Macro for output the data in excel

CoG — Fri, 05 Jan 2024 16:43:42 GMT

Still not sure why you would need a macro:

Re: Macro for output the data in excel

akumar2609 — Mon, 08 Jan 2024 14:59:29 GMT

Hi Andrew,

Thank you for replying on my query.

Not sure, I am getting the below error message :- Error: Output Data (6): Unable to create backup of C:\Users\Desktop\Alteryx Examples\Tabbed_Output workflow (1)\Tabbed_report.xlsx to C:\Users\Desktop\Alteryx Examples\Tabbed_Output workflow (1)\Tabbed_report.xlsx.bak: Access is denied. (5)

To avoid the above error message, my preference was to move on Macro, however doesn't succeed in that also.

Could you help me in getting it resolved?

Re: Macro for output the data in excel

CoG — Mon, 08 Jan 2024 16:42:57 GMT

Do you have AMP engine enabled? This can occasionally cause that error to be thrown. Try disabling in "Runtime" settings in Configuration pane for workflow. What Alteryx Version are you running?