Function importDatatoXIAP()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As excel.Application
Dim i As Long
'***************************************************************************************
'The following code was created by Trevor G
'In May 2010
'Contact email is
'The purpose of the code is to repopulate the main table named "XIAP stub Dataset New"
'***************************************************************************************
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Open CurrentProject.Path & "\Q1 PI Data v4.xlsx"
.Visible = True
End With
Dim lngCount As Long
rst.Open "XIAP Stub dataset New", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("Q1 Data").Select
ws.Range("a2").Select
Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("Legal Entity").Value = ws.ActiveCell.Offset(0, 0).Value
.Fields("Entity No").Value = ws.ActiveCell.Offset(0, 1).Value
.Fields("UWYear").Value = ws.ActiveCell.Offset(0, 2).Value
.Fields("Cob 1").Value = ws.ActiveCell.Offset(0, 3).Value
.Fields("Cob 2").Value = ws.ActiveCell.Offset(0, 5).Value
.Fields("XIAP Product").Value = ws.ActiveCell.Offset(0, 6).Value
.Fields("New or Renewal").Value = ws.ActiveCell.Offset(0, 7).Value
.Fields("Lineslip/Binder").Value = ws.ActiveCell.Offset(0, 8).Value
.Fields("MI Ref").Value = ws.ActiveCell.Offset(0, 10).Value
.Fields("Declaration").Value = ws.ActiveCell.Offset(0, 11).Value
.Fields("Dec Master").Value = ws.ActiveCell.Offset(0, 12).Value
.Fields("Primary / Excess").Value = ws.ActiveCell.Offset(0, 14).Value
.Fields("Insured Code").Value = ws.ActiveCell.Offset(0, 18).Value
.Fields("Reinsured Code").Value = ws.ActiveCell.Offset(0, 20).Value
.Fields("Insured domicile").Value = ws.ActiveCell.Offset(0, 24).Value
.Fields("Territorial Scope").Value = ws.ActiveCell.Offset(0, 26).Value
.Fields("Broker Code").Value = ws.ActiveCell.Offset(0, 29).Value
.Fields("LIMIT CCY").Value = ws.ActiveCell.Offset(0, 31).Value
.Fields("Limit").Value = ws.ActiveCell.Offset(0, 32).Value
.Fields("Excess").Value = ws.ActiveCell.Offset(0, 33).Value
.Fields("100% GROSS EPI").Value = ws.ActiveCell.Offset(0, 34).Value
.Fields("DEDUCTIONS").Value = ws.ActiveCell.Offset(0, 36).Value
.Fields("OUR EPI GBP").Value = ws.ActiveCell.Offset(0, 37).Value
.Fields("INCEPT DATE").Value = ws.ActiveCell.Offset(0, 38).Value
.Fields("EXPIRY DATE").Value = ws.ActiveCell.Offset(0, 39).Value
.Fields("UWR ID Code").Value = ws.ActiveCell.Offset(0, 40).Value
.Fields("Programme Reference").Value = ws.ActiveCell.Offset(0, 42).Value
.Fields("Trade Code").Value = ws.ActiveCell.Offset(0, 46).Value
.Fields("Leader Code").Value = ws.ActiveCell.Offset(0, 48).Value
.Fields("Branch Code").Value = ws.ActiveCell.Offset(0, 51).Value
.Fields("Inwards Header Cat").Value = ws.ActiveCell.Offset(0, 53).Value
.Fields("Inwards Header Type Code").Value = ws.ActiveCell.Offset(0, 54).Value
.Fields("Participant Type").Value = ws.ActiveCell.Offset(0, 55).Value
.Fields("Participant 1").Value = ws.ActiveCell.Offset(0, 56).Value
.Fields("Participant 1 Reference").Value = ws.ActiveCell.Offset(0, 57).Value
.Fields("Participant 1 Line").Value = ws.ActiveCell.Offset(0, 58).Value
.Fields("Participant 2").Value = ws.ActiveCell.Offset(0, 59).Value
.Fields("Participant 2 Reference").Value = ws.ActiveCell.Offset(0, 60).Value
.Fields("Participant 2 Line").Value = ws.ActiveCell.Offset(0, 61).Value
.Fields("Layer No").Value = ws.ActiveCell.Offset(0, 62).Value
.Fields("Section Type Code").Value = ws.ActiveCell.Offset(0, 63).Value
.Fields("File Handler").Value = ws.ActiveCell.Offset(0, 64).Value
.Fields("Period Description").Value = ws.ActiveCell.Offset(0, 65).Value
.Update
lngCount = lngCount + 1
End With
ws.ActiveCell.Offset(1, 0).Select
Loop
ws.Range("A1").Select
ws.Quit
End Function