Read sheet names in a xlsb and only pull through one specific sheet name

Question

Hi all I am stumped on this...i am attempting to read a directory with multiple xlsb files. From there I need to look for a specific sheet name, if any of those xlsb files contain that sheet name I need to carry only those forward and then open that sheet up and see all the data within those multiple sheets. I have the extra driver installed so i can open xlsb files, i just am having trouble because i can't view all the sheet names like you normally can with a xlsx.

I have seen macros and stuff online that convert xlsb to xlsx to read the sheet names, my only concern is does this permanently change the type of file extension in the directory i'm pulling the xlsb in from? because for me, that would be a big no no.

caltang · Answer

I asked a related question a few months ago: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Excel-Binary-Input-into-Alteryx/m-p/1139974/highlight/true#M281729

Refer to @flying008 ’a comment and Python solution to achieve this.

Otherwise, if you already know all the sheet names and they are static, you can use a Directory tool to get your file path, then add the sheet names (append to it), then try using a Batch Macro as stated above.

albert_alaluf · Answer

@laurennewton hi, save your excel file as .xlsx file. .xlsb is just compressed binary file of your excel document. When you have .xlsx, you can read it.

Albert

laurennewton · Answer

@Prometheus hi, this only works with xlsx it looks like. i need to read the sheet names of a xlsb file