Convert Excel to JSON using VBA

2220 pts.
Tags:
JSON
Microsoft Excel
VBA
I need to write an Excel macro which will convert the excel workbook into a JSON file. The excel will have multiple sheets, each sheet will have a tabular form of data (in rows and columns). One of the column will have hyperlinked data and the macro should extract and explicitly list the hyperlink as well.

Software/Hardware used:
Microsoft Technologies | VB

Answer Wiki

Thanks. We'll let you know when a new response is added.

Just Google the problem. There are a lot of converters out there that will do this for you. If you need to do this your self or have problems or issues come back with more details please.

TRy saving your Excel file as a CSV the check out this link

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Subhendu Sen
    Have you written some code for that? It is not possible to write the code for you. If you wrote something, you can come here with errors if any. For a hint, you may need to convert Excel to JSON, and store that in a variable. To do that, you need to dimension a few variables to use later in the same program.
    119,590 pointsBadges:
    report
  • 6r
    Hi,

    Below code is working for 100 rows and 50 columns, but i want to make it to work n number of rows and n number of columns means do not want to hard code it should take as per data in excel sheet so how to customize it also it should work for all the sheets in given excel not only on the first default excel sheet it should cover all the sheets in the excel.
    Function toJSON(rangeToParse As Range, parseAsArrays As Boolean) As String
        Dim rowCounter As Integer
        Dim columnCounter As Integer
        Dim parsedData As String: parsedData = "["
        Dim temp As String

        If parseAsArrays Then ' Check to see if we need to make our JSON an array; if not, we'll make it an object
            For rowCounter = 1 To rangeToParse.Rows.Count ' Loop through each row
                temp = "" ' Reset temp's value

                For columnCounter = 1 To rangeToParse.Columns.Count ' Loop through each column
                    temp = temp & """" & rangeToParse.Cells(rowCounter, columnCounter) & """" & ","
                Next

                temp = "[" & Left(temp, Len(temp) - 1) & "]," ' Remove extra comma from after last object
                parsedData = parsedData & temp ' Add temp to the data we've already parsed
            Next
        Else
            For rowCounter = 2 To rangeToParse.Rows.Count ' Loop through each row starting with the second row so we don't include the header
                temp = "" ' Reset temp's value

                For columnCounter = 1 To rangeToParse.Columns.Count ' Loop through each column
                    temp = temp & """" & rangeToParse.Cells(1, columnCounter) & """" & ":" & """" & rangeToParse.Cells(rowCounter, columnCounter) & """" & ","
                Next

                temp = "{" & Left(temp, Len(temp) - 1) & "}," ' Remove extra comma from after last object
                parsedData = parsedData & temp ' Add temp to the data we've already parsed
            Next
        End If

        parsedData = Left(parsedData, Len(parsedData) - 1) & "]" ' Remove extra comma and add the closing bracket for the JSON array
        toJSON = parsedData ' Return the JSON data
    End Function

    Function getValuesRange(sheet As String) As Range
        ' Row variables
        Dim usedRows As Integer: usedRows = 0
        Dim rowCounter As Integer: rowCounter = 1
        Dim rowsToCount As Integer: rowsToCount = 100
        ' Column variables
        Dim usedColumns As Integer: usedColumns = 0
        Dim columnCounter As Integer: columnCounter = 1
        Dim columnsToCount As Integer: columnsToCount = 50

        Do While rowCounter <= rowsToCount ' Loop through each row
            Do While columnCounter <= columnsToCount ' Loop through each column
                If Worksheets(sheet).Cells(rowCounter, columnCounter) <> "" Then ' Check to see if the cell has a value
                    usedRows = rowCounter ' Since the current row has a cell with a value in it, set usedRows to the current row

                    If columnCounter > usedColumns Then
                        usedColumns = columnCounter ' If the current column is greater than usedColumns, set usedColumns to the current column
                    End If

                    If usedRows = rowsToCount Then
                        rowsToCount = rowsToCount + 100 ' If the value of usedRows reaches the rowsToCount limit, then extend the rowsToCount limit by 100
                    End If

                    If usedColumns = columnsToCount Then
                        columnsToCount = columnsToCount + 50 ' If the value of usedColumns reaches the columnsToCount limit, then extend the columnsToCount limit by 100
                    End If
                End If
                columnCounter = columnCounter + 1 ' Increment columnCounter
            Loop

            rowCounter = rowCounter + 1 ' Increment rowCounter
            columnCounter = 1 ' Reset the columnCounter to 1 so we're always checking the first column every time we loop
        Loop

        Set getValuesRange = Worksheets(sheet).Range("a1", Worksheets(sheet).Cells(usedRows, usedColumns).Address) ' Return the range of cells that have values
    End Function

    Sub parseData()
        Worksheets("Sheet2").Range("b1") = toJSON(getValuesRange("Sheet1"), False) ' Set cell B1's value to our JSON data
    End Sub


    2,220 pointsBadges:
    report
  • 6r
    What if need to parse data of all the work sheets in a excel and The number of sheets and number of columns in each sheet might vary and it should be written  into an output file as file name.json (output file containing data from all the sheets and Note that Column A (Name) of every sheet has hyperlinked data. This should get parsed as "Name": "filename", "URL": "file complete address"
    2,220 pointsBadges:
    report
  • 6r
    so any idea what code changes in above pasted code to complete this requirement?

    2,220 pointsBadges:
    report
  • 6r
    VBA M acro experts could you please advise on this soon.
    2,220 pointsBadges:
    report
  • 6r
    any other forum's link if this is not the right forum to ask such queries???
    2,220 pointsBadges:
    report
  • 6r
    any updates please???
    2,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: