423,850 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

Count Imported Rows From Excel Spreadsheet

P: 10
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportLatestSaleData_Click()
  2.  
  3.  
  4.  
  5.     DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
  6.  
  7.  
  8.     MsgBox ("Update of Latest Sales Data Complete")
  9.  
  10.  
  11. End Sub

Thanks
Feb 12 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportLatestSaleData_Click()
  2.  
  3.  
  4.  
  5.     DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
  6.  
  7.  
  8.     MsgBox ("Update of Latest Sales Data Complete")
  9.  
  10.  
  11. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportLatestSaleData_Click()
  2.       Dim StartNumber as long
  3.       Dim FinishNumber as long
  4.  
  5.      StartNumber = DCount("*","ThisSale")
  6.  
  7.     DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
  8.  
  9.      FinishNumber = DCount("*","ThisSale")    
  10.  
  11.     MsgBox ("Update of Latest Sales Data Complete (" & FinishNumber-StartNumber & " records added)" )
  12.  
  13.  
  14. End Sub

??

Disclaimer!!
This is untried and a suggestion only

MTB
Feb 12 '08 #2

P: 10
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportLatestSaleData_Click()
  2.       Dim StartNumber as long
  3.       Dim FinishNumber as long
  4.  
  5.      StartNumber = DCount("*","ThisSale")
  6.  
  7.     DoCmd.TransferSpreadsheet acImport, , "ThisSale", "S:\Accounts\Jim\Fleet\Disposals\BCA\ThisSale.xls", True
  8.  
  9.      FinishNumber = DCount("*","ThisSale")    
  10.  
  11.     MsgBox ("Update of Latest Sales Data Complete (" & FinishNumber-StartNumber & " records added)" )
  12.  
  13.  
  14. End Sub

??

Disclaimer!!
This is untried and a suggestion only

MTB
MTB - thanks, it works perfectly and stops my head hurting!!!
Feb 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.