locked
Reference to string in VBA code RRS feed

  • Question

  • I am trying to update a csv file programatically with the following code:

         

    Private Sub Command10_Click()

      Dim fName As String, fso As Object, fsoFile As Object, txt As Variant
      Dim strFirstName As String

      Set fso = CreateObject("Scripting.FileSystemObject")
      strFirstName = "Xavier"

      Set fsoFile = fso.OpenTextFile("C:\Users\stand\Documents\TestCSVFile.txt", 1)
      txt = fsoFile.ReadAll
      fsoFile.Close

        txt = Split(txt, vbNewLine)
        txt(7 - 1) = "0,""^clmntNm\.frs"",[strFirstName],""secure.ssa.gov"""
        txt(8 - 1) = "0,""^clmntNm\.mdl$"",""My"",""secure.ssa.gov"""



        Set fsoFile = fso.OpenTextFile("C:\Users\stand\Documents\TestCSVFile.txt", 2)
        fsoFile.Write Join(txt, vbNewLine)
        fsoFile.Close
    End Sub

    When I go to the file I find that line 7 reads as follows:  ,"^clmntNm\.frs",[strFirstName],"secure.ssa.gov"

    I had wanted, of course, for the name "Xavier" to appear because that is the definition of strgFirstName.  What am I doing wrong??

    Saturday, July 9, 2016 4:40 PM

All replies

  • There is no string insertion feature in VBA unlike C# or some other languages.  You'll need to build the string.

    txt(7 - 1) = "0,""^clmntNm\.frs""," & """Xavier""" & ",""secure.ssa.gov"""

    or

    txt(7 - 1) = "0,""^clmntNm\.frs""," & """" & strFirstName & """" & ",""secure.ssa.gov"""

    • Edited by mogulman52 Saturday, July 9, 2016 5:14 PM
    Saturday, July 9, 2016 5:09 PM