Microsoft Office Forums - View Single Post - [Solved] Trying to read CSV file and place values into word bookmarks

gmayor · 10-21-2016, 06:49 AM

You have declared no Header, yet you are looking for values that relate to the header, so that isn't going to work.

Declare all your variables.

The following will work provided the header row names in the CSV match the values requested and the path to the CSV file is where the code says it is.

Lose the space from the CSV's file name.

You have a loop so all the code is going to do is write each record in turn to all the variables, finishing with the variables from the last record populated.

You need to do something with those variables for each record. I have just written a few of them to the immediate window of the VBA editor to demonstrate that it does work, but if you are going to write them to Word bookmarks you are going to have to create a new document from the template with the bookmarks for each record and I suggest using the FillBM function from my web site (use the site search) to write the values to named bookmarks.

Code:

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