After publishing my article on determining if a file was a valid XML file:
VBA Function to Determine if a File is a Valid XML File
I was quickly reviewing a couple forums to look for interesting questions, ideas for blog posts and came across the following question on UA: and thought this could make for a good quick post.
I thought I’d do similarly for JSON files as they have become quite common too.
Now, like everything else in VBA, there are a multitude of possible approaches to this.
Like XML files, JSON files do not possess a file signature also referred to as ‘magic number’. The bigger issue with JSON files however is that unlike their XML counterpart, they do not include any declaration line to easily validate.
ScriptControl
My preferred solution is to merely use the ScriptControl. You see, JS provides us the ability to validate JSON files quite easily. In modern JS we’d use JSON.parse(), but sadly the ScriptControl is old and doesn’t offer that to us, but we do still have Eval() which will work equally for our needs. Hence, we can do something like:
'---------------------------------------------------------------------------------------
' Procedure : JSON_ISValidFile
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Validates a file to determine if it is a valid JSON file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft Script Control 1.0
' Dependencies: ReadFileAsText()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename to validate
'
' Output Variables:
' ~~~~~~~~~~~~~~~~~
' Boolean: True -> Is a JSON file
' False -> Is NOT a JSON file
'
' Usage:
' ~~~~~~
' JSON_ISValidFile("C:\Users\Daniel\Downloads\note.json")
' Returns -> True/False
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2025-04-21
'---------------------------------------------------------------------------------------
Function JSON_ISValidFile(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler
#Const ScriptControl_EarlyBind = False 'Should normally be in the Module header
#If ScriptControl_EarlyBind = True Then
Dim oSC As MSScriptControl.ScriptControl
Set oSC = New ScriptControl
#Else
Static oSC As Object
Set oSC = CreateObject("ScriptControl")
#End If
Dim sOutput 'As Boolean
Dim sJSON As String
Dim sEscapedJSON As String
Dim sJS As String
Dim obj As Object
' Minimal file validation
If Dir(sFile) = "" Then Exit Function ' File does not exist
' Could check if 1st character is { if we wanted to
' Get the content of the file in question
sJSON = ReadFileAsText(sFile)
If Not oSC Is Nothing Then
With oSC
.Language = "JScript"
Set obj = oSC.Eval("(" & sJSON & ")")
JSON_ISValidFile = True
End With
End If
Error_Handler_Exit:
On Error Resume Next
Set obj = Nothing
Set oSC = Nothing
Exit Function
Error_Handler:
Select Case Err.Number
Case 1002, 1006, 1028 ' Syntax error / Expected identifier, string or number
' There may be more err.number to add here as I've just used this in a limited capacity
Case Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: JSON_ISValidFile" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End Select
Resume Error_Handler_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : ADODB_ReadFileAsText
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : This code opens a UTF-8 encoded text file and reads its entire contents
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft ActiveX Data Objects x.x Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename to read
'
' Usage:
' ~~~~~~
' ADODB_ReadFileAsText("C:\Users\Dev\Desktop\Booking.json")
' Returns -> the content of the file in question
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2012-05-14
' 2 2025-04-21 Initial Public Release
'---------------------------------------------------------------------------------------
Function ADODB_ReadFileAsText(ByVal sFile As String) As String
On Error GoTo Error_Handler
#Const ADODBStream_EarlyBind = False 'Should normally be in the Module header
#If ADODBStream_EarlyBind = True Then
Dim oADODBStream As ADODB.stream
Set oADODBStream = New ADODB.stream
#Else
Static oADODBStream As Object
Set oADODBStream = CreateObject("ADODB.Stream")
Const adTypeText = 2
#End If
With oADODBStream
.Type = adTypeText
.Charset = "utf-8" 'You may need to change this depending on the file
.Open
.LoadFromFile sFile
ADODB_ReadFileAsText = .ReadText
.Close
End With
Error_Handler_Exit:
On Error Resume Next
Set oADODBStream = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: ADODB_ReadFileAsText" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Now, the major drawback here is the fact that the ScriptControl is only available on 32-bit installations!!!
Possible Other Approaches
Just like with XML files, there are other possible techniques we could use such as checking the File Extension.
File Extension Validation
Just like I explained in my XML article, extensions are no guarantee of the actual file content, however if you are in a controlled environment and believe that you can simply rely on the file extension to guarantee the JSON content of a file then you might be able to get away with simply doing something like:
'---------------------------------------------------------------------------------------
' Procedure : File_HasExtension
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Validate whether a file has the specified extension
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename to validate
' sFileExtension : File extension to check for
' Recommend including the . in the validation
'
' Output Variables:
' ~~~~~~~~~~~~~~~~~
' Boolean: True -> Has the specified file extension
' False -> Does NOT have the specified file extension
'
' Usage:
' ~~~~~~
' File_HasExtension("C:\Users\Daniel\Downloads\note.xMl", ".xml")
' Returns -> True
'
' File_HasExtension("C:\Temp\1.txtl", ".xml")
' Returns -> False
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2005-07-13
' 2 2025-04-22 Update Function Header
' Update Error Handler
'---------------------------------------------------------------------------------------
Function File_HasExtension(ByVal sFile As String, _
ByVal sFileExtension As String) As Boolean
On Error GoTo Error_Handler
If Right(sFile, Len(sFileExtension)) = sFileExtension Then File_HasExtension = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: File_HasExtension" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Where you can then simply do:
If Not File_HasExtension("C:\Users\Daniel\Downloads\booking-2390.json", ".json") Then
' Not a JSON file extension
Exit Sub/Function
End If
' Continue because the JSON file had the proper extension
Using the Modern Web Browser Control
Another option would be to use (hidden if you want) a MWBC to use JSON.parse() through it.
Use a JSON Library
Another great option would be to use a JSON library such as:
GitHub - BentBrains/VBA-JSON: JSON conversion and parsing for VBA (Windows and Mac Excel, Access, and other Office applications). It grew out of the excellent project vba-json, with additions and improvements made to resolve bugs and improve performance (as part of VBA-Web). Tested in Windows Excel 2013 and Excel for Mac 2011, but should apply to 2007+. For Windows-only support, include a reference to “Microsoft Scripting Runtime” For Mac and Windows support, include VBA-Dictionary
JSON conversion and parsing for VBA (Windows and Mac Excel, Access, and other Office applications). It grew out of the excellent project vba-json, with additions and improvements made to resolve bu…
Then you can use the built-in functions to test the JSON string. To do so you might create a simple function such as:
'---------------------------------------------------------------------------------------
' Procedure : JSON_JsonConverter_ISValidFile
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Validate whether the file is a properly formatted JSON file or not
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Dependencies: ReadFileAsText()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename to validate
'
' Output Variables:
' ~~~~~~~~~~~~~~~~~
' Boolean: True -> Is a JSON file
' False -> Is NOT a JSON file
'
' Usage:
' ~~~~~~
' ? JSON_JsonConverter_ISValidFile("C:\Users\Daniel\Downloads\json02.json")
' Returns -> True/False
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2025-04-21
'---------------------------------------------------------------------------------------
Function JSON_JsonConverter_ISValidFile(ByVal sFile As String) As Boolean
On Error GoTo Error_Handler
Dim sFileContent As String
Dim oJson As Object
' Retrieve the file's contents
sFileContent = ReadFileAsText(sFile)
' Try parsing the JSON
Set oJson = JsonConverter.ParseJson(sFileContent)
JSON_JsonConverter_ISValidFile = True
Error_Handler_Exit:
On Error Resume Next
Set oJson = Nothing
Exit Function
Error_Handler:
Resume Error_Handler_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : ADODB_ReadFileAsText
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : This code opens a UTF-8 encoded text file and reads its entire contents
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft ActiveX Data Objects x.x Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path and filename to read
'
' Usage:
' ~~~~~~
' ADODB_ReadFileAsText("C:\Users\Dev\Desktop\Booking.json")
' Returns -> the content of the file in question
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2012-05-14
' 2 2025-04-21 Initial Public Release
'---------------------------------------------------------------------------------------
Function ADODB_ReadFileAsText(ByVal sFile As String) As String
On Error GoTo Error_Handler
#Const ADODBStream_EarlyBind = False 'Should normally be in the Module header
#If ADODBStream_EarlyBind = True Then
Dim oADODBStream As ADODB.stream
Set oADODBStream = New ADODB.stream
#Else
Static oADODBStream As Object
Set oADODBStream = CreateObject("ADODB.Stream")
Const adTypeText = 2
#End If
With oADODBStream
.Type = adTypeText
.Charset = "utf-8" 'You may need to change this depending on the file
.Open
.LoadFromFile sFile
ADODB_ReadFileAsText = .ReadText
.Close
End With
Error_Handler_Exit:
On Error Resume Next
Set oADODBStream = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: ADODB_ReadFileAsText" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Then, you can simply use the function in your code like:
If Not JSON_JsonConverter_ISValidFile ("C:\Users\Daniel\Downloads\booking-2390.json") Then
' Not a valid JSON file
Exit Sub/Function
End If
' Continue because the JSON file passed validation
Also, if you are looking to validate a file to see if it is in a valid JSON format, you probably want to actually work with it further and such a Library as the JsonConverter will greatly facilitate such work. So this is a great option as it facilitates much more than just validating the file.
So there you have it multiple possible approaches you can implement to validate if a file is truly a JSON file, or not. Now, all you have to do is pick the one that makes you happy and suits your development needs.