Hidden Characters?

[vnoonan]

I am my wits' end about this issue. The following code steps through the Output Sheet and writes 24 columns of data into 24 separate files. The file names are in a 3x8 grid on the Temp sheet. The data is being output into .json files for upload into ArcGIS. The code works beautifully for me with one exception. If I try to import the files as written, ArcGIS says there's an unexpected character in the file. I don't see any extra characters when I open the file in Notepad, the output looks fine to me. I've tried everything I can think of to delete hidden characters and the only thing that works is: open a new.txt file, copy all the data from the target file, close the target file, and save the new.txt file as the target .json file, overwriting the old file.

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

 

[Richard U]

It sound like you're getting non-printing ASCII chars Anything with an ASCII value under 32 might do that.

http://www.asciitable.com/

you may want to run a quick scan on your file to see if there are any non-printing characters there.

something like....

For each mycell in range(myrange)
   for i = 1 to len(mycell.value)
       if asc(mid(mycell.value, i, 1)) <32 then 
          msgbox "Cell " & mycell.address & " has non-printing characters")
          exit for
       end if
    next i
next mycell

 

[vnoonan]

Thanks for the quick reply! I spliced this code in as shown so that every time it grabs a new cell it runs the check for ASCII characters < 32. It ran and did not pop up a message box.

        For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
            celldata = output.Cells(l, k)
            For m = 1 To Len(output.Cells(l, k))
                If Asc(Mid(output.Cells(l, k), m, 1)) < 32 Then
                    MsgBox "Cell " & output.Cells(l, k).Address & " has non-printing characters"
                    Exit For
                End If
            Next m
            fileout.write celldata
        Next l

 

[vnoonan]

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):

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)
        Open filepath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
            celldata = output.Cells(l, k)
            'fileout.write celldata
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , celldata
        Next l
        'fileout.Close
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Next j
Next I