Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-21-2016, 05:35 AM
Philip1 Philip1 is offline Trying to read CSV file and place values into word bookmarks Windows 7 64bit Trying to read CSV file and place values into word bookmarks Office 2010 64bit
Novice
Trying to read CSV file and place values into word bookmarks
 
Join Date: Oct 2016
Posts: 3
Philip1 is on a distinguished road
Default Trying to read CSV file and place values into word bookmarks

I have the following code which doesn't seem to do anything on the above.

At this moment in time I'm just trying to read the CSV and use MSG Box to display field values.

Can you please help?


Option Explicit

Private Sub BtnLoad_Click()
On Error GoTo Err_Catch

Err_Catch:
Resume Next

Dim Customer, Salutation, FirstName, Surname, Telephone, Mobile, SiteMobile, EMail, Address1, Address2, Address3, TownCity, _
PostCode, SageNo, CurrentStatus, DTA, SLADate, Services, BagLimit, Charge, ChargePeriod, Sites, strSQL As String
Dim Bookmark, CurrentField As Range

Dim CSVConnection As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set CSVConnection = New ADODB.Connection
Set rs = New ADODB.Recordset



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

strSQL = "SELECT * FROM Customer Report.csv"

rs.Open strSQL, CSVConnection, adOpenStatic, adLockReadOnly

Do While Not rs.EOF
MsgBox rs(0)
rs.MoveNext
Loop

If rs.RecordCount > 0 Then
Do
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")

rs.MoveNext
Loop Until rs.EOF
End If

rs.Close
Set CSVConnection = Nothing

End Sub
Reply With Quote
  #2  
Old 10-21-2016, 06:49 AM
gmayor's Avatar
gmayor gmayor is offline Trying to read CSV file and place values into word bookmarks Windows 10 Trying to read CSV file and place values into word bookmarks Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 10-21-2016, 01:58 PM
macropod's Avatar
macropod macropod is offline Trying to read CSV file and place values into word bookmarks Windows 7 64bit Trying to read CSV file and place values into word bookmarks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

On a related issue, one would have to ask why you're using bookmarks instead of mergefields and Word's mailmerge tools to populate the document. That would be significantly simpler and more flexible than what you now have.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 10-24-2016, 01:39 AM
Philip1 Philip1 is offline Trying to read CSV file and place values into word bookmarks Windows 7 64bit Trying to read CSV file and place values into word bookmarks Office 2010 64bit
Novice
Trying to read CSV file and place values into word bookmarks
 
Join Date: Oct 2016
Posts: 3
Philip1 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
On a related issue, one would have to ask why you're using bookmarks instead of mergefields and Word's mailmerge tools to populate the document. That would be significantly simpler and more flexible than what you now have.
That's a good point I didn't consider actually, I suppose I was trying to make this as easy to do as possible for the document I'm creating. quite surprisingly i've encountered people in admin roles who don't know how to use mail merge and constantly finding people get bored with the process quickly if they have to click more than twice.

On the issue at hand. I'm now getting the following error:

run-time error '-2147467259 (80004005)';

[Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified.

I've ran the driver manager here: "%SystemRoot%\System32\odbcad32.exe"
And it is correct I have no ODBC driver installed. Strange.
Anyone know where I can get the correct driver?

I did try myself by installing this one:
https://www.microsoft.com/en-gb/down....aspx?id=36434

but no luck
Reply With Quote
  #5  
Old 10-24-2016, 04:01 PM
macropod's Avatar
macropod macropod is offline Trying to read CSV file and place values into word bookmarks Windows 7 64bit Trying to read CSV file and place values into word bookmarks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Philip1 View Post
I suppose I was trying to make this as easy to do as possible for the document I'm creating. quite surprisingly i've encountered people in admin roles who don't know how to use mail merge and constantly finding people get bored with the process quickly if they have to click more than twice.
Once set up, using a mailmerge is a trivial undertaking. And, if you want, a macro could be used to make it so simple that all the person using it has to do is open the document. Of course, that also means they get no opportunity to exercise some control over the process...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 10-27-2016, 12:37 AM
Philip1 Philip1 is offline Trying to read CSV file and place values into word bookmarks Windows 7 64bit Trying to read CSV file and place values into word bookmarks Office 2010 64bit
Novice
Trying to read CSV file and place values into word bookmarks
 
Join Date: Oct 2016
Posts: 3
Philip1 is on a distinguished road
Default

I think if I used this method, given what i'm trying to do with the document i.e. split fields and other things this should give me more control.

Okay so I'm presuming the ODBC driver is the software it needs to actually perform the functions in the code? I'm still having problems running the code successfullly as it tells me there's no default driver set or installed.

Is it due to a line of code I can remove to get this to work?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste from one page to another doesnt place in the same place MrShhh Visio 0 09-21-2016 05:47 AM
Trying to read CSV file and place values into word bookmarks Form updating Bookmarks - writes to the bookmarks multiple times PeterPlys Word VBA 13 01-14-2015 06:41 AM
Trying to read CSV file and place values into word bookmarks Read only file NienkeG Word 2 08-03-2014 03:57 PM
Trying to read CSV file and place values into word bookmarks editing a number/text at one place and changes taking place wherever it appears anurag.butoliya Word 1 06-14-2014 06:27 PM
Trying to read CSV file and place values into word bookmarks Why the "day" is not inserted as we read values from Excel to Word? Jamal NUMAN Word 8 09-04-2011 07:48 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft