Load multiple binary workbooks with inconsistent sheet names

[Virender_Sharma]

Hi, 

Can someone suggest how to load multiple .xlsb(Binary workbooks) in one go in Alteryx designer, where files are having different/random sheet names? Sheet names are not consistent across all files and having numeric string combination. one example of sheet name is : abc=123. 

[abacon]

@Virender_Sharma The issue is xlsb files are tricky to read the sheetnames in which is needed to do what you are trying to do. There are macros you can use to read in a directory of files and dynamically change the path to pull in all the data. Here are a few articles on how to get the sheetnames from xlsb files that may be helpful. Once you get that, you will use a simple batch macro to read in a directory of files and you can dynamically change the names. I have attached a workflow that works for xlsx files, once you read in the xlsb files, you can just switch the configurations to be for xlsb files and it should work for you. Just change the directory location to the location of your files.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Read-sheet-names-in-a-xlsb-and...

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Excel-Binary-Input-into-Altery...

[Virender_Sharma]

Hi, I have tried above however getting below error while running the workflow. If you can help.

Info: Designer x64: The Designer x64 reported: Running at a Low Priority.
Info: Designer x64: The Designer x64 reported: This is AMP Engine; running 8 worker threads; memory limit 4065.0 MB.
Error: Input Data (5): Error opening connect string: Microsoft Access Database Engine: The Microsoft Access database engine cannot open o It is already opened exclusively by another user, or you need permission to view and write its data.\3051 = -534709256 - Unspecified error
Info: Macro Output (6): 0 records were output.
End: Designer x64: Finished running GetSheetNames.yxmc in 2.3 seconds with 1 error using AMP engine

[abacon]

@Virender_Sharma did you switch the locations of the files to your directories? Or, is the file open by another user? The easiest solution is to convert these files to an xlsx file using a python script before you pull them in. Alteryx and xlsb seem to be tricky to use.