Open 2022/10/22にコメント追加 9

VBAでExcelのデータをJson形式で出力するまで

Excel

初心者

JSON

VBA

タイトルの通りExcelのデータをJsonとして出力したいことがあったので調べた内容ややったことを議事録として残していきます。

Excelを開き、


                 ファイル > その他 > オプション

を開く


                Excelのオプション

が開くので、


                リボンのユーザー設定

を選択し、右側にある


                開発

にチェックを入れる

Excelの画面に戻り、上に


                 開発

というタブが出ていればOK

上で表示させた


                 開発

タブをクリックし、左上にある


                 Visual Basic

をクリックする

するとVBE( Visual Basic Editor )というマクロを作成・編集するための専用のエディターが開かれる

使い方は下記サイトが詳しく書かれており、私はこちらのサイトで学習しました。
</span><a href="https://excel-ubara.com/excelvba1/EXCELVBA481.html" style="" target="_blank" rel="nofollow noopener noreferrer">https://excel-ubara.com/excelvba1/EXCELVBA481.html</a></p> </div></div><footer class="ThreadItemContent_footer__LjgIe"><div class="LikeButton_container__lhHqp style-icon"><button aria-label="いいね" class="LikeButton_button__uWgw1"><svg class="LikeButton_svgLike___dmwM" viewbox="0 0 110 110"><path class="LikeButton_svgLikeLine__ncYg7" d="M73,24a23.78,23.78,0,0,0-15.89,6.19,3.14,3.14,0,0,1-4.18,0A23.81,23.81,0,0,0,37,24a22,22,0,0,0-22,22c0,16.67,19.64,32.82,25.11,37.93,2.84,2.65,6.15,5.64,8.92,8.13a8.9,8.9,0,0,0,11.9,0c2.77-2.49,6.07-5.48,8.91-8.13C75.37,78.81,95,62.66,95,46A22,22,0,0,0,73,24Z" fill="currentColor"/><path class="LikeButton_svgLikeInner__AOG_t" d="M66.25,76.42c-.71.64-1.32,1.2-1.82,1.67-2.51,2.33-5.39,5-7.94,7.25a2.21,2.21,0,0,1-3,0C51,83,48.1,80.42,45.59,78.09c-.5-.47-1.12-1-1.82-1.67C38.09,71.29,23,57.67,23,46A14,14,0,0,1,37,32a15.92,15.92,0,0,1,11.65,5.23l4.73,5a2.2,2.2,0,0,0,3.23,0l4.72-5A16.06,16.06,0,0,1,73,32,14,14,0,0,1,87,46C87,57.67,71.93,71.29,66.25,76.42Z" fill="currentColor"/><g class="LikeButton_svgLikeDecoration__X2v53"><circle cx="41.5" cy="9.5" fill="#3ea8ff" r="3.5"/><circle cx="98.5" cy="26.5" fill="#ffdc6e" r="3.5"/><circle cx="13" cy="19" fill="#c067f4" r="5"/><circle cx="77" cy="9" fill="#f76685" r="5"/><circle cx="26.5" cy="92.5" fill="#f76685" r="3.5"/><circle cx="105.5" cy="48.5" fill="#c067f4" r="3.5"/><circle cx="4.5" cy="60.5" fill="#3ea8ff" r="3.5"/><circle cx="94.5" cy="73.5" fill="#3ea8ff" r="1.5"/><circle cx="16.5" cy="75.5" fill="#ffdc6e" r="1.5"/><circle cx="78.5" cy="91.5" fill="#ffdc6e" r="1.5"/></g></svg></button></div><button class="ThreadItemContent_reply___Rso2"><svg viewbox="0 0 27 27" aria-label="コメント" class="ThreadItemContent_replySvg__QDHPi" height="19" width="19"><path fill="currentColor" d="M5.9,25.3a1.27,1.27,0,0,1-.6-.1,1.68,1.68,0,0,1-.9-1.3V18.8a8.53,8.53,0,0,1-2.2-6.4C2.2,6.5,6.9,2.3,13.5,2.3S24.8,6.6,24.8,12.4,20,22.6,13.5,22.6a6.36,6.36,0,0,1-2.5-.3L6.7,25A1.6,1.6,0,0,1,5.9,25.3ZM13.5,4.7c-5.2,0-8.8,3.2-8.8,7.7,0,2.4.5,3.7,1.8,5a1.09,1.09,0,0,1,.4.9V22l3.3-2.1a1.38,1.38,0,0,1,1.1-.1,4.49,4.49,0,0,0,2.3.4c5.2,0,8.8-3.2,8.8-7.7S18.7,4.7,13.5,4.7Z"/></svg><span>返信</span></button></footer></div></article></div><div class="ScrapThread_item__4G_47"><article class="ThreadItemContent_parentItem__NDSRE" id="comment-ac46a5299f0a38"><aside class="ThreadItemContent_header__wPez0"><a class="ThreadItemContent_authorAvatar__TUsK8" href="/namioto"><img alt="なみお" class="AvatarImage_plain__BCJNs " height="36" loading="lazy" referrerpolicy="no-referrer" src="https://res.cloudinary.com/zenn/image/fetch/s--4geB6KKn--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/f031ffe5bf.jpeg" width="36"/></a><a class="ThreadItemContent_userName__4WlCb" href="/namioto">なみお</a><a class="ThreadItemContent_dateLink__OUwVw" href="#comment-ac46a5299f0a38"><time datetime="2022-10-22T05:14:07+00:00">2022/10/22</time></a><button aria-label="メニューを開く" class="ThreadItemContent_menu__WtHSc"><svg viewbox="0 0 27 27" height="16" width="16"><path fill="currentColor" d="M12.74,20.53,3.48,11.35a.75.75,0,0,1,0-1.07L4.71,9.05a.75.75,0,0,1,1.07,0l7.49,7.41,7.49-7.41a.74.74,0,0,1,1.06,0l1.24,1.23a.77.77,0,0,1,0,1.07L13.8,20.53A.74.74,0,0,1,12.74,20.53Z"/></svg></button></aside><div class="ThreadItemContent_content__kd2ZM"><div class="InsertCopyButtonToCodeBlock_insertCopyButtonWrapper__tRZUu"><div class="znc BodyCommentContent_bodyCommentContainer__WXWq0"><h2 id="%E3%80%8Cmicrosoft-scripting-runtime%E3%80%8D%E3%81%AE%E5%8F%82%E7%85%A7%E8%A8%AD%E5%AE%9A"> <a class="header-anchor-link" href="#%E3%80%8Cmicrosoft-scripting-runtime%E3%80%8D%E3%81%AE%E5%8F%82%E7%85%A7%E8%A8%AD%E5%AE%9A" aria-hidden="true"/> 「Microsoft Scripting Runtime」の参照設定</h2> <p>この後設定する<code>VBA-JSON</code>で使用する<code>Dictionary</code>型を使えるようにするために設定をします。<br/> 設定の仕方は下記サイトがわかりやすかったです。<br style=""/> <span class="embed-block zenn-embedded zenn-embedded-card"><iframe id="zenn-embedded__1871bfcbb1db9" src="https://embed.zenn.studio/card#zenn-embedded__1871bfcbb1db9" data-content="https%3A%2F%2Fvba-labo.rs-techdev.com%2Farchives%2F27" frameborder="0" scrolling="no" loading="lazy"/></span><a href="https://vba-labo.rs-techdev.com/archives/27" style="" target="_blank" rel="nofollow noopener noreferrer">https://vba-labo.rs-techdev.com/archives/27</a></p> </div></div><footer class="ThreadItemContent_footer__LjgIe"><div class="LikeButton_container__lhHqp style-icon"><button aria-label="いいね" class="LikeButton_button__uWgw1"><svg class="LikeButton_svgLike___dmwM" viewbox="0 0 110 110"><path class="LikeButton_svgLikeLine__ncYg7" d="M73,24a23.78,23.78,0,0,0-15.89,6.19,3.14,3.14,0,0,1-4.18,0A23.81,23.81,0,0,0,37,24a22,22,0,0,0-22,22c0,16.67,19.64,32.82,25.11,37.93,2.84,2.65,6.15,5.64,8.92,8.13a8.9,8.9,0,0,0,11.9,0c2.77-2.49,6.07-5.48,8.91-8.13C75.37,78.81,95,62.66,95,46A22,22,0,0,0,73,24Z" fill="currentColor"/><path class="LikeButton_svgLikeInner__AOG_t" d="M66.25,76.42c-.71.64-1.32,1.2-1.82,1.67-2.51,2.33-5.39,5-7.94,7.25a2.21,2.21,0,0,1-3,0C51,83,48.1,80.42,45.59,78.09c-.5-.47-1.12-1-1.82-1.67C38.09,71.29,23,57.67,23,46A14,14,0,0,1,37,32a15.92,15.92,0,0,1,11.65,5.23l4.73,5a2.2,2.2,0,0,0,3.23,0l4.72-5A16.06,16.06,0,0,1,73,32,14,14,0,0,1,87,46C87,57.67,71.93,71.29,66.25,76.42Z" fill="currentColor"/><g class="LikeButton_svgLikeDecoration__X2v53"><circle cx="41.5" cy="9.5" fill="#3ea8ff" r="3.5"/><circle cx="98.5" cy="26.5" fill="#ffdc6e" r="3.5"/><circle cx="13" cy="19" fill="#c067f4" r="5"/><circle cx="77" cy="9" fill="#f76685" r="5"/><circle cx="26.5" cy="92.5" fill="#f76685" r="3.5"/><circle cx="105.5" cy="48.5" fill="#c067f4" r="3.5"/><circle cx="4.5" cy="60.5" fill="#3ea8ff" r="3.5"/><circle cx="94.5" cy="73.5" fill="#3ea8ff" r="1.5"/><circle cx="16.5" cy="75.5" fill="#ffdc6e" r="1.5"/><circle cx="78.5" cy="91.5" fill="#ffdc6e" r="1.5"/></g></svg></button></div><button class="ThreadItemContent_reply___Rso2"><svg viewbox="0 0 27 27" aria-label="コメント" class="ThreadItemContent_replySvg__QDHPi" height="19" width="19"><path fill="currentColor" d="M5.9,25.3a1.27,1.27,0,0,1-.6-.1,1.68,1.68,0,0,1-.9-1.3V18.8a8.53,8.53,0,0,1-2.2-6.4C2.2,6.5,6.9,2.3,13.5,2.3S24.8,6.6,24.8,12.4,20,22.6,13.5,22.6a6.36,6.36,0,0,1-2.5-.3L6.7,25A1.6,1.6,0,0,1,5.9,25.3ZM13.5,4.7c-5.2,0-8.8,3.2-8.8,7.7,0,2.4.5,3.7,1.8,5a1.09,1.09,0,0,1,.4.9V22l3.3-2.1a1.38,1.38,0,0,1,1.1-.1,4.49,4.49,0,0,0,2.3.4c5.2,0,8.8-3.2,8.8-7.7S18.7,4.7,13.5,4.7Z"/></svg><span>返信</span></button></footer></div></article></div><div class="ScrapThread_item__4G_47"><article class="ThreadItemContent_parentItem__NDSRE" id="comment-2f9ebec34113a0"><aside class="ThreadItemContent_header__wPez0"><a class="ThreadItemContent_authorAvatar__TUsK8" href="/namioto"><img alt="なみお" class="AvatarImage_plain__BCJNs " height="36" loading="lazy" referrerpolicy="no-referrer" src="https://res.cloudinary.com/zenn/image/fetch/s--4geB6KKn--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/f031ffe5bf.jpeg" width="36"/></a><a class="ThreadItemContent_userName__4WlCb" href="/namioto">なみお</a><a class="ThreadItemContent_dateLink__OUwVw" href="#comment-2f9ebec34113a0"><time datetime="2022-10-22T06:23:50+00:00">2022/10/22に更新</time></a><button aria-label="メニューを開く" class="ThreadItemContent_menu__WtHSc"><svg viewbox="0 0 27 27" height="16" width="16"><path fill="currentColor" d="M12.74,20.53,3.48,11.35a.75.75,0,0,1,0-1.07L4.71,9.05a.75.75,0,0,1,1.07,0l7.49,7.41,7.49-7.41a.74.74,0,0,1,1.06,0l1.24,1.23a.77.77,0,0,1,0,1.07L13.8,20.53A.74.74,0,0,1,12.74,20.53Z"/></svg></button></aside><div class="ThreadItemContent_content__kd2ZM"><div class="InsertCopyButtonToCodeBlock_insertCopyButtonWrapper__tRZUu"><div class="znc BodyCommentContent_bodyCommentContainer__WXWq0"><h2 id="%E3%80%8Cvba-json%E3%80%8D%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88"> <a class="header-anchor-link" href="#%E3%80%8Cvba-json%E3%80%8D%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88" aria-hidden="true"/> 「VBA-JSON」のインポート</h2> <p><code>VBA-JSON</code>は<code>Dictionary</code>型というVBAで使用する型を<code>Json</code>形式の<code>String</code>型に変換してくれるものです。<br/> <code>Json</code>を出力するためには必須です。</p> <li>下記のサイト ( github ) を開く</li> <p><span class="embed-block zenn-embedded zenn-embedded-card"><iframe id="zenn-embedded__cca8e9ce7d352" src="https://embed.zenn.studio/card#zenn-embedded__cca8e9ce7d352" data-content="https%3A%2F%2Fgithub.com%2FVBA-tools%2FVBA-JSON" frameborder="0" scrolling="no" loading="lazy"/></span><a href="https://github.com/VBA-tools/VBA-JSON" style="" target="_blank" rel="nofollow noopener noreferrer">https://github.com/VBA-tools/VBA-JSON</a></p> <li>開いたgithubの最下部あたりにある<code>Installation</code>の<code>latest release</code>をクリック</li> <code>Source code (zip)</code>をクリックしzipファイルをダウンロード</li> <li>ダウンロードしたzipファイルを解凍する</li> <li>解凍したフォルダの中にある<code>JsonConverter.bas</code>をVBEにインポートする</li> <p>basファイルのインポート方法<br/> VBEを開き<code>ファイル &gt; ファイルのインポート</code>をクリックし、ファイルを選択する<br/> 上の場合<code>JsonConverter.bas</code>ファイルを選択する</p> </div></div><footer class="ThreadItemContent_footer__LjgIe"><div class="LikeButton_container__lhHqp style-icon"><button aria-label="いいね" class="LikeButton_button__uWgw1"><svg class="LikeButton_svgLike___dmwM" viewbox="0 0 110 110"><path class="LikeButton_svgLikeLine__ncYg7" d="M73,24a23.78,23.78,0,0,0-15.89,6.19,3.14,3.14,0,0,1-4.18,0A23.81,23.81,0,0,0,37,24a22,22,0,0,0-22,22c0,16.67,19.64,32.82,25.11,37.93,2.84,2.65,6.15,5.64,8.92,8.13a8.9,8.9,0,0,0,11.9,0c2.77-2.49,6.07-5.48,8.91-8.13C75.37,78.81,95,62.66,95,46A22,22,0,0,0,73,24Z" fill="currentColor"/><path class="LikeButton_svgLikeInner__AOG_t" d="M66.25,76.42c-.71.64-1.32,1.2-1.82,1.67-2.51,2.33-5.39,5-7.94,7.25a2.21,2.21,0,0,1-3,0C51,83,48.1,80.42,45.59,78.09c-.5-.47-1.12-1-1.82-1.67C38.09,71.29,23,57.67,23,46A14,14,0,0,1,37,32a15.92,15.92,0,0,1,11.65,5.23l4.73,5a2.2,2.2,0,0,0,3.23,0l4.72-5A16.06,16.06,0,0,1,73,32,14,14,0,0,1,87,46C87,57.67,71.93,71.29,66.25,76.42Z" fill="currentColor"/><g class="LikeButton_svgLikeDecoration__X2v53"><circle cx="41.5" cy="9.5" fill="#3ea8ff" r="3.5"/><circle cx="98.5" cy="26.5" fill="#ffdc6e" r="3.5"/><circle cx="13" cy="19" fill="#c067f4" r="5"/><circle cx="77" cy="9" fill="#f76685" r="5"/><circle cx="26.5" cy="92.5" fill="#f76685" r="3.5"/><circle cx="105.5" cy="48.5" fill="#c067f4" r="3.5"/><circle cx="4.5" cy="60.5" fill="#3ea8ff" r="3.5"/><circle cx="94.5" cy="73.5" fill="#3ea8ff" r="1.5"/><circle cx="16.5" cy="75.5" fill="#ffdc6e" r="1.5"/><circle cx="78.5" cy="91.5" fill="#ffdc6e" r="1.5"/></g></svg></button></div><button class="ThreadItemContent_reply___Rso2"><svg viewbox="0 0 27 27" aria-label="コメント" class="ThreadItemContent_replySvg__QDHPi" height="19" width="19"><path fill="currentColor" d="M5.9,25.3a1.27,1.27,0,0,1-.6-.1,1.68,1.68,0,0,1-.9-1.3V18.8a8.53,8.53,0,0,1-2.2-6.4C2.2,6.5,6.9,2.3,13.5,2.3S24.8,6.6,24.8,12.4,20,22.6,13.5,22.6a6.36,6.36,0,0,1-2.5-.3L6.7,25A1.6,1.6,0,0,1,5.9,25.3ZM13.5,4.7c-5.2,0-8.8,3.2-8.8,7.7,0,2.4.5,3.7,1.8,5a1.09,1.09,0,0,1,.4.9V22l3.3-2.1a1.38,1.38,0,0,1,1.1-.1,4.49,4.49,0,0,0,2.3.4c5.2,0,8.8-3.2,8.8-7.7S18.7,4.7,13.5,4.7Z"/></svg><span>返信</span></button></footer></div></article></div><div class="ScrapThread_item__4G_47"><article class="ThreadItemContent_parentItem__NDSRE" id="comment-d66412d0075604"><aside class="ThreadItemContent_header__wPez0"><a class="ThreadItemContent_authorAvatar__TUsK8" href="/namioto"><img alt="なみお" class="AvatarImage_plain__BCJNs " height="36" loading="lazy" referrerpolicy="no-referrer" src="https://res.cloudinary.com/zenn/image/fetch/s--4geB6KKn--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/f031ffe5bf.jpeg" width="36"/></a><a class="ThreadItemContent_userName__4WlCb" href="/namioto">なみお</a><a class="ThreadItemContent_dateLink__OUwVw" href="#comment-d66412d0075604"><time datetime="2022-10-22T05:57:10+00:00">2022/10/22に更新</time></a><button aria-label="メニューを開く" class="ThreadItemContent_menu__WtHSc"><svg viewbox="0 0 27 27" height="16" width="16"><path fill="currentColor" d="M12.74,20.53,3.48,11.35a.75.75,0,0,1,0-1.07L4.71,9.05a.75.75,0,0,1,1.07,0l7.49,7.41,7.49-7.41a.74.74,0,0,1,1.06,0l1.24,1.23a.77.77,0,0,1,0,1.07L13.8,20.53A.74.74,0,0,1,12.74,20.53Z"/></svg></button></aside><div class="ThreadItemContent_content__kd2ZM"><div class="InsertCopyButtonToCodeBlock_insertCopyButtonWrapper__tRZUu"><div class="znc BodyCommentContent_bodyCommentContainer__WXWq0"><h2 id="%E6%97%A9%E9%80%9F%E5%87%BA%E5%8A%9B%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B"> <a class="header-anchor-link" href="#%E6%97%A9%E9%80%9F%E5%87%BA%E5%8A%9B%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B" aria-hidden="true"/> 早速出力してみる</h2> <li>VBEを開いた状態で上のメニュータブから<code>挿入 &gt; 標準モジュール</code>をクリック<br/> <li>上のコードを入力する窓に下記コードを入力</li> <details><summary>コード全文</summary><div class="details-content"><div class="code-block-container"> <div class="code-block-filename-container"><span class="code-block-filename"> Module1</span></div> <pre class="language-vba"><code class="language-vba"><span class="token keyword">Sub</span> test<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">Dim</span> params <span class="token keyword">As</span> <span class="token keyword">New</span> Dictionary <span class="token comment">' keyとvalueを追加</span> params<span class="token punctuation">.</span>Add <span class="token string">"key1"</span><span class="token punctuation">,</span> <span class="token string">"value1"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key2"</span><span class="token punctuation">,</span> <span class="token string">"value2"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key3"</span><span class="token punctuation">,</span> <span class="token string">"value3"</span> <span class="token comment">' Dictionaryの入れ子</span> <span class="token keyword">Dim</span> child <span class="token keyword">As</span> <span class="token keyword">New</span> Dictionary child<span class="token punctuation">.</span>Add <span class="token string">"childKey1"</span><span class="token punctuation">,</span> <span class="token string">"childValue1"</span> child<span class="token punctuation">.</span>Add <span class="token string">"childKey2"</span><span class="token punctuation">,</span> <span class="token string">"childValue2"</span> child<span class="token punctuation">.</span>Add <span class="token string">"childKey3"</span><span class="token punctuation">,</span> <span class="token string">"childValue3"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key4"</span><span class="token punctuation">,</span> child <span class="token comment">' Listを追加</span> <span class="token keyword">Dim</span> list <span class="token keyword">As</span> <span class="token keyword">New</span> Collection list<span class="token punctuation">.</span>Add <span class="token string">"item1"</span> list<span class="token punctuation">.</span>Add <span class="token string">"item2"</span> list<span class="token punctuation">.</span>Add <span class="token string">"item3"</span> list<span class="token punctuation">.</span>Add <span class="token string">"item4"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key5"</span><span class="token punctuation">,</span> list <span class="token comment">' Jsonを作成</span> <span class="token keyword">Dim</span> json <span class="token keyword">As</span> <span class="token keyword">String</span> json <span class="token operator">=</span> JsonConverter<span class="token punctuation">.</span>ConvertToJson<span class="token punctuation">(</span>params<span class="token punctuation">,</span> Whitespace<span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token comment">' Jsonを出力</span> <span class="token keyword">With</span> CreateObject<span class="token punctuation">(</span><span class="token string">"ADODB.Stream"</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token keyword">Type</span> <span class="token operator">=</span> <span class="token number">2</span> <span class="token punctuation">.</span>Charset <span class="token operator">=</span> <span class="token string">"utf-8"</span> <span class="token punctuation">.</span>Open <span class="token punctuation">.</span>WriteText json<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">.</span>SaveToFile ThisWorkbook<span class="token punctuation">.</span>Path <span class="token operator">&amp;</span> <span class="token string">"/"</span> <span class="token operator">&amp;</span> <span class="token string">"data.json"</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">.</span>Close <span class="token keyword">End</span> <span class="token keyword">With</span> <span class="token keyword">End</span> <span class="token keyword">Sub</span> <li>VBEを開いた状態で<code>F5</code>を押し、実行する</li> <li>デスクトップに<code>data.json</code>が出力されている</li> <p>これでJsonファイルの出力ができました。<br/> ちなみに実行したファイルをvscodeで開いたのが下記画像です。<br/> <p>コードにある<code>JsonConverter.ConvertToJson(params, Whitespace:=4)</code>の<code>Whitespace:=4</code>という指定をしているの入れ子ごとにスペース4つ分の間隔があいてます。</p> </div></div><footer class="ThreadItemContent_footer__LjgIe"><div class="LikeButton_container__lhHqp style-icon"><button aria-label="いいね" class="LikeButton_button__uWgw1"><svg class="LikeButton_svgLike___dmwM" viewbox="0 0 110 110"><path class="LikeButton_svgLikeLine__ncYg7" d="M73,24a23.78,23.78,0,0,0-15.89,6.19,3.14,3.14,0,0,1-4.18,0A23.81,23.81,0,0,0,37,24a22,22,0,0,0-22,22c0,16.67,19.64,32.82,25.11,37.93,2.84,2.65,6.15,5.64,8.92,8.13a8.9,8.9,0,0,0,11.9,0c2.77-2.49,6.07-5.48,8.91-8.13C75.37,78.81,95,62.66,95,46A22,22,0,0,0,73,24Z" fill="currentColor"/><path class="LikeButton_svgLikeInner__AOG_t" d="M66.25,76.42c-.71.64-1.32,1.2-1.82,1.67-2.51,2.33-5.39,5-7.94,7.25a2.21,2.21,0,0,1-3,0C51,83,48.1,80.42,45.59,78.09c-.5-.47-1.12-1-1.82-1.67C38.09,71.29,23,57.67,23,46A14,14,0,0,1,37,32a15.92,15.92,0,0,1,11.65,5.23l4.73,5a2.2,2.2,0,0,0,3.23,0l4.72-5A16.06,16.06,0,0,1,73,32,14,14,0,0,1,87,46C87,57.67,71.93,71.29,66.25,76.42Z" fill="currentColor"/><g class="LikeButton_svgLikeDecoration__X2v53"><circle cx="41.5" cy="9.5" fill="#3ea8ff" r="3.5"/><circle cx="98.5" cy="26.5" fill="#ffdc6e" r="3.5"/><circle cx="13" cy="19" fill="#c067f4" r="5"/><circle cx="77" cy="9" fill="#f76685" r="5"/><circle cx="26.5" cy="92.5" fill="#f76685" r="3.5"/><circle cx="105.5" cy="48.5" fill="#c067f4" r="3.5"/><circle cx="4.5" cy="60.5" fill="#3ea8ff" r="3.5"/><circle cx="94.5" cy="73.5" fill="#3ea8ff" r="1.5"/><circle cx="16.5" cy="75.5" fill="#ffdc6e" r="1.5"/><circle cx="78.5" cy="91.5" fill="#ffdc6e" r="1.5"/></g></svg></button></div><button class="ThreadItemContent_reply___Rso2"><svg viewbox="0 0 27 27" aria-label="コメント" class="ThreadItemContent_replySvg__QDHPi" height="19" width="19"><path fill="currentColor" d="M5.9,25.3a1.27,1.27,0,0,1-.6-.1,1.68,1.68,0,0,1-.9-1.3V18.8a8.53,8.53,0,0,1-2.2-6.4C2.2,6.5,6.9,2.3,13.5,2.3S24.8,6.6,24.8,12.4,20,22.6,13.5,22.6a6.36,6.36,0,0,1-2.5-.3L6.7,25A1.6,1.6,0,0,1,5.9,25.3ZM13.5,4.7c-5.2,0-8.8,3.2-8.8,7.7,0,2.4.5,3.7,1.8,5a1.09,1.09,0,0,1,.4.9V22l3.3-2.1a1.38,1.38,0,0,1,1.1-.1,4.49,4.49,0,0,0,2.3.4c5.2,0,8.8-3.2,8.8-7.7S18.7,4.7,13.5,4.7Z"/></svg><span>返信</span></button></footer></div></article></div><div class="ScrapThread_item__4G_47"><article class="ThreadItemContent_parentItem__NDSRE" id="comment-ff2a89b4f1f580"><aside class="ThreadItemContent_header__wPez0"><a class="ThreadItemContent_authorAvatar__TUsK8" href="/namioto"><img alt="なみお" class="AvatarImage_plain__BCJNs " height="36" loading="lazy" referrerpolicy="no-referrer" src="https://res.cloudinary.com/zenn/image/fetch/s--4geB6KKn--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/f031ffe5bf.jpeg" width="36"/></a><a class="ThreadItemContent_userName__4WlCb" href="/namioto">なみお</a><a class="ThreadItemContent_dateLink__OUwVw" href="#comment-ff2a89b4f1f580"><time datetime="2022-10-22T06:07:26+00:00">2022/10/22</time></a><button aria-label="メニューを開く" class="ThreadItemContent_menu__WtHSc"><svg viewbox="0 0 27 27" height="16" width="16"><path fill="currentColor" d="M12.74,20.53,3.48,11.35a.75.75,0,0,1,0-1.07L4.71,9.05a.75.75,0,0,1,1.07,0l7.49,7.41,7.49-7.41a.74.74,0,0,1,1.06,0l1.24,1.23a.77.77,0,0,1,0,1.07L13.8,20.53A.74.74,0,0,1,12.74,20.53Z"/></svg></button></aside><div class="ThreadItemContent_content__kd2ZM"><div class="InsertCopyButtonToCodeBlock_insertCopyButtonWrapper__tRZUu"><div class="znc BodyCommentContent_bodyCommentContainer__WXWq0"><h2 id="%E6%97%A5%E6%9C%AC%E8%AA%9E%E3%82%92%E5%85%A5%E5%8A%9B%E3%81%99%E3%82%8B%E3%81%A8..."> <a class="header-anchor-link" href="#%E6%97%A5%E6%9C%AC%E8%AA%9E%E3%82%92%E5%85%A5%E5%8A%9B%E3%81%99%E3%82%8B%E3%81%A8..." aria-hidden="true"/> 日本語を入力すると...</h2> <p>先ほどのコードの<code>Value</code>部分をすべて日本語に変えてみた。</p> <details><summary>日本語に変更後のコード全文</summary><div class="details-content"><div class="code-block-container"> <div class="code-block-filename-container"><span class="code-block-filename"> Module1</span></div> <pre class="language-vba"><code class="language-vba"><span class="token keyword">Sub</span> test<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">Dim</span> params <span class="token keyword">As</span> <span class="token keyword">New</span> Dictionary <span class="token comment">' keyとvalueを追加</span> params<span class="token punctuation">.</span>Add <span class="token string">"key1"</span><span class="token punctuation">,</span> <span class="token string">"あいうえお"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key2"</span><span class="token punctuation">,</span> <span class="token string">"かきくけこ"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key3"</span><span class="token punctuation">,</span> <span class="token string">"さしすせそ"</span> <span class="token comment">' Dictionaryの入れ子</span> <span class="token keyword">Dim</span> child <span class="token keyword">As</span> <span class="token keyword">New</span> Dictionary child<span class="token punctuation">.</span>Add <span class="token string">"childKey1"</span><span class="token punctuation">,</span> <span class="token string">"たちつてと"</span> child<span class="token punctuation">.</span>Add <span class="token string">"childKey2"</span><span class="token punctuation">,</span> <span class="token string">"なにぬねの"</span> child<span class="token punctuation">.</span>Add <span class="token string">"childKey3"</span><span class="token punctuation">,</span> <span class="token string">"はひふへほ"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key4"</span><span class="token punctuation">,</span> child <span class="token comment">' Listを追加</span> <span class="token keyword">Dim</span> list <span class="token keyword">As</span> <span class="token keyword">New</span> Collection list<span class="token punctuation">.</span>Add <span class="token string">"まみむめも"</span> list<span class="token punctuation">.</span>Add <span class="token string">"やゆよ"</span> list<span class="token punctuation">.</span>Add <span class="token string">"らりるれろ"</span> list<span class="token punctuation">.</span>Add <span class="token string">"わをん"</span> params<span class="token punctuation">.</span>Add <span class="token string">"key5"</span><span class="token punctuation">,</span> list <span class="token comment">' Jsonを作成</span> <span class="token keyword">Dim</span> json <span class="token keyword">As</span> <span class="token keyword">String</span> json <span class="token operator">=</span> JsonConverter<span class="token punctuation">.</span>ConvertToJson<span class="token punctuation">(</span>params<span class="token punctuation">,</span> Whitespace<span class="token punctuation">:</span><span class="token operator">=</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token comment">' Jsonを出力</span> <span class="token keyword">With</span> CreateObject<span class="token punctuation">(</span><span class="token string">"ADODB.Stream"</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token keyword">Type</span> <span class="token operator">=</span> <span class="token number">2</span> <span class="token punctuation">.</span>Charset <span class="token operator">=</span> <span class="token string">"utf-8"</span> <span class="token punctuation">.</span>Open <span class="token punctuation">.</span>WriteText json<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">.</span>SaveToFile ThisWorkbook<span class="token punctuation">.</span>Path <span class="token operator">&amp;</span> <span class="token string">"/"</span> <span class="token operator">&amp;</span> <span class="token string">"data.json"</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">.</span>Close <span class="token keyword">End</span> <span class="token keyword">With</span> <span class="token keyword">End</span> <span class="token keyword">Sub</span> <p>このコードを実行し、<code>data.json</code>を確認したところ下記のようにUnicodeの16進数表記になってしまう。<br/> </div></div><footer class="ThreadItemContent_footer__LjgIe"><div class="LikeButton_container__lhHqp style-icon"><button aria-label="いいね" class="LikeButton_button__uWgw1"><svg class="LikeButton_svgLike___dmwM" viewbox="0 0 110 110"><path class="LikeButton_svgLikeLine__ncYg7" d="M73,24a23.78,23.78,0,0,0-15.89,6.19,3.14,3.14,0,0,1-4.18,0A23.81,23.81,0,0,0,37,24a22,22,0,0,0-22,22c0,16.67,19.64,32.82,25.11,37.93,2.84,2.65,6.15,5.64,8.92,8.13a8.9,8.9,0,0,0,11.9,0c2.77-2.49,6.07-5.48,8.91-8.13C75.37,78.81,95,62.66,95,46A22,22,0,0,0,73,24Z" fill="currentColor"/><path class="LikeButton_svgLikeInner__AOG_t" d="M66.25,76.42c-.71.64-1.32,1.2-1.82,1.67-2.51,2.33-5.39,5-7.94,7.25a2.21,2.21,0,0,1-3,0C51,83,48.1,80.42,45.59,78.09c-.5-.47-1.12-1-1.82-1.67C38.09,71.29,23,57.67,23,46A14,14,0,0,1,37,32a15.92,15.92,0,0,1,11.65,5.23l4.73,5a2.2,2.2,0,0,0,3.23,0l4.72-5A16.06,16.06,0,0,1,73,32,14,14,0,0,1,87,46C87,57.67,71.93,71.29,66.25,76.42Z" fill="currentColor"/><g class="LikeButton_svgLikeDecoration__X2v53"><circle cx="41.5" cy="9.5" fill="#3ea8ff" r="3.5"/><circle cx="98.5" cy="26.5" fill="#ffdc6e" r="3.5"/><circle cx="13" cy="19" fill="#c067f4" r="5"/><circle cx="77" cy="9" fill="#f76685" r="5"/><circle cx="26.5" cy="92.5" fill="#f76685" r="3.5"/><circle cx="105.5" cy="48.5" fill="#c067f4" r="3.5"/><circle cx="4.5" cy="60.5" fill="#3ea8ff" r="3.5"/><circle cx="94.5" cy="73.5" fill="#3ea8ff" r="1.5"/><circle cx="16.5" cy="75.5" fill="#ffdc6e" r="1.5"/><circle cx="78.5" cy="91.5" fill="#ffdc6e" r="1.5"/></g></svg></button></div><button class="ThreadItemContent_reply___Rso2"><svg viewbox="0 0 27 27" aria-label="コメント" class="ThreadItemContent_replySvg__QDHPi" height="19" width="19"><path fill="currentColor" d="M5.9,25.3a1.27,1.27,0,0,1-.6-.1,1.68,1.68,0,0,1-.9-1.3V18.8a8.53,8.53,0,0,1-2.2-6.4C2.2,6.5,6.9,2.3,13.5,2.3S24.8,6.6,24.8,12.4,20,22.6,13.5,22.6a6.36,6.36,0,0,1-2.5-.3L6.7,25A1.6,1.6,0,0,1,5.9,25.3ZM13.5,4.7c-5.2,0-8.8,3.2-8.8,7.7,0,2.4.5,3.7,1.8,5a1.09,1.09,0,0,1,.4.9V22l3.3-2.1a1.38,1.38,0,0,1,1.1-.1,4.49,4.49,0,0,0,2.3.4c5.2,0,8.8-3.2,8.8-7.7S18.7,4.7,13.5,4.7Z"/></svg><span>返信</span></button></footer></div></article></div><div class="ScrapThread_item__4G_47"><article class="ThreadItemContent_parentItem__NDSRE" id="comment-2b7c1c43db6be7"><aside class="ThreadItemContent_header__wPez0"><a class="ThreadItemContent_authorAvatar__TUsK8" href="/namioto"><img alt="なみお" class="AvatarImage_plain__BCJNs " height="36" loading="lazy" referrerpolicy="no-referrer" src="https://res.cloudinary.com/zenn/image/fetch/s--4geB6KKn--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/f031ffe5bf.jpeg" width="36"/></a><a class="ThreadItemContent_userName__4WlCb" href="/namioto">なみお</a><a class="ThreadItemContent_dateLink__OUwVw" href="#comment-2b7c1c43db6be7"><time datetime="2022-10-22T06:16:21+00:00">2022/10/22</time></a><button aria-label="メニューを開く" class="ThreadItemContent_menu__WtHSc"><svg viewbox="0 0 27 27" height="16" width="16"><path fill="currentColor" d="M12.74,20.53,3.48,11.35a.75.75,0,0,1,0-1.07L4.71,9.05a.75.75,0,0,1,1.07,0l7.49,7.41,7.49-7.41a.74.74,0,0,1,1.06,0l1.24,1.23a.77.77,0,0,1,0,1.07L13.8,20.53A.74.74,0,0,1,12.74,20.53Z"/></svg></button></aside><div class="ThreadItemContent_content__kd2ZM"><div class="InsertCopyButtonToCodeBlock_insertCopyButtonWrapper__tRZUu"><div class="znc BodyCommentContent_bodyCommentContainer__WXWq0"><h2 id="unicode-16%E9%80%B2%E6%95%B0%E8%A1%A8%E8%A8%98%E3%81%AE%E6%94%B9%E5%96%84"> <a class="header-anchor-link" href="#unicode-16%E9%80%B2%E6%95%B0%E8%A1%A8%E8%A8%98%E3%81%AE%E6%94%B9%E5%96%84" aria-hidden="true"/> Unicode 16進数表記の改善</h2> <li>VBEの左側<code>プロジェクト</code>の中にある<code>JsonConverter</code>をダブルクリックして開く</li> <li>コード内で<code>ctrl + F</code>で<code>VBA.Right$</code>を検索する</li> <li>下記のようにシングルクォーテーションを付け加え、コードを書き換える</li> <div class="code-block-filename-container"><span class="code-block-filename"> JsonConverter</span></div> <pre class="diff-highlight language-diff-vba"><code class="diff-highlight language-diff-vba">・・・中略 <span class="token deleted-sign deleted language-vba"><span class="token prefix deleted">-</span> json_Char <span class="token operator">=</span> <span class="token string">"\u"</span> <span class="token operator">&amp;</span> VBA<span class="token punctuation">.</span>Right<span class="token operator">$</span><span class="token punctuation">(</span><span class="token string">"0000"</span> <span class="token operator">&amp;</span> VBA<span class="token punctuation">.</span>Hex<span class="token operator">$</span><span class="token punctuation">(</span>json_AscCode<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span> </span><span class="token inserted-sign inserted language-vba"><span class="token prefix inserted">+</span> <span class="token comment">' json_Char = "\u" &amp; VBA.Right$("0000" &amp; VBA.Hex$(json_AscCode), 4)</span> </span>・・・中略

タイトルの通りExcelのデータをJsonとして出力したいことがあったので調べた内容ややったことを議事録として残していきます。

筆者の環境

Excelに「開発」タブ（リボン）を表示させる

Visual Basicを開く