How to display letters with french accents: é and others like it?

Question

My code does:

• Search in a Form for checked name (button radio)
• Search for that name in a CSV file and extract some values (Name, Title, function and link to pictures)
• Insert those values into Word document (for reference value or signature value)

It does work for standard names like John Smith but when I have special letters in the name like François Bénét, it doesn't. Instead of é, é appears in the documents.

If the name has some other special characters it also does not find anything in the CSV. But for that I could find a workaround.

Have tried with other CSV format like UTF 8. But in that case I get an error 3265.

'Répertoire
directory = "W:\Tous\DigitRHTemp\"
FileName = "BaseSignatureTest.csv"
'Ouverture de la connexion CSV
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & directory & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited;CharacterSet=850"";"
strSQL = "SELECT * FROM " & FileName
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst

'Passer au travers des colonnes
Do
   'Mise en variable des valeurs
   col1 = rs("Nom")
   col2 = rs("Fonction")
   col3 = rs("DPT")
   col4 = rs("Titre")
   col5 = rs("Signature")
   rs.MoveNext

   'Si une valeur a été trouvée et qu'elle corresponds, définition des valeurs
   If col1 = RHREF Then

    '***
    '--- Populer les signets Signatures 1
    '***
        'Test si signet exist dans le document
        If ActiveDocument.Bookmarks.Exists("RHRefInt") = True Then
            'Si oui - Position sur le signet
            Selection.GoTo What:=wdGoToBookmark, Name:="RHRefInt"
            'Insére le text
            Selection.TypeText Text:=col4
        End If
        If ActiveDocument.Bookmarks.Exists("RHRefNom") = True Then
            Selection.GoTo What:=wdGoToBookmark, Name:="RHRefNom"
            Selection.TypeText Text:=col1
        End If

   End If

Loop Until rs.EOF

Is there some parameter to add to have the proper format for French characters (é, à, è ,ç, etc.)?

Answer 1

I've managed to do a big part (thank you Mathieu Guindon).

So the part that shows the department, title and name with special characters work fine (it is even quicker than the previous version of my code).

I've used the ADODB.Stream as suggested ans open the csv by forcing it in UTF-8

'Définition de l'objet
Dim objStream, strData
'Création d'un objet ADODB Stream
Set objStream = CreateObject("ADODB.Stream")
'Définition format en Utf-8
objStream.Charset = "utf-8"
'Ouverture de l'objet
objStream.Open
'Load du fichier
objStream.LoadFromFile ("W:\Tous\DigitRHTemp\BaseSignatureTest.csv")
'Définition du ligne par ligne
objStream.LineSeparator = 10
'Ouvrir chaque ligne jusqu'à la dernière ligne du fichier
Do Until objStream.EOS
'Défintion du texte à lire (ligne complète)
strData = objStream.ReadText(-2)

'Définition pour la partie valeur par valeur - sépare la ligne en valeur
Dim LArray() As String
'Sépare la valeur en se basant sur la virgule ","
LArray = Split(strData, ",")

   'Si une valeur a été trouvée et qu'elle corresponds, définition des valeurs
   If LArray(0) = RHSignature1 Then

    '***
    '--- Populer les signets Signatures 1
    '***
        'Test si signet exist dans le document
        If ActiveDocument.Bookmarks.Exists("RHSign1Nom") = True Then
            'Si oui - Position sur le signet
            Selection.GoTo What:=wdGoToBookmark, Name:="RHSign1Nom"
            'Insére le text
            Selection.TypeText Text:=LArray(0)
        End If
        If ActiveDocument.Bookmarks.Exists("RHSign1DPT") = True Then
            Selection.GoTo What:=wdGoToBookmark, Name:="RHSign1DPT"
            Selection.TypeText Text:=LArray(1)
        End If
        If ActiveDocument.Bookmarks.Exists("RHSign1Fonction") = True Then
            Selection.GoTo What:=wdGoToBookmark, Name:="RHSign1Fonction"
            Selection.TypeText Text:=LArray(2)
        End If    
   End If
'Fermeture de la boucle
Loop
'fermeture du objstream
objStream.Close