VBA generates a JSON file for Excel data tables _vba

ADODB. Stream to create a UTF-8+BOM encoded text file.

It then traverses the data area, formats the data, and outputs it.

Small data is OK, big data is not tested.

In addition, the text file created by using the FSO is encoded as Ansi,ajax parsing JSON does not parse correctly when garbled.

Sub Tojson () ' Creates UTF8 text File myrange = Worksheets ("Sheet1"). UsedRange ' selects data through a valid data area ' myrange = Activeworkbook.names (' Schoolinfo '). RefersToRange ' selects the data ' myrange = Range (Worksheets ("Sheet1") by a defined name. Range ("A1"). End (Xldown), Worksheets ("Sheet1"). Range ("A1").  End (xlToRight)) ' Selects total = UBound (myrange, 1) ' fetch row number Fields = UBound (myrange, 2) ' to get the number of columns in the maximum row maximum column of the header row Dim objstream As Object Set objstream = CreateObject ("ADODB.") Stream ") with objstream. Type = 2. Charset = "UTF-8". Open. WRITETEXT "{" "Total" ":" & Total & "," "Contents" ": [" For i = 2 to total.) WRITETEXT "{" For j = 1 to Fields. WriteText "" "& MyRange (1, j) &" ":" "" & Replace (MyRange (I, J), "" "", "" "" "" & "" "If J < > Fields Then. WriteText "," End If Next if i = Total Then. WriteText "}" Else. WriteText "}," End If Next. WritetExt "]}".
 SaveToFile Activeworkbook.fullname & ". JSON", 2 end and Set objstream = Nothing End Sub

Recently in writing a Web page, need to query from the background of ASP Web pages to the MySQL recordset returned to the foreground ASP page, we know Ajax is not able to return from the background database recordset to the front page.

Looking at a lot of data, for the time being, it's a good choice to convert the recordset to a JSON-formatted stream, and then to import the Weboffice control's Excel by the foreground VBA. After some thinking, the function process code is dedicated to everyone.

    Function Getjson (Rs)
    Dim JSON  
    Dim Returnstr Dim 
    I
    Dim onerecord   
    If Rs.eof=false and Rs.bof=false Then
    returnstr= "{" &CHR (& "Records" &CHR () & ": [" while rs.eof=false for
    
     i=0 to Rs.fields.count-1
      Onerecord=onerecord & Chr (a) & Rs.fields (i). Name & Chr (+) & ":" 
      Onerecord=onerecord & Chr (%) & Rs.fields (i). Value & Chr (+) & ","
     Next
     onerecord=left (Onerecord,instrrev (Onerecord, ",")-1)
     onerecord= Onerecord & "},"
     returnstr=returnstr  & Onerecord
     rs.movenext
    wend
    returnstr=left (Returnstr,instrrev (Returnstr, ",")-1)
    Returnstr=returnstr & "]}" end
    if 
    getjson=returnstr end   
  Function