Look up UserForm Caption Variable in CSV to get other values

Question

I want a Word Macro that will insert dynamic signatures (up to three) and have never done any VBA before (more of a Powershell guy...).

To do that, I've created a UserForm in the "developer" interface with 3 frames and plenty of radio buttons containing as caption the names of the people that will be able to sign.

As there are 60 peoples (or more) that will be able to sign, I have thought about having a CSV file with them containing their "Name, Department, Function).

My issue is I have no clue on how to read a CSV, compare it with my caption (let's say Caption = "Jon Swiss") and initialize the variable for three other values.

I have tagged my document with some bookmarks (that works with the caption only).

I don't know if you need the script I have done (nothing about the CSV - sorry...)

Private Sub RHBtn_Accepter_Click_Click()
'Initialisation
Dim i As Integer
'***
'       Signature 1
'*** 
'Pour chaque valeur possible
  For i = 1 To Me.Controls.Count
    'Si i plus petit que le nombre de valeur définie
    If i < Me.Controls.Count Then
      Set MyControl = Me.Controls.Item(i)
      'Si control dispose du tag RHSign1
      If MyControl.Tag = "RHSign1" Then
         'Si une valeur est activée
         If MyControl.Value = True Then
            'Mise en variable du nom en Sign1
            RHSignature1 = MyControl.Caption
            Exit For
         End If
      End If
    End If
  Next i

'***
'       Gettings CSV Value
'***

'***
'       Tagging the bookmarks
'***
           If ActiveDocument.Bookmarks.Exists("RhSignet1") = True Then
              Selection.GoTo What:=wdGoToBookmark, Name:="RhSignet1"
              Selection.TypeText Text:=RHSignature1
           End If
End Sub

CSV is a plain-text file format; you can use the `Open` statement and a loop with `Line Input` statements to read/parse it line by line. See if [this answer](https://stackoverflow.com/a/11528932/1188513) helps. — Mathieu Guindon, Apr 10 '19 at 15:08
With VBA is better to think of CSV as an excel file. Open it with VBA as a `new Excel.Application object`. Then you can do your search on the excel file for the caption. That's the first approach I personally would try. Edit: The comment above states the truth about CSV, it's a plain text. However with VBA you can easily open it on Excel and work with it much simpler in my opinion, this is why I said its best to think of it as an excel file when using VBA. — Ricardo A, Apr 10 '19 at 15:10
Rather than CSV you might consider XML. "Looking up" can be done more "reasonably" with XML since an XML parser provides such tools. And using XML won't involve invoking another application (Excel, as suggested in another comment). Or does your experience with PowerShell involve any kind of data querying? INI files are another possibility... No matter which approach you decide on, you will need to do some basic research... — Cindy Meister, Apr 10 '19 at 17:17
As was previous stated, "CSV is a plain-text file". There is no value in opening such a file in Excel unless you're doing something Excel-specific with it. To extract a particular record, or group of records, you could use ADODB, or simply open it in Word and use Find. — macropod, Apr 11 '19 at 07:01

score 0 · Accepted Answer · answered Apr 12 '19 at 06:11

Thank you for your help.

I found a way with CSV.

  'Répertoire
directory = "H:\@SCRIPTS\VBA_MacroWord_DigitRH\"
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"";"
strSQL = "SELECT * FROM " & FileName
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst

Do
   col1 = rs("Nom")
   col2 = rs("Fonction")
   col3 = rs("DPT")
   col4 = rs("Signature")
   rs.MoveNext

   'Si une valeur a été trouvée et qu'elle corresponds, définition des valeurs
   If col1 = RHSignature2 Then
   MsgBox "Signature2"
   MsgBox col1
   MsgBox col2
   MsgBox col3
   MsgBox col4

Seems to be working. Maybe there is a better way. But thanks to everyone that took some time that took the time to read me. Have a nice week, Jon

Look up UserForm Caption Variable in CSV to get other values

1 Answers1