Change this (Your Code)
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AchieveSummaryReportMonthly", ".xls"
To this (Make a folder on C:\ and call it Temp)
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AchieveSummaryReportMonthly", "C:\Temp\ASRM.xls"
I'm assuming that AchieveSummaryReportMonthly is the name of a table or query. After you have run the new code open C:\Temp and you will find ASRM.xls
If you have a look in your Access Database Default Folder for an excel workbook named .xls you will most likely see the file that you exported with your original code.
May I suggest that you name Table as tblNameOfTable, Query as qryNameOfQuery & Report as rptNameOfReport. This makes it a little easier for all.