troubleshooting Question

MS Access - Import data from Excel to Access using Criteria

Avatar of WS
WSFlag for United Arab Emirates asked on
Microsoft AccessVBA
15 Comments4 Solutions315 ViewsLast Modified:
Hi,

I have a form that import data from excel from Access. I have written a code that Import data but i want to Import data on the basis of criteria as if the field datatype has text as "Cap" it should import that data to table "tblCap", if it have text as "Gen" it should import all those rows to "tblGen", is there any way to do that?

Attach is a code for import.

Thank you.
Function selectFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    With fd
        If .Show Then
            selectFile = .SelectedItems(1)
        Else
            'stop execution if nothing selected
            End
        End If
    End With
 
    Set fd = Nothing
End Function


Private Sub Command0_Click()
On Error GoTo ErrorHandler
    'disable ms access warnings
    DoCmd.SetWarnings False
 
    'Delete existing data in table
    'This is optional. If you are combining spreadsheets
    'in your table, don't run this query
    'DoCmd.RunSQL "DELETE * FROM names"
 
    'load spreadsheet
    DoCmd.TransferSpreadsheet acImport, 8, "tblCapacity", selectFile(), True
 MsgBox "Import Successful"
    're-enable ms access warnings
    DoCmd.SetWarnings True
 
Exit Sub
 
ErrorHandler:
MsgBox "There was an Error: " & Err & ": " & Error(Err)
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 15 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 4 Answers and 15 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