CODEWITHSUNDEEP
vba
Jonathan E
Jonathan E

Reputation: 54

excel vba button to add text to a file

I am creating a spreadsheet with a button which creates a new file, then adds text to the file. I can't seem to get it working properly. (It is actually json, but for my purposes it is just raw text being inserted into a file.)

This is the code i have so far. It creates a new file and puts some text in it, I can't get the formatting of the text to come out how I want it. I don't really understand why the quotes appear in the output.

Private Sub GENERATE_ORDER_BUTTON_Click()

Dim path As String
path = "H:\order.json"

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createtextfile(path, True)
a.WriteLine ("{")
a.Close

Dim outputstring As String
outputstring = "test" & "anothertest"

Open path For Append As #1
Write #1, outputstring
Close #1



End Sub

What i am finding is the output comes out like this:

{
"testanothertest"

The ultimate goal is to create a file with the indentations, quotes etc exactly as it is below.

{
  "BASE_CE": [
    {
      "CE_HOSTNAME": "TESTCE-DCNCE-01",
      "NEW": "NEW",

Upvotes: 1

Views: 135

Answers (1)

Rabus
Rabus

Reputation: 81

How to create and write to a txt file using VBA

I think this could maybe help you. Cause if you use print,it doesn't appear to be formated like you dont want it to be...

    Private Sub GENERATE_ORDER_BUTTON_Click()

Dim path As String
path = "J:\order.json"

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createtextfile(path, True)
a.WriteLine ("{")
a.Close

Dim outputstring As String
outputstring = "test" & "anothertest"

Open path For Append As #1
Print #1, outputstring
Close #1



End Sub

Upvotes: 1

Related Questions