json - 将 Excel 选定范围导出为 Json

Question

我正在尝试将选定范围导出到 json 并反复遇到接收错误消息“未定义子或函数”的相同问题。

Sub PrintJson()
Dim fs As Object
Dim jsonfile
Dim rangetoexport As Range
Dim rowcounter As Long
Dim columncounter As Long
Dim linedata As String
Dim path As String
Dim fname As String

Set rangetoexport = Sheets("Marketing").Range("C6", Range("C6").End(xlDown).End(xlToRight))

path = ThisWorkbook.path & "\"
fname = "export.json"

Set fs = CreateObject("Scripting.FileSystemObject")
Set jsonfile = fs.CreateTextFile(path & fname, True)

linedata = "{""Output"": ["
jsonfile.WriteLine linedata
For rowcounter = 2 To rangetoexport.Rows.Count
    linedata = ""
    For columncounter = 1 To rangetoexport.Columns.Count
        linedata = linedata & """" & Replace(rangetoexport.Cells(1, columncounter), """", "\""") & """" & ":" & """" _
        & Replace(rangetoexport.Cells(rowcounter, columncounter), """", "\""") & """" & ","
    Next
    linedata = Left(linedata, Len(linedata) - 1)
    If rowcounter = rangetoexport.Rows.Count Then
        linedata = "{" & linedata & "}"
    Else
        linedata = "{" & linedata & "},"
    End If

    jsonfile.WriteLine linedata
Next
linedata = "]}"
jsonfile.WriteLine linedata
jsonfile.Close

Set fs = Nothing

End Sub

我对 VBA 真的很陌生，只是在学习。但这让我感到困惑，所以任何帮助表示赞赏。

标签： jsonexcelvbaexport