Rename sheet name in xlsb file

SH_94

Hi Community, 

May i know if we can change the sheet name for each excel file (xlsb) in Alteryx to become same sheet name across all the file? As i plan to use macro to read the multiple xlsb files.

Appreciate if you can share the workflow on how to build it in the Alteryx.

Thank you.

BenMoss

Hi,

This isn't really possible using the standard tools within Alteryx, you'll have to run external code to do this, however this code can be triggered from within Alteryx, before your workflow runs, using the 'events' tab on the workflow configuration pane.

The complexity here is setting up your .ps1 file (which you would do in notepad or another text editor, and just rename the extension .ps1

$excel = new-object -ComObject "Excel.Application"

$excel.DisplayAlerts=$true

$excel.Visible =$false

$files = dir("C:\Users\BenMoss\Desktop\*.xlsb")

foreach ($file in $files) 

{
    
    $workbook = $excel.Workbooks.Open($file)
    $worksheet = $workbook.worksheets.item(1)
    $worksheet.name = "data"
    $workbook.Save()
    $workbook.Close()
}

$excel.Quit()

The powershell script above will look at all xlsb files stored on my desktop and rename the first tab to 'data'. You may need to tweak the script above to work through all files within subfolders, but I'm not going to do all the leg work here!

Ben