Results 1 to 5 of 5

Vba convert excel to json with additional text

Threaded View

chhiew Vba convert excel to json... 06-24-2019, 07:26 PM
Norie Re: Vba convert excel to json... 06-24-2019, 07:50 PM
chhiew Re: Vba convert excel to json... 06-24-2019, 08:37 PM
Norie Re: Vba convert excel to json... 06-24-2019, 09:37 PM
chhiew Re: Vba convert excel to json... 06-24-2019, 10:14 PM
  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 64bit
    Posts
    48

    Vba convert excel to json with additional text

    Everytime i convert excel to json, I want to add some text before and after the json output file with below vba code, how can i achieve it ?

    Eg. Add "var data = { "data": 1130,"id": " ..............and ..................."}"......before and after json file.
    Public Sub tojson()
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        jsonFilename = fso.GetBaseName(ActiveWorkbook.Name) & ".json"
        fullFilePath = Application.ActiveWorkbook.Path & "\" & jsonFilename
    
        Dim fileStream As Object
        Set fileStream = CreateObject("ADODB.Stream")
        fileStream.Type = 2 'Specify stream type - we want To save text/string data.
        fileStream.Charset = "utf-8" 'Specify charset For the source text data.
        fileStream.Open 'Open the stream And write binary data To the object
    
        Dim wkb As Workbook
        Set wkb = ThisWorkbook
    
        Dim wks As Worksheet
        Set wks = wkb.Sheets(1)
    
        lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
        lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
        Dim titles() As String
        ReDim titles(lcolumn)
        For i = 1 To lcolumn
            titles(i) = wks.Cells(1, i)
        Next i
        fileStream.WriteText "["
        dq = """"
        escapedDq = "\"""
        For j = 2 To lrow
            For i = 1 To lcolumn
                If i = 1 Then
                    fileStream.WriteText "{"
                End If
                cellvalue = Replace(wks.Cells(j, i), dq, escapedDq)
                fileStream.WriteText dq & titles(i) & dq & ":" & dq & cellvalue & dq
                If i <> lcolumn Then
                    fileStream.WriteText ","
                End If
            Next i
            fileStream.WriteText "}"
            If j <> lrow Then
                fileStream.WriteText ","
            End If
        Next j
        fileStream.WriteText "]"
        fileStream.SaveToFile fullFilePath, 2 'Save binary data To disk
        a = MsgBox("Saved to " & fullFilePath, vbOKOnly)
    End Sub
    Last edited by chhiew; 06-24-2019 at 07:30 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert JSON Date to Excel Date
    By NeedForExcel in forum Excel General
    Replies: 6
    Last Post: 03-16-2018, 08:20 AM
  2. [SOLVED] Convert jscript JSON flattener to work with VBA ScriptControl
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2018, 11:49 AM
  3. Replies: 2
    Last Post: 09-28-2017, 10:40 AM
  4. Convert Object to JSON String
    By amartinez988 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-01-2016, 03:41 PM
  5. Excel VBA-JSON Example
    By manofcheese in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2016, 11:52 AM
  6. API Json to Excel Macro
    By ermengard in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2014, 05:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1