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.Application
Set objExcel = New Excel.Application
Dim stDocName As String
Dim strSQL As String
DoCmd.SetWarnings False
Set DB = CurrentDb()
If Len(Nz(Me.Location, "")) = 0 Then
Me.Status.Value = "Select the Excel file to upload...........!"
Me.Location.SetFocus
Exit Sub
End If
Set wkbk = Workbooks.Open(Me.Location, True)
If Range("A1").Value <> "StrInward_No" _
Or Range("B1").Value <> "Inward_Date" _
Or Range("C1").Value <> "Hospital_Name" _
Or Range("D1").Value <> "Date_of_Admin" _
Or Range("E1").Value <> "Date_of_Discharge" _
Or Range("F1").Value <> "Claim_Amt" _
Or Range("G1").Value <> "Policy_No" _
Or Range("H1").Value <> "Policy_Name" _
Or Range("I1").Value <> "UHID_Fast_Track" _
Or Range("J1").Value <> "Name_of_Patient" _
Or Range("K1").Value <> "Claim_Rec_Date" _
Or Range("L1").Value <> "Claim_Type" _
Or Range("M1").Value <> "Claim_Status" _
Or Range("N1").Value <> "Claim_No" _
Or Range("O1").Value <> "Reason_Description" _
Or Range("P1").Value <> "Final_Query_Status" _
Or Range("Q1").Value <> "Actual_Query_Date" _
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.OpenRecordset("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("StrInward_No") = Range("A" & r).Value ' add values to each field in the record
.Fields("Inward_Date") = Range("B" & r).Value
.Fields("Hospital_Name") = Range("C" & r).Value
.Fields("Date_of_Admin") = Range("D" & r).Value
.Fields("Date_of_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_Fast_Track") = Range("I" & r).Value
.Fields("Name_of_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.Application.DisplayAlerts = False
ActiveWorkbook.Close
DoCmd.SetWarnings True
Exit_myError:
Exit Sub
Err_myError:
MsgBox Err.Description
Resume Exit_myError
End Sub
31
