@lookforsmt, in #8 you wrote
i dont want to use file dialog method as the user needs to scroll through many files which is highly likely to select the wrong file.
I think you are successfully proving that it is harder to type in the right file and path.
In your statement
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_test", strTable, True
you are trying to import the contents of strTable. which you have prompted the user to enter the name of a new table.
Insert a few debug.prints in your code to see just what you are doing, eg
debug.print strTable, strFile