Sending a string that contains quotations to a file with VBA

Question

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

I don't see where you are showing your desired result. Or what happened with the append operation when you tried your various attempts. — Ron Rosenfeld
– Ron Rosenfeld, Commented Aug 12, 2020 at 21:48
ok, so for example, it will say this in MyFile.jet when i append: "{""myid"":345,""content"":[" — Brandon Redmond
– Brandon Redmond, Commented Aug 13, 2020 at 0:32

Traveler · Accepted Answer · 2023-09-07 22:38:25Z

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, 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?

edited Sep 7, 2023 at 22:38

Traveler

2674 silver badges13 bronze badges

answered Aug 13, 2020 at 1:04

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brandon Redmond Over a year ago

Yes! That is exactly what I needed. It works! Thank you very much.

Brandon Redmond Over a year ago

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"]},

Ron Rosenfeld Over a year ago

@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.

Collectives™ on Stack Overflow

Sending a string that contains quotations to a file with VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related