{"id":103,"date":"2012-10-14T09:19:20","date_gmt":"2012-10-14T09:19:20","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=103"},"modified":"2021-03-21T23:32:22","modified_gmt":"2021-03-22T03:32:22","slug":"confirm-record-count-on-excel-import","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=103","title":{"rendered":"Confirm record count after Excel import…"},"content":{"rendered":"

When importing records from Excel\u00a0here’s a way you can let the User know they all made it.<\/p>\n

Step 1<\/strong>…
\nIn your Import code \u00a0include the UpdateUser() line…<\/p>\n

\r\nFunction ImportMySpreadsheet()\r\n\r\n    Dim lngColumn As Long\r\n    Dim xlx As Object\r\n    Dim xlw As Object, xls As Object, xlc As Object\r\n    Dim dbs As DAO.Database\r\n    Dim rst As DAO.Recordset\r\n    Set xlx = CreateObject("Excel.Application")\r\n    Set xlw = xlx.Workbooks.Open(Me.txtImportPath & Me.txtDirectoryName & "\\" & Me.txtFileName)\r\n    Set xls = xlw.Worksheets(1)\r\n    Set xlc = xls.Range("A2")\r\n    Set dbs = CurrentDb()\r\n    Set rst = dbs.OpenRecordset("tblImport", dbOpenDynaset, dbSeeChanges)\r\n\r\n   'UpdateUser found on http:\/\/regina-whipp.com\/blog\/?p=100\r\n    UpdateUser ("Importing " & xls.usedrange.rows.Count \u2013 1 & " rows from spreadsheet\u2026")\r\n\r\n'balance of your code...\r\nEnd Function\r\n<\/pre>\n
Step 2<\/strong>…
\n<\/span>Then\u00a0go to\u00a0the On_Click<\/strong> event of the button you’re using for your Import\u00a0and after the line that calls\u00a0your Import code\u00a0place the line bolded below.<\/span><\/p>\n
Private Sub cmdImport_Click()<\/span><\/p>\n
\r\n      Call ImportMySpreadsheet\r\n      Me.lblUpdate2.Caption = DCount(\u201ciImportID\u201d, \u201ctblImport\u201d) & \u201d Records successfully imported!\u201d\r\n\r\n      balance of your code\u2026\r\n<\/pre>\n
End Sub<\/span><\/p>\n
Step 3<\/strong>…
\nGo back to your form and while still in Design View place\u00a0two Labels on your form, name one lblUpdate and the other name\u00a0lblUpdate2 and leave the Captions blank.\u00a0 (Tip <\/strong>~\u00a0If the Caption Property does not let you leave it blank, as in the Label disappears, add a period and Save.\u00a0 After the initial Save, remove the period and Save again.)\u00a0 lblUpdate will show you how many rows are in the Excel Spreadsheet and lblUpdate2 will tell you how many records made it into your Temp table, see Figure 1.\u00a0 If the counts don’t match they can then open the spreadsheet to see what went wrong.<\/span><\/p>\n
Figure 1<\/p><\/div>\n
This is also useful when the\u00a0processing of data is going to take a few minutes.\u00a0\u00a0You can\u00a0let your Users know what’s going on so they don’t get over anxious with the mouse while waiting for the process to finish.<\/span><\/p>\n
\u00a0Appendix<\/strong>…<\/p>\n
Private Sub <\/a>UpdateUser<\/strong>(strMsg As String)
\nlblUpdate.Caption = strMsg
\nMe.Repaint
\nDoEvents
\nEnd Sub<\/span><\/p>\n
BTAB Development – Import and Export Excel via Access<\/a><\/span><\/p>\n
Ken’s Import and Export via Access<\/a><\/span><\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
When importing records from Excel here’s a way you can let the User know they all made it.<\/p>\n
Step 1… In your Import code include the UpdateUser() line…<\/p>\n
 Function ImportMySpreadsheet() Dim lngColumn As Long Dim xlx As Object Dim xlw As Object, xls As Object, xlc As Object Dim dbs As DAO.Database Dim rst As […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,1],"tags":[64,34,23,21],"class_list":["post-103","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-general","tag-access-tips","tag-excel","tag-import-excel","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=103"}],"version-history":[{"count":32,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions"}],"predecessor-version":[{"id":1419,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions\/1419"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}