Running Batch Macro XLXB File (Error)

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

XR221.xlsx

XR220.xlsx

XR222.xlsx

Open Different Files XLSB.yxmd

XLSB Opener.yxmc

Common Use Cases

Accepted answers

CarliE

@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 🙂

All comments

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-Binary-Excel-Legacy-Access-accdb/ta-p/513178

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703