CODEWITHSUNDEEP
jsonexcelspecial-charactershidden-charactersvba
Vance Noonan
Vance Noonan

Reputation: 11

Hidden Characters?

I've written a set of modules that parses data into .json files for upload into ArcGIS. Everything works great except that when I try to upload the .json files, it crashes and says there are unexpected characters. I don't see any characters when I open the .json in NotePad. I've tried simply re-saving the file. I've written the data to .txt files and changing the extension. I've cut the data out, saved the empty file, and pasted it back in.

The only thing that seems to work is: open the target .json file, copy the data into a new.txt file, close the target .json file, and then save the new.txt file as the target .json file. This fixes the issue, but it's very time consuming because there are 24 .json files I have to do this with.

Here's my code:

Sub WriteData()
Dim filepath As String
Dim celldata As String
Dim lastrow As String
Dim lastcol As String
Dim k As Long
Dim temp As Worksheet
Dim ouput As Worksheet
Dim fileout As Object
Dim fso As Object
'
Set temp = ActiveWorkbook.Sheets("Temp")
Set output = ActiveWorkbook.Sheets("Output Sheet")
Set fso = CreateObject("Scripting.FileSystemObject")
For i = 15 To 22
    For j = 3 To 5
        k = 3 * (i - 15) + j - 2
        filepath = Application.ActiveWorkbook.Path & "\JSON Files\" & temp.Cells(i, 1) & " " & temp.Cells(15, j) & ".json"
        Set fileout = fso.CreateTextFile(filepath, True, True)
        For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
            celldata = output.Cells(l, k)
            fileout.write celldata
        Next l
        fileout.Close
    Next j
Next i
Sheets("Output Sheet").Select
ActiveWorkbook.Save
End Sub

Upvotes: 0

Views: 769

Answers (1)

Vance Noonan
Vance Noonan

Reputation: 11

I fixed it! There must be something going on when you use fileout.write. I changed it out to a simple Print command and it works! Here are the changes (I left the old lines in for reference):

k = 3 * (i - 15) + j - 2
filepath = Application.ActiveWorkbook.Path & "\JSON Files\" & temp.Cells(i, 1) & " " & temp.Cells(15, j) & ".json"
'Set fileout = fso.CreateTextFile(filepath, True, True)
Open filepath For Output As #1
For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
    celldata = output.Cells(l, k)
    'fileout.write celldata
    Print #1, celldata
Next l
'fileout.Close
Close #1

Upvotes: 1

Related Questions