I'm needing help with the Blob!

[Andrew_T-H]

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

[OTrieger]

@Andrew_T-H 
There is a big difference when you copy an paste a new data to excel, where the table will create a new line to Alteryx pasting the data to the desired range. 

When you are using Blob tool after that Alteryx filled in the template, the existing rows in the table will have the table format while the additional rows will not. In excel you can define a range as a table, however in Alteryx not, you can define a range.

So when you are using blob the template need to have the desired format for that whole range in advance, because that Alteryx will use the original format for that specific cell, so you will need to find a more creative solution for it

[Andrew_T-H]

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?

[OTrieger]

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

[flying008]

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.