Confirm record count after Excel import…

When importing records from Excel here’s a way you can let the User know they all made it.

Step 1…
In your Import code  include the UpdateUser() line…

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 DAO.Recordset
    Set xlx = CreateObject("Excel.Application")
    Set xlw = xlx.Workbooks.Open(Me.txtImportPath & Me.txtDirectoryName & "\" & Me.txtFileName)
    Set xls = xlw.Worksheets(1)
    Set xlc = xls.Range("A2")
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblImport", dbOpenDynaset, dbSeeChanges)

   'UpdateUser found on https://regina-whipp.com/blog/?p=100
    UpdateUser ("Importing " & xls.usedrange.rows.Count – 1 & " rows from spreadsheet…")

'balance of your code...
End Function

Step 2…
Then go to the On_Click event of the button you’re using for your Import and after the line that calls your Import code place the line bolded below.

Private Sub cmdImport_Click()

      Call ImportMySpreadsheet
      Me.lblUpdate2.Caption = DCount(“iImportID”, “tblImport”) & ” Records successfully imported!”

      balance of your code…

End Sub

Step 3…
Go back to your form and while still in Design View place two Labels on your form, name one lblUpdate and the other name lblUpdate2 and leave the Captions blank.  (Tip ~ If the Caption Property does not let you leave it blank, as in the Label disappears, add a period and Save.  After the initial Save, remove the period and Save again.)  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.  If the counts don’t match they can then open the spreadsheet to see what went wrong.

Figure 1

This is also useful when the processing of data is going to take a few minutes.  You can let your Users know what’s going on so they don’t get over anxious with the mouse while waiting for the process to finish.

 Appendix…

Private Sub UpdateUser(strMsg As String)
lblUpdate.Caption = strMsg
Me.Repaint
DoEvents
End Sub

BTAB Development – Import and Export Excel via Access

Ken’s Import and Export via Access

 1,471 total views,  1 views today