excelvbastringappendquotation-marks

Sending a string that contains quotations to a file with VBA


I am trying to use VBA build a .jet file, but when I try to append, two possible problems appear. Either it includes all double quotes including the double double quotes like you would normally do in, say, a msgbox, or the string wont work if i remove the double double quotes because the first instance of quotes terminates the string. An example of my code is below (note, the commented/indented areas in the main sub are various possibilities I have tried but without success:

Sheets("Sheet1").Range("A1").Select
Dim MyStr As String
  'MyStr = "{" & Chr(34) & "myid" & Chr(34) & ":345," & Chr(34) & "content" & Chr(34) & ":["
  'MyStr = "{""myid"":345,""content"":["

  'appendToFile ("{""myid"":345,""content"":[")
  'appendToFile (MyStr)
End Sub


Sub appendToFile(MyStr As String)
Dim fileName As String
 fileName = "MyFile.jet"
Open Application.ActiveWorkbook.Path & "\" & fileName For Append As #1
    Write #1, MyStr
Close #1
End Sub

Solution

  • If you want to avoid the extra quotes appearing in your .jet, you can append using the Print # statement, and not the Write # statement.

    Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file.

    For example, this code:

    Option Explicit
    Sub ject()
    Dim MyStr As String
      
      MyStr = "{" & Chr(34) & "myid" & Chr(34) & ":345," & Chr(34) & "content" & Chr(34) & ":["
      
      appendToFile (MyStr)
    End Sub
    
    
    Sub appendToFile(MyStr As String)
    Dim fileName As String
     fileName = "MyFile.jet"
    Open Application.ActiveWorkbook.path & "\" & fileName For Append As #1
        Print #1, MyStr 
    Close #1
    End Sub
    

    will result in:

    {"myid":345,"content":[
    

    when opening the .jet file with a text editor.

    Is that what you want?