VBA: Power Queryを使ってCSVファイルを読み込む

-

VBAからPower Queryを使ってCSVを読み込んでみました。


CSVファイル

元になるCSVファイルは次の通りです。

ダブルクリックしてExcelで開くと次のように表示されます。

VBAを実行すると次のように読み込むことができます。

コード

コードは次の通りです。

コピペ用です。

Public Sub uImport()
    Dim uWB As Workbook
    Dim uWS As Worksheet
    Dim uFormula As String
    Dim uQuery As WorkbookQuery
    Dim uList As ListObject

    Const uCSVPath As String = "C:\NKTemp\Sample.csv"
    Const uType As String = "{""購入日"", type date}, {""金額"", Int64.Type}"
    Const uQueryName As String = "任意のクエリー名"
    Const uListName As String = "任意のテーブル名"
    Const uSheet As String = "Sheet1"
    Const uDestination  As String = "A3"
    
    Set uWB = ThisWorkbook
    Set uWS = uWB.Worksheets(uSheet)  'テーブルを作成するワークシート
    
    uDeleteTableAndQuery uWS, uListName, uQueryName
    
    uFormula = _
        "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _
            "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _
        "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _
        "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _
            "{" & uType & "}) " & _
        "in uChangedType"   ' M言語によるクエリー
    
    Set uQuery = uWB.Queries.Add(Name:=uQueryName, Formula:=uFormula)   'クエリーを作成
    
    Set uList = uWS.ListObjects.Add( _
        SourceType:=xlSrcExternal, _
        Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;" & _
            "Location=" & uQuery.Name, _
        Destination:=uWS.Range(uDestination)) 'テーブルを作成（クエリーテーブル付き）
    
    With uList.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")
        
        .ListObject.DisplayName = uListName
        
        .Refresh BackgroundQuery:=False '読込実行
    End With
End Sub


'指定されたーテーブルとクエリーを削除
Private Sub uDeleteTableAndQuery( _
    ByVal uWS As Worksheet, _
    ByVal uListName As String, _
    ByVal uQueryName As String)

    Dim uWB As Workbook
    Dim uQuery As WorkbookQuery
    Dim uList As ListObject
    
    For Each uList In uWS.ListObjects
        If uList.Name = uListName Then
            uList.Delete
            Exit For
        End If
    Next
    
    Set uWB = uWS.Parent
    
    For Each uQuery In uWB.Queries
        If uQuery.Name = uQueryName Then
            uQuery.Delete
            Exit For
        End If
    Next
End Sub


解説

「マクロの記録」を使ってCSVを読み込んだ際に生成されるコードを元に作り直しています。

最初の Constant はパラメーターです。

Const uCSVPath As String = "C:\NKTemp\Sample.csv"
Const uType As String = "{""購入日"", type date}, {""金額"", Int64.Type}"
Const uQueryName As String = "任意のクエリー名"
Const uListName As String = "任意のテーブル名"
Const uSheet As String = "Sheet1"
Const uDestination  As String = "A3"

CSVファイルの場所
型を変更する列と型指定
クエリー名
テーブル名
シート名
そして、テーブルを配置する開始セルです。

次の行では、ワークブックとワークシートを指定しています。必要に応じて、任意のものを指定するとよいでしょう。

Set uWB = ThisWorkbook
Set uWS = uWB.Worksheets(uSheet)  'テーブルを作成するワークシート

繰り返し読み込む場合に備えて、最初にクエリーとテーブルの削除を行っています。

uDeleteTableAndQuery uWS, uListName, uQueryName


M言語部分

クエリー(Power Query)の命令部分です。M言語で記述されています。

uSourceでCSVファイルを開き、uPromotedHeadersでCSVファイルの一行目を見出しにして、uChangedTypeで列のデータ型を指定しています。

uFormula = _
    "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _
        "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _
    "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _
    "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _
        "{" & uType & "}) " & _
    "in uChangedType"   ' M言語によるクエリー

Csv.Document

Delmiterは区切り文字なので、必要に応じてタブなどを指定するとよいでしょう。

Encodingは、シフトJISなので932が指定されています。これも適宜指定するとよいでしょう。省略するとUTF-8になるようです。

QuoteStyleは""の中に改行が現れた場合の処理です。セル内改行されているようなデータを読み込む際に必要だと思います。通常はQuoteStyle.Csvのままが良いでしょう。

詳細については、Microsoftのリファレンスを見た方が良いでしょう。

Csv.Document 

Table.PromoteHeaders

ファイルの一行目が見出しではなくデータであれば、この行は不要だと思います。

Table.TransformColumnTypes

列の型の変換、という事になるでしょう。

試した限りでは、Csv.Documentで読み込んだだけだとすべての列が文字として読み込まれていました。

文字以外に、数値や、日付を指定したい場合には、上の購入日や金額の例のようにデータタイプを指定します。

データ型を指定しなくても良い場合には、この行も不要だと思います。削除する場合には、最後の in uChangeTypeをuPromotedHeadersや、uSourceに変更する必要があります。

より汎用的にするには、ここで変換せず、読み込んだ後にセルの書式指定やクエリーテーブルのSQL文などで変更してしまっても良いのかもしれません。

クエリーの作成

Queries.Addを使ってワークブックにクエリーを追加しています。

Set uQuery = uWB.Queries.Add(Name:=uQueryName, Formula:=uFormula) 

追加されたクエリーはExcelの「データ」にある「クエリと接続」をクリックすると確認することができます。


テーブルの作成

ListObject.Addを使って、クエリーにリンクしたテーブルを作成します。

Set uList = uWS.ListObjects.Add( _
    SourceType:=xlSrcExternal, _
    Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;" & _
        "Location=" & uQuery.Name, _
    Destination:=uWS.Range(uDestination))

xlSrcExternalは次のページを参照するとよいでしょう。

XlListObjectSourceType 列挙 (Excel)

Sourceにデータを読み込むための文字列を指定します。Locationにクエリー名を指定することにより、指定したクエリーにリンクできるようです。

Destinationはテーブルを配置する場所を示します。

クエリーテーブルの設定と実行

テーブル（ListObject）に接続されたクエリーテーブルを設定し、クエリーを実行します。

クエリーとクエリーテーブルは別物なので注意が必要です。

With uList.QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")
    .ListObject.DisplayName = uListName
    .Refresh BackgroundQuery:=False '読込実行
End With

CommandTypeはxlCmdSqlで固定だと思います。

CommandTextにSQL文を指定できます。

ListObject.DisplayNameで、クエリーテーブルに接続されているテーブルの名前を変更しています。必須ではありません。

Refleshメソッドを実行することにより、クエリーが実行され、CSVファイルからの読み込みが実行されます。直接クエリーを実行しても同じ結果になると思います。

uDeleteTableAndQuery

指定されたテーブルとクエリーを削除しているだけです。

クエリーはワークブックにリンクし、テーブルはワークシートにリンクすることを理解しておいた方が良いでしょう。


クエリーテーブルの確認方法

クエリーテーブルは、テーブルクリック→「テーブルデザイン」「プロパティ」「クエリプロパティ」「定義」で確認できました。ただし、変更はできないようです。


クエリーテーブルで絞り込む

クエリーテーブルに指定したSQL文を変更することにより、絞り込みを行うことができます。

.CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")

次のように指定することにより、指定した列のみをテーブルに表示することが可能です。

.CommandText = Array("SELECT モデル,金額 FROM [" & uQuery.Name & "]")

WHERE句も使えます。

.CommandText = Array("SELECT * FROM [" & uQuery.Name & "] WHERE モデル='LOLO'")

CommandTextを書き換えることにより、このようにフィルタリングを行うことが可能になります。


再読み込み

単純な再読み込み

データをファイルから再読み込みするだけであれば、次のようにクエリーテーブルの Reflesh メソッドを実行するだけで済みます。

'再読み込み
Public Sub uReflesh()
    Dim uWS As Worksheet
    Dim uList As ListObject
    
    Set uWS = Worksheets("Sheet1")
    Set uList = uWS.ListObjects("任意のテーブル名")
    
    uList.QueryTable.Refresh BackgroundQuery:=False
End Sub

CSVファイルに行を追加したあと実行すると、次のように読み込まれます。

CSVファイルを変更して再読み込み

CSVファイルのパスを変更する必要があるのなら、Formula プロパティの再設定が必要になるでしょう。

'CSVファイル変更
Public Sub uChangeCSV()
    Dim uQuery As WorkbookQuery
    Dim uCSVPath
    
    Set uQuery = ThisWorkbook.Queries("任意のクエリー名")
    
    uCSVPath = "C:\NKTemp\Sample2.csv"
    
    uQuery.Formula = _
        "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _
            "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _
        "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _
        "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _
            "{{""購入日"", type date}, {""金額"", Int64.Type}}) " & _
        "in uChangedType"
    
    uReflesh
End Sub

Sample.csvをSample2.csvとしてコピーして、LOLOを「変更後」と書き直し、実行した結果。


Sample2.csv の内容が読み込まれています。

このように、初回にクエリーとテーブルを作った後は、比較的短いコードでテーブルのアップデートが可能です。

Formula("uSource") = のように、Csv.Documentの部分だけ指定して変更できれば、もっとシンプルに書けるのですが、そのような方法は見当たりませんでした。

クエリーの編集画面ではできていますが、あれはM言語の構文解析を行っているのではないかと思います。


リンクに注意

上の例では、CSVファイルとテーブルはリンクされています。

そのため、ファイル内容を更新すれば、テーブルの内容まで更新される場合があります。

特に「すべての更新」を行うと、VBAで作成したクエリーもアップデートされてしまうかもしれません。

クエリーや、クエリーテーブルを削除することにより、更新を停止できるのではないかと思います。まだ、試していませんが。


誤訳

以前から、クエリーの編集画面に表示される「昇格されたヘッダー数」が意味不明でした。コードを読んでも、これ数値じゃないんじゃね？と思っていました。

今回、調べたところ、元の英語は PromotedHeaders でした。なので、昇格されたヘッダー、もしくは昇格された見出し、と訳すのが正しいでしょう。


おわり

今回説明したような方法を使うと、VBAからCSVをいかようにでも読み込むことが可能になります。

ただ、定型業務で、CSVファイルだけその都度他のファイルに変更したいという場合には、もっと簡単な方法もあります。

VBA クエリーで読み込むCSVファイルを簡単に切り替える


これまで、QueryTableを使ってCSVを読み込んでからテーブルに変換したり、PowerQueryの読み込みをマクロ記録したコードを手直しして対応してきたため、CSVファイルの処理は憂鬱でした。

今回、一念発起してクエリーとQueryTableとListObjectを調べてコードを整理してみました。ようやく容易にCSVを処理できそうです。


2022/10/23 追記　使いやすいようにパラメーターをConstantにしてまとめました。


コメント

コメントを投稿

アクセス数の多い投稿

セキュリティ対策ソフトのノートンが詐欺ソフトまがいになってしまってショック

-
イメージ
夜の10時に風呂に入っていると、80近くになる実家の母から、突然ノートンの表示が出たけどどうすれば、という連絡がありました。表示されたメッセージは次の通りです。 ！　お使いのパソコンで462件の破損されたレジストリが検出されました。パソコンをクリーンアップしてパフォーマンスを向上させましょう。 レジストリの問題は、パソコンの速度低下、フリーズ、さらにはクラッシュの原因となります。ノートンTMユーティリティーズ　アルティメットを入手して、レジストリの問題の解決とパソコンのパフォーマンス向上に役立ててください。 ノートンがこんな、レジストリが壊れているからと不安をあおって、別の製品を買わせる詐欺ソフトまがいのメッセージを出してくるとは。 親曰く「レジストリが破損て驚いた」（原文ママ）。わけのわからん高齢者に無用なストレスを与えています。 大体レジストリーとか言って、それの意味が正しく分かる一般人がいるか！（怒） ブロードコムに買収された法人向けのシマンテックはひどいことになっているけれど、個人向けのノートンライフロックは大丈夫みたいと思っていたら、こんなことになるとは。 もう、セキュリティ製品を買うのはやめて、Windows に最初からついてくる Microsoft Defender でもいいのかもしれないと思う今日この頃です。そのほうが安定してるし、こういう余計な問題も起きないし。 2022/9/1 追記　Defenderに切り替えてみました。 さらばノートン 2022/01/13 追記 悩んでいる方がいらっしゃるようなのでこのメッセージの解説をしておこうかと思います。 レジストリーとは Windows や 各種アプリ（ソフト）は設定などをWindowsが管理するデータベース（ファイル）に保存するものが多いです。そのデータベースがレジストリーです。 ソフトをインストールするときや、設定を変更する際などに書き換えられます。 設定等が保存されているため、これが壊れるとWindowsやアプリの挙動に支障をきたす可能性があること自体は確かです。 どうして壊れるのか レジストリーが壊れる原因は色々考えられます。 Windowsやアプリの不具合で作成や更新に失敗した 作成や更新中にWindowsやアプリが異常終了して中途半端になった データを保存する部品（SSDやHDDなどのドラ...
続きを読む

ZIPファイルを開こうとすると、展開を完了できません、と言われる

-
イメージ
 ある日、職場の方から突然ZIPファイルを開けなくなりましたという連絡がありました。エクスプローラーでファイルを開いて、圧縮されている中のファイルをダブルクリックすると、「展開を完了できません。展開先ファイルを作成できませんでした。」というメッセージが表示され、ファイルの中身が表示されません。 7zipからは開くことができるので、Windows 10標準のZIP機能がおかしいらしい。 ネットで検索して一時ファイルを消してみたり、SFC /SCANNOW を実行して見たり、色々やってみたけれど効果なし。 海外サイトで Windows cannot complete the extraction. The destination file could not be created. などで検索してもいい情報が見つかりません。 途方に暮れつつ、ZIPファイルを右クリックして、「すべて展開」を選んでみたところ、エラーコード「0x80004005」が出ました。 それで検索したところ、次のページがヒットしました。 Windows 10でZIPファイルの解凍エラー（0x80004005）が発生したときの対処方法 問題のZIPファイルの作成には7zipを使っていたので、圧縮時の設定を見てみたところ、確かに圧縮方式がデフォルトのdeflateではなく、BZip2になっていました。 もとのdeflateに戻して、再度圧縮して標準ZIP機能で開いたところ、あっさり開くことができるようになりました。なんと。 というわけで、ZIPファイルが開けない場合には、元のツールの圧縮方式を疑ってみる必要があります。取引先から送られてきたものは、頼み込むか、7zip等で開くしかなさそうです。 また、無駄な時間を使ってしまった。 ちなみに、暗号化方式がZipCryptでないとやはりWindows 標準のZIP機能では開けないそうです。
続きを読む

Excel 2019 クエリが原因で日本語入力の一文字目が勝手に確定する

-
イメージ
既存ファイルにシートを追加してセルに文字を入力していったところ、なぜか一文字目が勝手に確定されてしまうという現象に遭遇しました。 一文字目が勝手に確定される セルに、例えば「支払い」と入力しようとすると、shiharaiのsを入れた時点で確定されてしまい、「s いはらい 」のようになってしまいます。 消しては入力やり直しなので異常に入力しづらい。大量に入力する必要がある方は絶望を感じるでしょう。 クエリが原因 新しいファイルでは問題ないのでどうやらファイル依存の問題らしいということがわかりました。 新しいファイルを作って、問題のファイルにあるシートを一つずつ移動していったところ、あるシートを移動したところで新しいファイルでも発生することがわかりました。 それは銀行のサイトにある為替レートのページを参照しているクエリが含まれるシートでした。 そこで、「クエリ」と「確定」で検索したところ、なんと既に先人が原因を特定して レポート してくれていました。 それによると、「データ」メニューの「クエリと接続」グループにある「クエリと接続」をクリックして表示される画面で、クエリが選択されたまま保存されていると発生するということでした。 実際に問題のファイルを開いてみると、確かにクエリが選択された状態になっていて、選択を解除したところ改善しました。こりゃわからんわ。 新しいファイルでクエリを作っても簡単に再現可能です。 PowerQueryはExcel 2016で標準搭載されたようなので、通常はExcel 2016か2019で遭遇する問題だと思います。 回避方法 選択を解除して保存 私がこの問題に遭遇したときは、クエリの選択を解除して保存するだけで解決し、次回開いた時にも再現することはなかったと記憶しています。 ところが、今試してみると、選択解除して保存しても、次回開くとまた選択された状態になっていて、文字入力に支障が出ます。バージョンの違いかと思い、職場のPCでも試してみましたが同様です。私の記憶違いだったのか、その後のバージョンアップで悪化しているのか… クエリと接続画面を表示 現状では、「クエリと接続」画面を表示させると（選択を解除しなくても）現象は収まります。 ファイルを開くときにマクロで自動的に選択を解除する方法も考え...
続きを読む

Excelのテーブルに行や列を挿入する際のエラー

-
イメージ
 Excelのテーブルに行や列を挿入しようとすると、「この操作を実行するとワークシート上のテーブル内でセルが移動されてしまうため、この操作は行われません」というエラーメッセージが表示され、失敗する時があります。 長年このエラーの原因が不明でしたが、あるときどうしても解決する必要があって調べたところ、ようやく原因がわかりました。 現象 図1の場合、上のテーブルに行を追加しようとするとエラーが発生します。 図1 図2の場合は、左側のテーブルに列を追加しようとすると発生します。 図2 テーブルに行や列を追加しようとすると、そのテーブルの範囲だけが拡張されます。 どういう事かというと、図1の上のテーブルの場合、行を挿入すると、B, C, D列のみセルが追加され、A列やE列は変化がありません。 そうすると、下のテーブルは、列1、2、3だけ下にずれることになり、テーブルが壊れてしまいます。そのため、最初のエラーメッセージが表示されるという事です。 図2の場合も同様で、左のテーブルに列を追加しようとすると2行目から5行目までだけが右にシフトしようとします。これもまた右側のテーブルが壊れてしまうため、エラーが起こるというわけです。 回避策 テーブルに行や列を追加するのではなく、シートに対して行全体、列全体を追加すれば、図1の下のテーブルや図2の右のテーブルも全体的に移動するのでエラーは発生しません。 この場合、人間が手動で追加する場合はいいのですが、VBAを使って、テーブルに行や列を追加する場合は、シートに対する行や列の追加が必要になるためやっかいです。 一つのシートにテーブルを複数追加する場合は、このような問題が起こらないようにレイアウトを考える必要があります。 私の場合は、仕方なく、図1のパターンでは、列が少ないテーブルを下に配置することにしました。これだと、上のテーブルを追加しても、下のテーブルは全体的に下にずれるため問題ありません。 そういうレイアウトにできない場合は、仕方がないので、行全体、列全体を追加することになります。
続きを読む

オカムラ家具のOAチェアー、コンテッサを分解清掃

-
イメージ
オカムラ家具の高級オフィスチェアー、コンテッサを使っています。ジウジアーロがデザインしたという当時としては未来的なフォルムでした。 以前のアパートに住んでいた時に買ったのでヘタするともう20年くらい使っているんじゃないかと思います。 当時吉祥寺にあった大塚家具で、アーロンチェアーと座り比べてこれにしました。多分10万円くらいで入手したと思います。 掃除が厄介 コンテッサは座面の下にほこりがたまりやすく、そして取りにくいという問題があります。そのため、ある程度の掃除しかできませんでした。 そして、なにより座面。長年の使用で汚れてしまっているので何とかしたいと思いました。しかし、座面を外すのが容易ではないと思っていました。 そろそろ買い替えようかな、とも思いましたが、今、後継モデルのコンテッサ セコンダを買うと20万円位するみたいです！！ ここはいっちょ気合を入れて掃除してみることにしました。 分解 前はそう簡単には外せないと思いましたが、今回トライしたところ、意外に簡単に外せることがわかりました。使った工具は次の三つです。 左からプラスドライバー、6mm六角レンチ、4mm六角レンチ レンチは背もたれを外すために使います。 座面はなんとプラスドライバーのみで外すことができました。 前の2か所のネジは簡単に外せますが後ろが外せないと思っていました。ドライバーがまっすぐ入らないため。しかし、リクライニングをすればネジが表れて、後ろの2か所も外せるようになりました。 赤丸の部分を裏から外します ネジはかなり固いので、上の写真のように径が大きく、グリップがゴムのドライバーを使うとよいでしょう。粗悪なものを使うとネジの頭がだめになると思います。 ドライバーを真っすぐセットできない時には、座面を前後にスライドしましょう。 レンチはサイズが合えば何でも良いと思います。写真のように長い方が楽に回せると思いますが。 オカムラ家具のコンテッサ用の六角レンチを調達 背もたれを先に取ってしまうと、リクライニングをするのが少々面倒です。先に座面を外した方が良さそうです。 背もたれの上のボルトを外すとき、ヘッドレストがついている場合は落下に注意しましょう。ヘッドレストを押さえながら、外した方が良いと思います。 座面と背もたれを洗ったけれど はずした座面と背もたれを風呂にもっていってシャワーを掛ける...
続きを読む

突然滅茶苦茶遅くなったPCがWindows Updateのキャッシュクリアで復活

-
イメージ
職場のPCが滅茶苦茶遅くなって操作できないというので色々試していった結果Windows Updateのキャッシュクリアで解決しました。 とにかく遅い PCを起動してサインインしてしばらくの間はそれなりに動作するのですが、時間の経過につれて動作が重くなってくる感じです。 特にタスクバーの反応が遅くなります。 タスクマネージャーとかコンピューターの管理を開いても、ずいぶん経って忘れたころに開いてくる感じです。 ひどくなってくると、もう全然反応がありません。 サインアウトや再起動がうまく行かない場合もありました。 ただし、何もいじらないと後からでも動作する場合もありました。 システムの復元も効果なし 12/3に発生して、Windowsの機能「システムの復元」を試みると12/1と、12/3の9時くらいに復元ポイントがありました。9時くらいのものが怪しかったので復元を行いましたが、残念ながら効果がなく、しかも12/1の復元ポイントまで消失しました。 結果的にはプログラムの問題ではなくデータの問題だったため効果がなかったということになります。システムの復元の最後にユーザーのデータには影響を与えませんみたいなメッセージが表示されることからも、そうなのでしょう。 今回の教訓としてはポイントが消えるかもしれないので復元するなら思い切って前の日にさかのぼったほうが良いという事でした。 [Windows 10] システムの復元を実行する方法を教えてください。 　富士通 アンインストールもうまく行かない たまたまシマンテックのライセンス切れが重なったため、シマンテックが原因かと思いアンインストールを試みました。しかし、途中までは進むものの取り消されてしまい削除できません。 ブロードコム（シマンテックを買収）から削除ツールCleanWipeを入手して削除してみましたが、復旧せずシマンテック以外が原因だと思いました。 Cryptographic ServicesとWindows Update タスクマネージャーでCPUの使用率を見ていると、100%で遅いというのではありませんでした。使用率は高くても数十％。低い時は十数％くらいでしょうか。 見ていると周期的にCryptographic ServicesがCPUを使用し、高い時は40%くらいになります。そして、たまにWindows Updateが...
続きを読む

