Running Batch Macro XLXB File (Error)

[SH_94]

Hi Community,

I had tried to look around the article on how to configure or modify the macro so that the all the file will be imported. However, i failed to get the result i want. 

I had tried to move the file to desktop and also update the path accordingly but the error still pop out as per screenshot below.

May i know if anyone know how to fix this as I have tried the method before suggested by other members.

Location of the file saved

Below is my workflow and source of file. Since i cant send the xlsb file, hence, i have convert it to xlsx. 

Appreciate if someone can help me on this

[CarliE]

Hi @SH_94 

I just tried running this with minor adjustments and did not have any issues. Check for the following:

1. Look at the sheet names in the XLSB -- The sheet names for the files attached are mostly AAA instead of sheet1 (XR222 has Sheet1 but the other files have AAA), so I updated the formula to look at AAA instead of Sheet1 and renamed the tab in XR222

2. Make sure you are able to access the excel binary files. Do you have the driver installed already? If not, refer to this link to help you: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Enable-Microsoft-Excel-Binar...

3. Make sure the paths are correct in both the macro and the directory tool.

4. if 1,2,3,4, etc. are your column headers make sure to uncheck 1st row has data

Let me know if you still have issues after making these changes.

Have a nice weekend!

[SH_94]

Hi @CarliE ,

Thanks a lot for the input.

I would like to clarify with you on the following :

1. If i have the multiple tab in one excel file and have multiple excel files but we all have different names ( but we all have the same header name), can alteryx read the data in this case? Or should we change the name of the tab before importing into the alteryx?

2. I have checked and confirmed that i have the excess to the binary files.

3. I would like  to confirm on the path for macro and directory tool. For directory tool , i choose the folder which consist of multiple excel files that i plan to run which consist of following features : different excel name, different name of the tab,same structure

While for the macro, i choose one of the file in the folder that i plan to run but i always have no confidence on the how should i edit the path name as per screenshot below. Should i just need to make sure both path are the same? May i know how can i edit the path in Alteryx so that it able to read the file correctly?

4. I will take note on this and thanks a lot for reminding me on this.

Have a great weekend too.

[CarliE]

XSLB wont allow for you to list sheet names as you can do with an XLSX. So I would make sure the tab names are the same. Your configuration looks good on the path. 

Is there a reason you need XLSB and not XLSX?

You are able to have different excel names with the same structure but the tab names should be consistent.

[SH_94]

Hi @CarliE ,

We use the XLSB file it is because the file was generated from the system. Currently we have three options to generate the data: 

1. CSV

2. XLSB

3.XLSX (only applicable for certain system).

In this case, may i know that is it we can only do it via XLSX file and not XLSB & CSV? 

Currently i have two plans as below:

1. Change all the format to XLSX

2. Change all the name tabs to be consistent.

Lastly, i would like to ask about the consistency of the name tab. Will alteryx identify any inconsistency if we have the name below:

1. Sheet 1

2. Sheet   1

3. Sheet 1  (extra spacing after 1)

Last but not least,thanks a lot for the great input.

[CarliE · Accepted solution]

@SH_94 

You can use XLSB with that macro if your tab names are consistent throughout each excel-- The different excel file names are okay since were switching out the path with the macro. From what I know, there is no way at the moment to pull in all the sheet names for the XLSB so its more dynamic for different tab names.

With that being said, if you wanted to have one XLSB with multiple tabs you or multiple excels with different tab names, you would have to bring it in a few times.

As for the excel, there is an option to list sheet names so you can essentially bring in the sheet names for an excel and then get the full path -- so if the sheet names were different, they can dynamically update in the formula where you were specifying the full path + the sheet name

CSV's don't allow for multiple tabs also.

The best way to go about this is to go the excel route or just have the directory tool like you had and make sure the tab names in each file are the same (including spacing -- having spacing will be an issue as well). 

Good luck 🙂 

[SH_94]

Hi @CarliE ,

Thanks a lot for your great input. It really helps a lot for me to build the workflow.

[CarliE]

Of course! Happy to help!

[SH_94]

Hi @CarliE ,

I am trying to handle with xlsb file which contain the following characteristics

1. Different excel file within different subfolders

2. Different sheet name for each excel file ( one sheet for each excel file)

Understood from your previous post , alteryx able to perform step 1 while we can't use the macro to perform the step 2 .Since alteryx is unable to read the data if there is different sheet name for each excel file, Can we change all the sheet name for each excel file to become same sheet name in alteryx?

So that we can require the Alteryx to read the each tab of excel files again.

Thank you.