Hi guys, how to import excel data that are only approved into access?
Below is my code for importing data:
Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\users\Desktop\FabricPO.xlsx"
If FileExist(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
If IsNull(DLookup("[Date]", "NewFabricPO")) Then
MsgBox "No new data to add"
Else
DoCmd.OpenQuery "qryappend", acViewNormal
End If
Else
MsgBox "File not found. Please check filename or file location."
End If
Dim SQLDelete As String
SQLDelete = "delete * from TempFromExcel"
DoCmd.RunSQL SQLDelete
End Sub
Function FileExist(sTestFile As String) As Boolean
'this function does not use DIR since it is possible that uou might have
'been in the middle of running DIR against another directory in
'an attempt to match one directory against another
'it does not handle wildcard characters
Dim lSize As Long
On Error Resume Next
'Preset length to -1 because files can be zero bytes in length
lSize = -1
'get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
FileExist = False
End If
End Function
Below is my code for importing data:
Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\users\Desktop\FabricPO.xlsx"
If FileExist(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
If IsNull(DLookup("[Date]", "NewFabricPO")) Then
MsgBox "No new data to add"
Else
DoCmd.OpenQuery "qryappend", acViewNormal
End If
Else
MsgBox "File not found. Please check filename or file location."
End If
Dim SQLDelete As String
SQLDelete = "delete * from TempFromExcel"
DoCmd.RunSQL SQLDelete
End Sub
Function FileExist(sTestFile As String) As Boolean
'this function does not use DIR since it is possible that uou might have
'been in the middle of running DIR against another directory in
'an attempt to match one directory against another
'it does not handle wildcard characters
Dim lSize As Long
On Error Resume Next
'Preset length to -1 because files can be zero bytes in length
lSize = -1
'get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
FileExist = False
End If
End Function