0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【ExcelVBA】配列をJSONへ、そして保存

Last updated at Posted at 2021-05-25

不具合あれば教えてください。普段の業務で私は使えています。

参照設定の方法がわからな方はこちら
https://qiita.com/daichi05w/items/c2d827df55bb298e965a

'参照設定:Microsoft ActiveX Data Object x.x Library「x.x」には最も数字が大きいものを選択する
Public Const HOZON_PASS As String = "" 'ファイル名を含む絶対パスを指定する。拡張子は「.json」
Sub main()

    Dim info_table(0 To 3, 0 To 2) As String
    Dim keys(0 To 2) As String
    
    Dim json_string As String
    Dim name As String
    
    name = "scouter"
    
    keys(0) = "name"
    keys(1) = "age"
    keys(2) = "power"
    
    info_table(0, 0) = "sato"
    info_table(0, 1) = "58"
    info_table(0, 2) = "100000"
    info_table(1, 0) = "kato"
    info_table(1, 1) = "12"
    info_table(1, 2) = "1152134"
    info_table(2, 0) = "goto"
    info_table(2, 1) = "35"
    info_table(2, 2) = "4115"
    info_table(3, 0) = "muto"
    info_table(3, 1) = "44"
    info_table(3, 2) = "545144"
    
    json_string = array_to_json(name, keys, info_table)
    Call write_UTF8(json_string, HOZON_PASS)
    
End Sub
Function array_to_json(ByVal name As String, ByRef keys() As String, ByRef info_table() As String) As String

     Dim i As Long
     Dim j As Long
     Dim json_string As String

     json_string = name & "= '["

     For i = 0 To UBound(info_table, 1)

        json_string = json_string & "{"

        For j = 0 To UBound(keys, 1)

            json_string = json_string & """" & keys(j) & """ : """ & info_table(i, j) & """ ,"

        Next

        json_string = Left(json_string, Len(json_string) - 1)
        json_string = json_string & "},"

     Next

     json_string = Left(json_string, Len(json_string) - 1)
     json_string = json_string & "]'"

     array_to_json = json_string

End Function
Sub write_UTF8(ByVal csv_string As String, ByVal pass As String)

    Dim byteData() As Byte
    Dim objStream As ADODB.Stream
    Set objStream = New ADODB.Stream

    With objStream
        .Charset = "UTF-8"
        .LineSeparator = adLF
        .Open
        .WriteText csv_string
        .Position = 0
        .Type = adTypeBinary
        .Position = 3
        byteData = .Read
        .Close
        .Open
        .Write byteData
        .SaveToFile pass, adSaveCreateOverWrite
        .Close

    End With

End Sub

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?