We help IT Professionals succeed at work.
Get Started

Import from Excel but limit records to import

pdvsa
pdvsa asked
on
68 Views
Last Modified: 2020-01-30
Experts,

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
         
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

Open in new window

Comment
Watch Question
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE