Error with batch macro in (.xls) files

[Rahul_Analyst]

Hi,

I have multiple excel workbooks in .xls type excel. I have set up a batch macro in auto configuration by position. It is working for some .xls files but for some it is giving me error. 

Can you please look at it and help me know the reason why it is happening and how I can resolve it ?

[gabrielvilella]

You need this driver: JET Driver Error

[Rahul_Analyst]

Hi,

If I download this driver, my current 2019 version will not be working in my system. ( is it so ?)

Is there any solution which can help me get the outcome with current deriver ?

I tried to change excel file type to (.xlsx) then use this macro but then getting different error. 

[gabrielvilella]

The driver is required to read old XLS files. It will not affect XLSX files. Changing the file extension manually will not work. 

[Rahul_Analyst]

@gabrielvilella Thanks a lot!

If this driver is needed, then without having this driver, Alteryx is giving me output (doing union for 7 .xls files) for rest of the files (25 .xls files) not working. 

Just curious to know, how it is working for 7 files. It should not work for even single file.

[RolandSchubert]

Hi @Rahul_Analyst ,

it seems to be a mixture of xls and xlsx files you want to load using the macro. In addition to changing the filename you also have to change the fileformat:

This means, FileFormat as an additional parameter is needed:

You have to calculate the fileformat in your workflow and hand it over to the batch macro (xls = 8 / xlsx = 25). I've attached a sample.

Let me know if it work for you.

Best,

Roland

[Rahul_Analyst]

Hi, @RolandSchubert 

There is no mixture of .xls and xlsx file. I have only one type file .xls. In fact, if there is mixture then after removing .xlsx files, this macro should work with at least .xls files. But it is not working and giving error as attached picture. 

@gabrielvilella , he provided a solution of downloading a driver, which I will have to take permission for it. But, if it can be done without it, would be great. 

[Rahul_Analyst · Accepted solution]

Hi all, 

let me conclude it as a solution which worked. And, it was possible with the help of @gabrielvilella @RolandSchubert , thank you so much for the help.

1. .xls file can be changed manually to .xlsx files
2. The path in directory should be short. It should not be long. 

Thank you. 

[Felipe_Ribeir0]

Hi @Rahul_Analyst . Just to share with you, i had the same problem and in my case, the input of .xls files was recurrent, so it would be a problem to change manually from .xls to .xlsx. So i built this macro that uses python to convert .xls into .xlsx and uses the xlsx as input.

Convert .xls into .xlsx and parse the input - Alteryx Community

Hope that this is useful for you!