+ Reply to Thread
Results 1 to 5 of 5

Vba convert excel to json with additional text

Hybrid View

  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.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Vba convert excel to json with additional text

    It's not clear what you want to add at before/after/end but you should be able to do it here,
        fileStream.WriteText "["
    and here.
        fileStream.WriteText "]"
    For example.
        fileStream.WriteText """var data = { ""data"": 1130,""id"": "" ["
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    07-09-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 64bit
    Posts
    48

    Re: Vba convert excel to json with additional text

    Hi Norie,

    Thanks for enlightening me, you are right your suggestion is working but there is an extra "" , when i tried to remove them got this error "compile error, expected: end of statement", any idea how to fix this?

    Output file now:
    "var data = { "data": 1130,"id": "


    What i want is below, without extra "":
    var data = { "data": 1130,"id":

    Thanks

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Vba convert excel to json with additional text

    Perhaps.
    fileStream.WriteText "var data = { ""data"": 1130,""id"": ["

  5. #5
    Registered User
    Join Date
    07-09-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 64bit
    Posts
    48

    Re: Vba convert excel to json with additional text

    Norie,
    Yes your code is working perfectly, once again thank you so much, you are awesome!

+ Reply to Thread

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