Count Imported Rows From Excel Spreadsheet

[jimrand]

Hi

I am using Access 2007. How can I display to a user in a message box how many rows were imported when they click an import command button from a form?

This is my code to produce the import and the message I am currently displaying:

Private Sub ImportLatestSaleData_Click()
    
    
    
    DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
    
    
    MsgBox ("Update of Latest Sales Data Complete")
    
    
End Sub

Thanks

[MikeTheBike]

Hi

I am using Access 2007. How can I display to a user in a message box how many rows were imported when they click an import command button from a form?

This is my code to produce the import and the message I am currently displaying:

Private Sub ImportLatestSaleData_Click()
    
    
    
    DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
    
    
    MsgBox ("Update of Latest Sales Data Complete")
    
    
End Sub

Thanks

HI

I always use ADO connection/recordsets for this type of thing (gives you absolute contol of what is happening!), but if this always appends records and you want to know how many, then maybe this will work

Private Sub ImportLatestSaleData_Click()
      Dim StartNumber as long
      Dim FinishNumber as long
    
     StartNumber = DCount("*","ThisSale")

    DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True

     FinishNumber = DCount("*","ThisSale")    
    
    MsgBox ("Update of Latest Sales Data Complete (" & FinishNumber-StartNumber & " records added)" )
    
    
End Sub

??

Disclaimer!!
This is untried and a suggestion only

MTB

[jimrand]

HI

I always use ADO connection/recordsets for this type of thing (gives you absolute contol of what is happening!), but if this always appends records and you want to know how many, then maybe this will work

Private Sub ImportLatestSaleData_Click()
      Dim StartNumber as long
      Dim FinishNumber as long
    
     StartNumber = DCount("*","ThisSale")

    DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True

     FinishNumber = DCount("*","ThisSale")    
    
    MsgBox ("Update of Latest Sales Data Complete (" & FinishNumber-StartNumber & " records added)" )
    
    
End Sub

??

Disclaimer!!
This is untried and a suggestion only

MTB

MTB - thanks, it works perfectly and stops my head hurting!!!