Figured it out on my own after a week investigation.
Replace the connection string
From:
"DRIVER=Microsoft Excel Driver (*.xlsx);DSN='';DBQ=%s"), (LPCTSTR)csExcelFile
To:
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\MyExcel.xlsx;
and install MDAC 32bit since my application is 32 bit and the problem will be resolved.
Regards
Sampath.