[SOLVED] Sending a string that contains quotations to a file with VBA

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?