How to use Blob tool to extract single sheet out of multiple sheets in the input file

[Shrikant_Pai]

Hi Team,

I have one single input file with 7 sheets in it and it has complex formatting and formulas in it.

In order to maintain the formatting of these sheets I have used blob input and output tool and its working fine.

With this, all the 7 sheets are present in the output with the formatting and formulas intact.

However I require only one sheet out of those 7 sheets without impacting the formatting of that required sheet in the output with all the formulas removed (dependent on rest 6 of them).

Is there a way to do this?

Your help is much appreciated :)

Thanks

[OTrieger]

@Shrikant_Pai 

You cannot create a template from the sheet that you need, deleting all the rest of the sheets and removing all the formulas from this sheet. In this way you will have the needed format, the rest of the data Alteryx can manage

[Shrikant_Pai]

sorry sir,I dint get you.

[apathetichell · Accepted solution]

short answer 'No. not natively'

[flying008]

Hi, @Shrikant_Pai 

You need use script to get your want.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/powershell-script-through-run-... 

1- Command:

PowerShell.exe

2- Command Arguments [Optional]:

"& {$excelFilePath = 'C:/your path/abc.xlsx'; $sheetName = 'Output'; $newFilePath = (Split-Path $excelFilePath -Parent) +  '/' + $sheetName + '.xlsx'; $excel = New-Object -ComObject Excel.Application; $excel.Visible = $false; $workbook = $excel.Workbooks.Open($excelFilePath);$worksheet = $workbook.Worksheets.Item($sheetName); $newWorkbook = $excel.Workbooks.Add(); $worksheet.Copy($newWorkbook.Worksheets.Item(1)); $newWorksheet = $newWorkbook.Worksheets.Item(1); $newWorksheet.UsedRange.Value2 = $newWorksheet.UsedRange.Value2; $newWorkbook.SaveAs($newFilePath, 51); $newWorkbook.Close(); $workbook.Close($false); $excel.Quit(); [System.Runtime.Interopservices.Marshal]::ReleaseComObject($newWorksheet) | Out-Null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($newWorkbook) | Out-Null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null; [System.GC]::Collect(); [System.GC]::WaitForPendingFinalizers(); Write-Host "Sheet [$sheetName] has been copied to 「 $newFilePath 」 with formatting and without formulas."}"

3- You must change the string  'C:/your path/abc.xlsx' as your real path and file name, and the 'Output' as your sheet name. please attention to the backslashes in the path; you must replace them correctly. The code above has been tested and works as expected. Don't worry.