如何通过Excel宏将单元格JSON内容导出为指定文件名的JSON文件？

Absolutely doable! That Word workaround is a pain, so let’s build a macro button that skips all that and exports your JSON directly from Excel to a properly formatted .json file—using the filename from your specified cell, no extra quotes included.

Why Those Extra Quotes Pop Up #

When you copy text directly from an Excel cell containing quotes, Excel automatically adds an outer set of quotes and escapes internal quotes by doubling them (turning " into ""). This is Excel’s default behavior for handling text with special characters during copy-paste, but it breaks your JSON. The good news? A macro reads the actual value of the cell, not the escaped copy-paste version, so we avoid this issue entirely.

The VBA Macro to Export Your JSON #

Here’s a ready-to-use macro that reads your generated JSON content, grabs the filename from your specified cell, and saves it as a valid .json file. Just adjust the cell references to match your sheet:

Sub ExportJSONtoFile()
    Dim jsonContent As String
    Dim fileName As String
    Dim savePath As Variant
    
    ' Update these ranges to match your worksheet
    ' Replace "Sheet1" with your sheet name, and cell addresses as needed
    jsonContent = ThisWorkbook.Sheets("Sheet1").Range("B1").Value ' Cell with your CONCAT-generated JSON
    fileName = ThisWorkbook.Sheets("Sheet1").Range("A1").Value ' Cell holding your desired filename
    
    ' Validate filename input
    If fileName = "" Then
        MsgBox "Oops! Please enter a filename in the specified cell first.", vbExclamation
        Exit Sub
    End If
    
    ' Add .json extension if missing
    If LCase(Right(fileName, 5)) <> ".json" Then
        fileName = fileName & ".json"
    End If
    
    ' Let you select the save location
    savePath = Application.GetSaveAsFilename( _
        InitialFileName:=fileName, _
        FileFilter:="JSON Files (*.json), *.json", _
        Title:="Save Your JSON File")
    
    ' Proceed if you didn't cancel the save dialog
    If savePath <> False Then
        ' Write JSON content to the file
        Dim fileSystemObj As Object
        Set fileSystemObj = CreateObject("Scripting.FileSystemObject")
        Dim textFile As Object
        ' Use True instead of False here if you need UTF-8 encoding for special characters
        Set textFile = fileSystemObj.CreateTextFile(savePath, True, False)
        textFile.Write jsonContent
        textFile.Close
        
        MsgBox "Success! Your JSON file is saved here:" & vbCrLf & savePath, vbInformation
    End If
End Sub

Key Macro Features: #

• No More Extra Quotes: By reading the cell’s raw value directly, we bypass Excel’s copy-paste escaping—your output will match the clean JSON you get after the Word workaround.
• Automatic .json Extension: The macro checks if your filename already includes the extension and adds it if not.
• Flexible Save Location: You’ll get a standard save dialog to choose where to store the file, or you can hardcode a fixed folder path by replacing the GetSaveAsFilename section with something like C:\YourTargetFolder\ & fileName.
• Encoding Options: The default uses ASCII encoding; switch the third CreateTextFile parameter to True for UTF-8 if your JSON includes special characters.

How to Add the Macro Button to Your Excel Sheet #

1. Open the VBA Editor: Press Alt + F11 on your keyboard.
2. Insert a Module: Go to Insert > Module in the top menu, then paste the macro code into the blank module.
3. Show the Developer Tab (if hidden):
   • Go to File > Options > Customize Ribbon.
   • Check the box next to Developer in the right-hand list, then click OK.
4. Add the Button:
   • Navigate to the Developer tab, click Controls > Insert, then select Button (Form Control).
   • Draw the button on your worksheet where you want it placed.
   • In the pop-up dialog, select the ExportJSONtoFile macro and click OK.
   • Rename the button (right-click > Edit Text) to something like "Export JSON".

Optional: Tweak Your CONCAT Formula (If You Want) #

While the macro fixes the quote issue automatically, you can simplify your formula by replacing CHAR(34) with """ (three double quotes)—it’s Excel’s shorthand for inserting a single quote. For example:

=CONCAT("{""job"": {""name"": """, [NAME CELL], """, ...}")

This is totally optional though—the macro works perfectly with your existing formula.

内容的提问来源于stack exchange，提问作者Stuck