1

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
Brandon Redmond
  • 39
  • 8

1 Answers1

1

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,
Close #1
End Sub

will result in:

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

when opening the .jet file with a text editor.

Is that what you want?

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Yes! That is exactly what I needed. It works! Thank you very much. – Brandon Redmond Aug 13 '20 at 22:43
  • any ideas on how to delete the last character in a file? for example, I want to delete the comma in a textfile that ends in ...potato"]}, – Brandon Redmond Aug 14 '20 at 00:08
  • @BrandonRedmond There are any number of methods to do that, but not something that can be answered in a comment. If you are having problems, post it as a new question. Include your efforts at accomplishing it, along with enough usable data that someone can reproduce any problem you ran into. Might help to take a look at [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Ron Rosenfeld Aug 14 '20 at 01:20