Sub Import_BE_Specifics()
'Dim global variables
Dim Conn As ADODB.Connection
Dim DB_Path As String
Dim wb As Workbook
Dim ws_Setup As Worksheet
'Dim local variables
Dim ws_Output As Worksheet
Dim ws_specific As Worksheet
Dim rng_Export As Range
Dim rs As ADODB.Recordset
Dim rng_Null_Indicator As Range 'STEP 1 only, discards empty years of account
Dim As_at_Quarter As String 'STEP 3 only,
Dim Res_Class As String 'STEP 3 only,
'Dim working variables
Dim i As Integer
Dim j As Integer
Dim rs_sql As String
Dim str_Field As String
'Dim output variables
Dim date_Timestamp As Date
Dim str_DataSource As String
Application.ScreenUpdating = False
Application.Calculate
Application.Calculation = xlCalculationManual 'Turn off automated excel features
'Set global variables
Set wb = ThisWorkbook
Set ws_Setup = wb.Worksheets("Setup")
Set ws_specific = wb.Worksheets("Specific_Losses")
As_at_Quarter = ws_Setup.Range("_As_at_Quarter").Value
prev_as_at_quarter = ws_Setup.Range("_prev_asAtQuarter").Value
Res_Class = ws_Setup.Range("_Reserving_Class").Value
str_DataSource = ThisWorkbook.FullName
date_Timestamp = Now()
''## 1 # find the last blank row with formula in the specific losses sheet
ws_specific.Select
lr = Range("B5").End(xlDown).Row
Application.CutCopyMode = False
Range("B6:T6").Copy
Range("B7:T" & lr).PasteSpecial Paste:=xlPasteFormulas
ws_specific.Calculate
last_row = Range("B5:B" & lr).Find("", LookIn:=xlValues).Row
Set rng_Import = Range("B" & last_row)
''## 2 # POPULATE THE COLUMNS SQL SCRIPTS
rs_sql = "SELECT [Reserving_Class], [YOA], [SCC], [Underwriting_Entity], [Event_Code], [Claims_Name], [Best_Estimate_Adj_Flag], [GB_Gross_IBNR_SCC], [GB_RI_Prop_IBNR_SCC], [GB_RI_Fac_IBNR_SCC], [GB_RI_XL_IBNR_SCC], [GB_RI_RSTP_SCC], [GB_Used_in_Reserving], [GB_Already_in_Large_Count], [GB_In_large_reserving_adjustment], [GB_Additional_Large_Claims],[GB_Is_Large] from tbl_specific_ibnrs"
rs_sql = rs_sql & " WHERE as_at_quarter = '" & prev_as_at_quarter & "' AND [Reserving_Class] = '" & Res_Class & "' AND [Best_Estimate_Adj_Flag] = 1"
'Open reserving database connection
Set Conn = New ADODB.Connection
DB_Path = ws_Setup.Range("_Setup_Database_Location") & ws_Setup.Range("_Setup_Database_Name")
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DB_Path & ";"
Set rs = New Recordset
rs.Open rs_sql, Conn, adOpenStatic, adLockReadOnly
If Not rs.EOF Then
rng_Import.Offset(0, 2).CopyFromRecordset rs
End If
rs.Close
'Close Database Connection
Conn.Close
Set Conn = Nothing
'clear the extra 0s
Range("J" & last_row & ":J" & lr).ClearContents
Range("B5").Select
ws_Setup.Select
End Sub