Hello, Ananth.
How do you perform Excel to Access data import?
Regards,
Fish
I am using the below code to upload an excel file to Access. Kindly help me where should i define the code for Duplication.
Private Sub Bulk_Upload()
On Error GoTo Err_myError
Dim strmsg As String
Dim mypath As String
Dim rst As DAO.Recordset
Dim DB As Database
Dim r As Long
Dim wkbk As Workbook
Dim strDupName As String, strSaveName As String
Dim objRange1 As Range
Dim objRange2 As Range
Dim objExcel As Excel.Applicati on
Set objExcel = New Excel.Applicati on
Dim stDocName As String
Dim strSQL As String
DoCmd.SetWarnin gs False
Set DB = CurrentDb()
If Len(Nz(Me.Locat ion, "")) = 0 Then
Me.Status.Value = "Select the Excel file to upload......... ..!"
Me.Location.Set Focus
Exit Sub
End If
Set wkbk = Workbooks.Open( Me.Location, True)
If Range("A1").Val ue <> "StrInward_ No" _
Or Range("B1").Val ue <> "Inward_Dat e" _
Or Range("C1").Val ue <> "Hospital_N ame" _
Or Range("D1").Val ue <> "Date_of_Ad min" _
Or Range("E1").Val ue <> "Date_of_Discha rge" _
Or Range("F1").Val ue <> "Claim_Amt" _
Or Range("G1").Val ue <> "Policy_No" _
Or Range("H1").Val ue <> "Policy_Nam e" _
Or Range("I1").Val ue <> "UHID_Fast_Trac k" _
Or Range("J1").Val ue <> "Name_of_Patien t" _
Or Range("K1").Val ue <> "Claim_Rec_Date " _
Or Range("L1").Val ue <> "Claim_Type " _
Or Range("M1").Val ue <> "Claim_Stat us" _
Or Range("N1").Val ue <> "Claim_No" _
Or Range("O1").Val ue <> "Reason_Descrip tion" _
Or Range("P1").Val ue <> "Final_Query_St atus" _
Or Range("Q1").Val ue <> "Actual_Query_D ate" _
Then
ActiveWorkbook. Close
Me.Status.Value = "The process cannot access the file" & " " & Me.Location & " " & "...Column Numbers does not match the required definition"
Me.Location = ""
Exit Sub
End If
Set rst = DB.OpenRecordse t("Claim_Status ", dbOpenDynaset)
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0 ' repeat until first empty cell in column A
'
With rst
.AddNew ' create a new record
.Fields("StrInw ard_No") = Range("A" & r).Value ' add values to each field in the record
.Fields("Inward _Date") = Range("B" & r).Value
.Fields("Hospit al_Name") = Range("C" & r).Value
.Fields("Date_o f_Admin") = Range("D" & r).Value
.Fields("Date_o f_Discharge") = Range("E" & r).Value
.Fields("Claim_ Amt") = Range("F" & r).Value
.Fields("Policy _No") = Range("G" & r).Value
.Fields("Policy _Name") = Range("H" & r).Value
.Fields("UHID_F ast_Track") = Range("I" & r).Value
.Fields("Name_o f_Patient") = Range("J" & r).Value
.Fields("Claim_ Rec_Date") = Range("K" & r).Value
.Fields("Claim_ Type") = Range("L" & r).Value
.Fields("Claim_ Status") = Range("M" & r).Value
.Fields("Claim_ No") = Range("N" & r).Value
.Fields("Reason _Description") = Range("O" & r).Value
.Fields("Final_ Query_Status") = Range("P" & r).Value
.Fields("Actual _Query_Date") = Range("Q" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rst.Close
Set rst = Nothing
Set DB = Nothing
Excel.Applicati on.DisplayAlert s = False
ActiveWorkbook. Close
DoCmd.SetWarnin gs True
Exit_myError:
Exit Sub
Err_myError:
MsgBox Err.Description
Resume Exit_myError
End Sub
31
