Hello,
I have an application created in Access 2003, which has a button to import three Excel files using DoCmd.TransferSpreadsheet. I recently updated to Access 2010 and am getting the above referenced error message when I click the Import button.
After the import completes, I have a procedure that will export two Excel files as well. I have not yet saved this application with the new .accdb format, as there are still a couple of users that have not yet been upgraded. Not sure if that will make the error message go away.
I tried removing the spaces from the file name that I am trying to import.
I tried saving the files as .xlsx as well.
Not sure what else to try, so I am asking for someone to review my code, for which the debugger highlights the first DoCmd.TransferSpreadsheet:
Private Sub cmdImportBankData_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClearTblImportPriorDayTransactions"
DoCmd.OpenQuery "qryClearTblPFBSTransactionReport"
DoCmd.TransferSpreadsheet , acSpreadsheet, "tblImportPriorDayTransactions", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting Transaction Report.xls", True
DoCmd.OpenQuery "qryClearImportPriorDayBalances"
DoCmd.TransferSpreadsheet , acSpreadsheet, "tblImportPriorDayBalances", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting Balance Report.xls", True
DoCmd.OpenQuery "qryClearImportPriorDayLedger"
DoCmd.OpenQuery "qryClearTblWorkingPriorDayLedger"
DoCmd.TransferSpreadsheet , acSpreadsheet, "tblImportPriorDayLedger", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting Ledger Report.xls", True
DoCmd.OpenQuery "qryClearTblImportPriorDayClaimsTransactions"
DoCmd.TransferSpreadsheet acImport, SpreadsheetType = 9, "tblImportPriorDayClaimsTransactions", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting CD Transaction Report.xls", True, "Import File!"
MsgBox "Import has completed."
End Sub
Thank you, in advance!
Vickster3659