Error opening table: Microsoft Access Database Engine (happens with xls 97-2003 only)

[AkisM]

Dynamic Input (12) Error opening table: Microsoft Access Database Engine: The Microsoft Access database engine could not find the object Make sure the object exists and that you spell its name and the path name correctly. 

How do I fix this? Do I need some sort of driver I'm potentially missing (link please)?  Workflow works fine if I'm using xlsx files.

The weird thing is I can open .xls (97-2003) files too, if I'm just selecting 1 file using the input tool. The error I described above happens only if I'm trying to import multiple excels that are 97-2003 format (formula tool to change entire file path is configured correctly, I validated this because I run the exact same setup with .xlsx files and it works)

[BrandonB · Accepted solution]

Do you have the Access Driver installed yet? https://www.microsoft.com/en-us/download/details.aspx?id=13255

[AkisM]

@BrandonB Yes, I already have the 32x version of that driver installed. Tried installing the 64x one too for good measure but it's not possible without uninstalling all 32x microsoft products yet. I don't think it should matter though since i already have the 32 bit one.

[BrandonB]

Have you tried the macro approach rather than a Dynamic Input? http://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

[AkisM]

@BrandonB Yes, in fact the batch macro approach is the first one I tried as that's my ideal solution. I just also tried it with the dynamic tool to rule out the issue being something macro related. Same error occurs with both methods.

[AkisM · Accepted solution]

@BrandonB Looks like I may have been missing something because I tried a few more times and eventually it worked. What I ended up using as full path had the sheet name in this format: "|||'Sheet1$'"

Whereas for xlsx it worked with other formats too. But using the above format worked with both xlsx and xls. Thanks for your help anyway.

[BrandonB]

I'm glad its working now! Sometimes there can be a sneaky configuration option that slips past. One way to always check if the file path syntax is correct is by opening that file and that sheet in a regular Input Data tool. You will see the file path with sheet name listed out in the configuration window which you can then model a formula off of. 

[JokeFun]

Exactly the same issue and the same solution. Just adding $ fixed the problem.

[maheshbansal05]

@ AkisM, Could you provide workflow, Actually I tried lots of option but not solved yet