Macro to convert .XLSB and .XLSM files into .XLSX format

[RajatRehria]

Hi Alteryx Community,

I generally receive multiple files some are in .XLSB , some are in .XLSM and others in .XLSX format, however i need files only in .XLSX format.

So I need a Macro or any other solution using Alteryx which can convert all the .XLSB and .XLSM files kept in a folder into .XLSX format.

Kindly help. Thanks.

[caltang]

You plan to use Alteryx just to convert the files? 

I think you may run into issues uploading those files. Here's documentation on how to solve that part first: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-import-xlsb/td-p/106562...

Once there, you can call them separately in the Input Tools or gallery tools, then output to .XLSX...

Maybe you want to do some more cleaning on Alteryx before you output? What's the end goal here?

[ArnaldoSandoval]

Hi @RajatRehria 

I am attaching a workflow able to process several file types, like: CSV, CSV with pipes, XLS (old Excel), XLSX (multi-sheets) and XLSB (binary - new - just added)

Once you unpacked the attached file, it creates two folders: Folder (which contains several test files) and Template (which contains template files required by each specialized macro); the unpacked file should look like the one below:

The workflow: Load-Multi-Files-Types-01 is the workflow processing all the files located at the Folder Folder (yup, the folder was named Folder) and generate Excel files written to the Output folder which is created automatically when you run the workflow; This workflow is a mega converter as it support: CSV, XLS, XLSX and XLSB.

The Load-Multi-Files-Types-01 relies on the macro Input_Any_File.yxmc, this macro takes two parameters, TargetFile and TargetOutput, while TargetFile contains the Full Path, File Type and Base Name of the file being converted to Excel.

Currently the macro call several macros depending on the file type. The XLSB macro is still work in progress as I hardcoded the name of the worksheet to be "Sheet1", you may have to change it for your tests.

I also attached the Test-File workflow, once you unpack the file: Load-Multi-Files-Types-01.yxzp you can copy the workflow to its folder (Load-Multi-Files-Types-01) it should open a sample binary Excel file, this is to validate @caltang warning which could be true on you environment.

I am working with a Laptop where I installed several products like "Oracle", "MS SQL", "PostgreSQL" and more, perhaps, the MS SQL installed the component that allows Alteryx to open XLSB file, this is something we have to test in your environment.

Hope this helps,

Arnaldo

[caltang]

Fantastic explanation as always my friend @ArnaldoSandoval !

[RajatRehria]

Thanks you so much all for sharing the possible solution, however i am looking for some small macro or a few lines of code starts with @Echo off and saved in .Bat file and when i keep and run that Bat file in the same folder where i have .XLSB and .XLSM files and after running the .Bat file the files converted to .XLSX format. Thanks.

[ArnaldoSandoval]

Hi @RajatRehria 

It sounds like you are after a batch file, I can't help you with that, anyhow the workflow that I shared here Convert Excel tabs to PDF (keeping very specific formats) may help you, just change the script to open the XLSB file and the same vbs script saves the file as XLSX. Let us know your thought about this option.

Regards,

Arnaldo

[ArnaldoSandoval]

@RajatRehria 

I created a batch file: "Convert_Xlsb_2_xlsx.bat" that Open Excel Binary files and save them as standard Excel files; the batch file executes a shell script "xlsb_to_xlsx.vbs" The attached TestScript.yxzp contains both the bat and vbs script files.

Note: You do not have to run any Alteryx workflow, just open the attached TestScript to unzip the scripts, once they are unzipped just run "Convert_Xlsb_2_xlsx" from a DOS Console.

hth

Arnaldo

[RajatRehria]

Hi @ArnaldoSandoval thanks for this solution as well, however whenever i am trying to run the Convert_Xlsb_2_xlsx.bat file no a black window (Command window) opens for less than half second and then auto closed, no files converted into .XLSX

[ArnaldoSandoval]

Hi @RajatRehria 

This is a step by step execution

• Open a DOS session: How to Open Windows Command Prompt in Windows 10 (also Windows 11) 
• Within the DOS session navigate to the folder containing the batch file.

Notice that I have an XLSB on this folder.

• Type the command:

Now, because the file "Excel_Binary_File.xlsx" already exist, you get  a prompt to replace it!

• Now, I will copy the Excel binary to my D:\Temp folder, and rename it with a white space in its name, like so

A file name with spaces in their name, or folder with spaces were a pain back in DOS and Windows age, they still are.

• Running batch file, targetting folder-file without spaces in its name.

• If the target folder-file name contains spaces, then enclose the name in single or double quotes.

Scripts Updates:

• We don't code in VbScript and DOS Batch files on a daily basis, actually longer than 5 years or more ago 🙂
• The batch script now clear the screen and pause after execution, so you can see the messages, I am attaching its new version.
• The VBS script also changed, handling folder-file with spaces is, was, and always be challenging.
• You should run this batch file from a DOS session as shown above, if you want to run it as a shortcut, that's a totally different procedure.