Option Explicit

Private Sub BtnLoad_Click()

Dim CN As Object
Dim RS As Object
Dim strPath As String
Dim Customer As String, Salutation As String, FirstName As String, Surname As String, Telephone As String
Dim Mobile As String, SiteMobile As String, EMail As String, Address1 As String, Address2 As String, Address3 As String
Dim TownCity As String, PostCode As String, SageNo As String, CurrentStatus As String, DTA As String
Dim SLADate As String, Services As String, BagLimit As String, Charge As String, ChargePeriod As String, Sites As String

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

    'On Error Resume Next 'leave this out while testing as it will tend to show where tany errors lie

    Set CN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3

    strPath = "C:\Path"

    CN.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
            & strPath & ";Extensions=asc,csv,tab,txt;HDR=YES;Persist Security Info=False"

    RS.Open "SELECT * FROM Customer_Report.csv", CN, adOpenStatic, adLockOptimistic, adCmdText

    Do Until RS.EOF
        Customer = RS("Customer")
        Salutation = RS("Salutation")
        FirstName = RS("First Name")
        Surname = RS("Surname")
        Telephone = RS("Telephone")
        Mobile = RS("Mobile")
        SiteMobile = RS("Site Mobile")
        EMail = RS("EMail")
        Address1 = RS("Address 1")
        Address2 = RS("Address 2")
        Address3 = RS("Address 3")
        PostCode = RS("Post Code")
        SageNo = RS("Sage Account")
        CurrentStatus = RS("Current Status")
        DTA = RS("DTA Service")
        SLADate = RS("SLA Date")
        Services = RS("Current Services")
        BagLimit = RS("Bag Limit")
        Charge = RS("Charge")
        ChargePeriod = RS("Charge Period")
        Sites = RS("Sites")
        'Now do something with the record
Debug.Print Customer & ", " & Salutation & ", " & FirstName & ", " & Surname
        RS.MoveNext
    Loop
lbl_Exit:
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
    Exit Sub
End Sub