ChatGPTが日本語からVBAのコードを生成できてたまげる

-
イメージ
最近噂のChatGPT。大学受験問題も解ける賢いAIだとか。そこで私も試してみました。 指示は、「単価、数量、金額の列を持つExcelのテーブルを一行ずつ読み、金額を計算して更新するVBAを作りなさい。ListObjectとFor eachを用いなさい」です。 結果は次の通り正しく回答されました。 マジかと思いました。 自然言語からコードが生成されるだけで驚きですが、金額を計算しなさいと言っただけで、単価と数量を乗算しています。 試しに実行 上の画面のテキストをコピペしたものです。 ExcelのVBAでは、ListObjectを用いることで、Excelシート上のテーブルを操作することができます。以下は、単価、数量、金額の列を持つExcelのテーブルを一行ずつ読み、金額を計算して更新するVBAの例です。 Sub UpdateTable()   Dim tbl As ListObject   Dim row As ListRow      ' テーブルを取得します   Set tbl = ActiveSheet.ListObjects("Table1")      ' テーブルの各行を取得します   For Each row In tbl.ListRows     ' 単価と数量から金額を計算します     Dim unitPrice As Double     Dim quantity As Integer     unitPrice = row.Range(1, 1).Value     quantity = row.Range(1, 2).Value     row.Range(1, 3).Value = unitPrice * quantity   Next row End Sub 上記のVBAを実行すると、単価と数量から金額を計算し、テーブル内の金額の列が更新されます。 ListObjectを用いることで、Excelシート上のテーブルを簡単に操作することができるようになります。また、For Eachステートメントを用いることで、テーブル...
続きを読む

Windows セキュリティーのビックリマークが消えない

-
イメージ
ノートン削除でWindowsセキュリティにビックリマーク 実家のノートン360をアンインストールしたところ、タスクトレイにある Windows セキュリティーの盾（シールド）型アイコンに「！」が表示されるようになりました。Windows 11です。 マウスポインターを持っていくと、「Windows セキュリティ - 処置をお勧めします」と表示されます。 ところが、クリックしてセキュリティの概要を開いてみても、問題なさそうです。 ノートンを再インストールするとグリーンのチェックマークになりましたが、アンインストールするとまた黄色のビックリマークになります。 私のPCはアンインストールしても問題なかったのでPC次第なのでしょう。 解決方法 実家のPCは次の方法で解決しました。 Windowsのスタートボタンを押します。（左の四角） 「設定」を開きます。 「プライバシーとセキュリティ」を選び、「Windowsセキュリティ」をクリックします。 「アプリとブラウザーの制御」をクリックします。 「評価ベースの保護」が、有効になっていなかったので有効にして、保護設定をすべて有効にしました。 有効後 これで盾アイコンのビックリマークは消えました。 Web閲覧時に危険 SmartScreen（Web閲覧が保護）が有効になっていなかった可能性があるので、これは確認してよかったです。 それにしても、タスクバーのアイコンから開いたWindowsセキュリティの画面に「アプリとブラウザーの制御」が表示されていないのはバグではないかと思います。以前は出ていたような気がします。 2022/10/9 追記 今、タスクバーの盾アイコンをクリックしたら、「アプリとブラウザコントロール」が表示されるようになっていました。やはりなにかのバグだったのでしょう。 Windows 11 は22H2にアップデート済みです。 2022/11/26 追記 別件で知人のPCのメンテナンスを行ったところ、この現象が発生していたので、早速、上の手順で解消しました。 ESETがインストールされていましたが、期限切れだったのでアンインストールしてDefenderに切り替えました。アンインストールによって生じたのか、元々こうだったのか記憶があいまいです。 Windows 11 は22H2になっていませんでした。 スマートアプリコン...
続きを読む

