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

[SH_94]

Hi @BenMoss ,

Thanks a lot for the prompt response.

I have three queries which i would like to clarify with you as below:

Firstly, may i know if i would need to amend the following script for the first three line? If i would need to amend the script, could you briefly explain how should i amend the script?

Secondly, since i does not have powershell software, i use the notepad instead. Do i need to amend the details in the workflow ?

Thirdly, may i know how can i enable the tab "event" in this case and would like to confirm that we will apply it in the directory tools as per screenshot below since it is the first tool before importing the data?

Thanks again for your time and help.

[BenMoss]

1. No need to edit

2. Powershell is installed on windows by default, so no

3. The event is not linked to a specific tool, and therefor the events tab only resides on the workflow configuration pane. As shown in the screenshot you then choose the option ‘Before Run’, so this script will run before your workflow then runs, meaning that at the point at which you start reading your files into Alteryx all the sheets will have been renamed.

Ben

[BenMoss]

Make sure you include \*.xlsb on the end of your folder specification in the script though, I see you seem to have missed this. 

[SH_94]

@BenMoss ,

Thanks a lot again for the prompt response.

May i know how you know the command arguments as per screenshot below? Is it refer to the location of script?As i have save the script at the desktop.

[BenMoss]

Yes you need to replace the parts in double qoutes with your details (script location and name, with the .ps1 extenstion).

Ben