I'm needing help with the Blob!

Question

Hi,

I have a very large workflow that cleans and sorts a lot of data with the desired output for it to go into a template excel file that has formatted tables in it. There are no formulas or anything to make it more complicated, it's just supposed to paste the data into the table and extend the table to the full extent of the data.

There are multiple different tabs in this spreadsheet that will never change field names or tab names so that's not an issue.

I have tried to use the output tool to output the data into the specific fields while maintaining formatting and not bringing through the field names (as they already exist in the template), but it only outputs the first row of data into the table, and the rest go underneath the table.

I think I'm understanding this correctly that the blob input and output can bring in the template and keep any formatting while also outputting the data into the table, but I can't for the life of me figure out how to get it to work. It just replaces the entire sheet and doesn't keep any formatting.

I have attached a dummy workflow as an example of how I've set it up. I think I might be going wrong with the Blob Output tool but I could be wrong in other places.

I've also attached the template file for the dummy workflow.

The desired output I'm expecting is to look like the below:

The Template is as follows:

Is what I'm attempting to do possible at all?

Any help will be greatly appreciated.

Thanks

Blob test.yxmd

Table Template.xlsx

flying008 · Answer

Hi, @Andrew_T-H

I encountered the situation you mentioned a few years ago, but so far, it is almost unrealistic to rely solely on Alteryx's internal tools to dynamically adapt the Table area.

If you don't mind your output template file being in .xlsm format, I have two suggestions for your reference:

1- Create a custom VBA macro command in Excel specifically for the situation you mentioned. Its function is to expand the Table area to the entire range of your output data area with one click.
I am currently doing this, and the effect is very good. The only drawback is that you need to manually click the icon of this command after opening the worksheet to run it.

2- After you output the data to the template file, you also need to use the Event or Run Command tool in the workflow to run the PowerShell script, which will automatically expand the Table to the data area of ​​your output file. Then when you open the .xls* file in Excel, you will find that everything is as you wish!

The above two solutions require you to have certain VBA knowledge or PowerShell code skills, but they can achieve the results you want, so you can choose one of them that suits you.

OTrieger · Answer

There is no a magic solution here.

One way will be to set multiple templates with different amount and formatted tables, with 10, 50, 100 etc., so the automation will select the most appropriate template based on the number of lines.

Another solution is try to create a very similar table with the Table Tool at the Reporting section, but need to realize that it will not match 100% the template.

Another is to create the output and the user will fix the range of the table each time.

There is one potential solution, I never tried it before, however I was investigating to understand if it will be worth sending the time on it, and concluded that it is a very complex solution and therefore will not worth my time spending on it. Maybe one day when I will have some free time I would go back to that approach...

Andrew_T-H · Answer

Hi, thank you. I think I'm understanding it a bit more now. So would I need to set the excel template table up to include the maximum possible range of cells that my data could cover? My issue with this would be that it will most likely result in a lot of blank rows as the number of rows in the data will vary greatly each time the workflow is run. This wouldn't be too much of an issue though as the excel file is to be used as an input for Power BI so I could easily remove the blank rows in Power Query. I would ideally prefer the table to resize with the data that is output by alteryx though. Is there no way to achieve this?