Power Automate Desktopでブラウザでダウンロードしたファイルを処理する

-
イメージ
Power Automate Desktopを使って自動化する作業の一つはWebサービスだと思います。例えば私は銀行の明細照会や送金等の自動化を試みています。 そういう作業をしていて必要になって来るのがダウンロードしたファイルの処理です。例えば口座明細ファイルを保存するとか請求書を印刷するとかです。 ダウンロードされたファイル名がわかっているのであれば簡単ですが、実際には毎回違うなんだかよくわからない暗号コードのようなファイル名が付けられて落ちてくるという事は結構あります。 ファイル名が確定しなければアクションで指定してみようがないので困ります。今回はそういうファイルの処理方法について書いてみたいと思います。 画像はクリックすると拡大できます。 ダウンロードアクションを使用する Power Automate Desktopにはそのものズバリの「Webからダウンロードします」や「Webページのダウンロードリンクをクリックします」が用意されています。 しかし、残念ながらこれはChromeではつかえません。実行するとエラーが発生し「Chrome を使用したファイルのダウンロードはサポートされていません。オートメーション ブラウザーの使用を検討してください」と言われてしまいます。 そうなんだ、では、とEdgeやIE（Internet Explorer）を指定しても同様です。オートメーションブラウザーってなんなのと思ったら、IEの起動モードの一つでした。 それではと、オートメーションブラウザーを使ってみるとオートメーション用にすべてを削ぎ落した画面が表示され、これでは動かないサイトがありました。 また、IEのサポート終了が来年2022年6月に迫っているというのもひっかかります。 ダウンロードフォルダーを空にする ではどうするかと検索してみると、次のページではダウンロードする前にダウンロードフォルダーをクリアするという荒業を使っている方がいます。 Power Automate Desktop：ファイル名がわからないファイルをコピーする方法 いやこれ、私なんかはダウンロードフォルダーをデスクトップに設定しているので絶対に使えない方法です。 ブラウザのダウンロードフォルダーを一時的に作ったフォルダーに変更して元に戻すなんて言う方法も考えられますが、処理途中でエラーが発生するとそのま...
続きを読む

Excel VBAからODBCを使ってデータを簡単に取得する

-
イメージ
VBAから ADO を使わずにODBC経由でデータベースにアクセスする方法です。 サンプルデータベース 次のようなアクセスのデータベースがあるとします。 テーブル名は「商品マスター」 カラム名は、ID, 商品名, 金額 保存してあるファイルは C:\Test\SampleDB.accdb DSNの用意 このファイルにアクセスするための DSN を作成します。（作成方法は後述） VBAの実行 Excel でシートを開き、次のプロシージャーを実行します。これはテーブルを作成するプロシージャーなので、最初に一回実行するだけです。 Private Sub uCreateTableWithQuery()     Dim uWS As Worksheet     Dim uList As ListObject          Set uWS = ActiveSheet     Set uList = uWS.ListObjects.Add( _         SourceType:=xlSrcQuery, _         Source:="ODBC;DSN=NK Test", Destination:=Range("A3"))          uList.DisplayName = "ExcelProductMaster" End Sub DSN=にDSN名を指定します。この例では、NK Test。 Destination はテーブルの左上を配置するセルです。この例ではA3セル。 DisplayName はテーブル名を指定します。この例ではExcelProductMaster。 実行すると次のようにテーブルが作成されます。テーブルはQueryTableに接続され、QueryTableはDSNを利用可能です。 次のプロシージャーを実行します。 Public Sub uRefleshQueryTable()     Dim uList As ListObject     Dim uSQL As St...
続きを読む