troubleshooting Question

Import from Excel but limit records to import

Avatar of pdvsa
pdvsaFlag for United States of America asked on
Microsoft Access
13 Comments1 Solution72 ViewsLast Modified:

I  am importing data from excel (file name is ImportToAccess.xlsx)
It works fine.
I now only need to import certain records.
The records I would not import would be if there is a match on certain fields.

The fields are:
[Trade #] property is short text
[Buy CP]  property is short text
[Quantity BBLS]  property is number
[Batch] property is short text

How could I modify the attached code to only import records that do not match between ImportToAccess.xlsx and the file in access that is named the tblImportToAccess.
I am currently deleting all data from tblImportToAccess before I import and I think that part would have to be modified.
I hope that makes sense.  
Let me know if you require more information.  FYI:  I am a novice user of Access and do not have much coding experience.

below please find the code:

Private Sub btnImport_Click_Click()

    If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then

       Exit Sub
    End If
 CurrentDb.Execute "delete * from [tblImportToAccess]"
    Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", , False)
   strName = "ToImport"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing

    DoCmd.TransferSpreadsheet acImport, , "tblImportToAccess", "C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", True, strName & "!"
    MsgBox "Data has been imported.  The table will now appear and need to check for accuracy.", vbInformation
    DoCmd.OpenTable "tblImportToAccess"

On Error GoTo EH
If Err.Number = 2450 Then
    Exit Sub
    'resume next  'don't know what this is for.
ElseIf Err.Number <> 0 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description
End If

End Sub
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros