Ensure that both Tally and MS Office are of the same bit-ness, that is, if both applications are of 32 bit or 64 bit then both can communicate without any problem.
If Tally application is 64 bit and acting as client and MS office (i.e Excel is 32 bit acting as Server) then ideally they cannot communicate with each other. However you can follow below steps to make both applications communicate with each other by using ODBC interface
Install AccessDatabaseEngine_x64.exe by following the below steps:
Download the file - AccessDatabaseEngine_x64 to local system
Open Command prompt with administrative rights
Change Directory to exact file location. (Where the file AccessDatabaseEngine_x64 is located)
Type this command and Enter -> AccessDatabaseEngine_x64/passive
Once the Exe is installed. Re-start the Tally and proceed with the Import.
Further, to read an Excel File irrespective of the formats, one can use the following connection string:
"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=;"