【Excel VBA】Power Qurry でCSVを読み込む操作を簡略化出来ないでしょうか？

外部のcsvファイルとリンクさせた集計表を作成しました。
csvのレコードが日々増加するので、
同じファイル名でcsvを上書きする事によって集計の内容も更新されるようにしています。

上記のCSVのリンクの仕方ですがこれが正しいのか不明です。。
----------
Excel上部のメニューから、
データ→データの取得→ファイルから→テキストまたはcsvから
→ファイルを選択→データの変換→
Power Qurry エディターが起動→左上の「閉じて読み込む」
これで「クエリと接続」で読み込みます。
----------
こうしておくと、CSVを上書きすると集計結果にも反映されて便利なのですが、
問題は運用についてです。

できた集計表を別の人(PC)に配布すると、
当然ですが自分のPC上のCSVとリンクしているため、他のPCではファイルパスが異なるため、
リンク先を変更しなければいけません。

その際は、
クエリと接続からクエリをダブルクリック
データソース設定→ソースの変更→参照→OK→「閉じて読み込む」
という手順を踏んで別のPCに対応させています。

集計表を改訂する度にこの操作が大変です。
VBA等で簡略化出来ない物でしょうか？

No.3 回答者： Qchan1962 回答日時： 2021/04/20 19:59

こんばんは、

時間が空いてしまい申し訳ありません。
Power QurryはExcelのテーブル機能も使え大変便利ですね。
＃１のコードはPower Qurryではなく、QueryTableです。
CSVのデータを読み込み時にに加工しないなら良いかもと思いました。
誤解をさせてしまったかもしれません。すみません

私もPower Queryやスピり関数など比較的新しいものは、あまり知りません。最近はOfficeをあまり使わなくなてしまったので、、
前回も　https://oshiete.goo.ne.jp/qa/12285592.html
調べて回答したと言う形です。

もし、決まった処理を行うのであれば、
マクロの記録が出来るようですので、一通り記録して、整理してみるのが早いかもしれません。
https://excel-ubara.com/excelvba4/EXCEL_VBA_408. …

CSVのPath設定は別プロシージャで作成してCall するような感じで

No.2 回答者： Qchan1962 回答日時： 2021/04/18 23:42

#1です。

野暮用で少し時間がありてしまいました。
明日から出先で2、3日夜のわずかな時間だけが、拝見するタイミングになってしまいます。
一連のご質問の内容から、ご質問者様のスキルを想像して回答いたします。

＞そのパスを読みに行くようにするには、頂いたソースの Folder_Path の式の加工だけですか？
そのような形で取得できると思います。
正し、サンプルコードの方法に変更する場合は、CSVのデータが文字コードなどで不具合が出ないか確認してください。もし不具合があるようでしたら、QueryTableのプロパティなどを調べ対応してください。

＞ネットワークパスかローカルかによって SpecialFolders に切り替えるのは難しいですよね？
Dir関数でエラーを返して条件を設定すれば、さほど難しいとは思いません。

＞「CSVのファイルを指定してください。」とメッセージが出てファイル選択できるようにするのは難しいでしょうか？

いいえ、これもメッセージを出しファイルピッカダイアログなどを表示すれば、容易に思います。

＞ダウンロードしたCSVは「report1618715021460.csv」というファイル名で数字部分が毎回ランダムなので、固定名にリネームしないといけないですよね？
更新ボタンクリックした時に、都度ファイルを指定できれば良いのですが・・・

＞固定名にリネームしないといけないですよね？
これは、ファイル名で指定した場合はそのようになりますが、
例えば、FileDateTime 関数やCreateObject("Scripting.FileSystemObject")のDateLastModifiedなどを使い、フォルダ内のCSVファイル全ての更新日時（又は作成日時）を取得して新しいCSVを取得する方法も出来ると思います。（同時にある程度古いファイルを削除する事も可能です）

私が過去実際に作成したツールでは、CSVを取得して作業、閉じる時（更新時）に使用しているPCにバックアップ用CSVを保存していました（上書きCSVマスター）これも行数制限で古いデータは自動削除）

＞(その場合、A1セルにパスをを入れる案は無しになってしまいますね)
A1セルに保持するのは、名案だと思います。セルは優秀なメモリなので活用方法があると思います。

＞最後の疑問は、私がPower Qurry の更新をするときは以下のソースでやってましたが、
頂いたソースで行うのと同じ意味でしょうか？

意味は違います。
このコードは、接続の更新です。
PowerQueryの場合、自動接続を一旦切断しないと更新インスタンスが返って来ないので自動接続を無効にして更新して、元の設定に戻しています。
そのコードで接続されているデータの更新が可能ですが、

回答させていただいたサンプルは、CSVファイルそのものを取得するコードです。取得時に接続を維持していますので更新でRefresh出来ると思います。

具体的なコードを示せていませんが、キーワードで調べてみてください。

追記：QueryTableは、CSV取得は高速ですべてのデータを取得するには、良いのですが、データの追加には適していません。（そのように記憶しています）

この回答へのお礼

ありがとうございます。お忙しいところ申し訳ございません。

No.1で頂いたソースがうまく動作しませんでした。
これは私のPower Qurry の理解が低いためです。ソースは悪くありません。

集計表の「CSV」というシート名にcsvを取り込んでいます。
"piyo"を"CSV"にして実行したら、シートが真っ白になりました。
私は、Power Qurry を完全に理解しきっていません。ただ、便利だなと思って使い始めたところです。
私の場合、まず外部csvをインポートします。
すると、自動的に行が緑と白のストライプになって表示されます。
例えばC列とD列の間に新規列を挿入(D列になります)して、
D2セルに「=B2+C2」と数式を入れると、勝手に最下行まで数式がコピーされるのです。(便利な理由１)
同様に複数の列に新規列挿入して様々な関数で加工しています。
そんな状態で、CSVを読み込み直すと(データ→すべて更新)、
飛び飛びの加工した列を除き、インポートした列だけの値が最新に置き換わるのです。(便利な理由２)
しかもレコード数が増加しても自動的に関数が最下行まで行き渡ってくれるのです。(便利な理由３)

正しく理解しないまま上記の便利さだけを利用している状態です。
多分その辺が、Qchan1962さんには伝えられていなかったための結果と認識しています。
自分の事を人に聞くのもおかしいのですが、私の状況を解説していただけると幸いです。

お礼日時：2021/04/19 10:48

No.1 回答者： Qchan1962 回答日時： 2021/04/18 10:36

こんにちは、

＞VBA等で簡略化出来ない物でしょうか？
出来ると思いますが、
＞他のPCではファイルパスが異なるため
これが問題だと思います。なぜ、違うパスにCSVファイルを置くのでしょう？
同じパスにCSVファイルを置けば解決できるのでは？

＞同じファイル名でcsvを上書きする事によって
上書きはどなたがどのように行うのでしょう？VBAなどで自動？
これもパスが同じでないと困るのではないでしょうか

VBAの場合、どこからどこまでをVBAで行うのか、と言う事になるかな

＞データソース設定→ソースの変更→参照→OK→「閉じて読み込む」
という手順を踏んで別のPCに対応させています。

パスが変わるのであれば、VBAで簡略化するのは出来ないと思いますので
パスの変わらない場所にCSVを配置するように指示すれば良いと思います。
（ＶＢＡなどでパスを指定しても良いですね）

同様に、手順を踏んで別のPCに対応する　と言う事になりそうですが、

参考まで
ご質問をよく理解していないかもですが、
私的には、CSVを読み込んで処理をする場合、
下記の様にCSVのデータを取得して、加工、集計などVBAで行う事が多いです。
CSVの内容が不明なので暫定ですが、
QueryTableでCSVを読み込むコード（.Deleteをコメントにして接続を維持）

ブックを開く時に実行
Private Sub Workbook_Open()
Dim Folder_Path As String
Folder_Path = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\" & "hoge\huga.csv"
Const Sheet_Name As String = "piyo"
Call in_csvQuery(Folder_Path, Sheet_Name)
End Sub

’csvファイルパスと出力シート名で必要に応じて呼び出す
Private Sub in_csvQuery(TargetFile As String, Sheet_Name As String)
Dim qt As QueryTable
On Error Resume Next
Worksheets(Sheet_Name).Cells.Clear
Set qt = Worksheets(Sheet_Name).QueryTables.Add _
(Connection:="TEXT;" & TargetFile, Destination:=Range("A1"))
With qt
.TextFilePlatform = 932
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.RefreshStyle = xlOverwriteCells
.RefreshPeriod = 30
.Refresh
'.Delete 接続を維持
End With
End Sub
エディターで加工せず読み込むだけなら

CSVのパスはExcel配布時にCドライブ直下、デスクトップやマイドキュメントなど、いわゆるSpecialFoldersで取得できるフォルダ内に指定したフォルダ名でフォルダを作りその中に入れます。
配布時、設定用のVBAなどを使って意図する場所に配置する事もあります。

この回答へのお礼

ありがとうございます！

頂いた回答を見ながら、改めて考えてみました。
デスクトップに作ったCSVフォルダに入れる、というルールは統一しています。
C:\Users\■■■■■\Desktop\csv
しかしユーザフォルダ名が全員違っているので統一出来ないと思っていました。
ですが、SpecialFolders で取得すれば統一化出来るんですね！

もう一つは、ネットワークフォルダに置く事ですね。
例えば、Power Qurry が反映するシート名は「CSV」、
更新ボタンを設置してあるシートは「ピボット」ですが、
「ピボット」シートのA1セルに「\\192.168.5.5\public\abc\def\csv\」とパスを入力しておいて
そのパスを読みに行くようにするには、頂いたソースの Folder_Path の式の加工だけですか？
ネットワークパスかローカルかによって SpecialFolders に切り替えるのは難しいですよね？
「CSVのファイルを指定してください。」とメッセージが出てファイル選択できるようにするのは難しいでしょうか？

また、
ダウンロードしたCSVは「report1618715021460.csv」というファイル名で
数字部分が毎回ランダムなので、固定名にリネームしないといけないですよね？
更新ボタンクリックした時に、都度ファイルを指定できれば良いのですが・・・
(その場合、A1セルにパスをを入れる案は無しになってしまいますね)

最後の疑問は、私がPower Qurry の更新をするときは以下のソースでやってましたが、
頂いたソースで行うのと同じ意味でしょうか？
----------
For Each objConnection In ThisWorkbook.Connections
bBackground = objConnection.OLEDBConnection.BackgroundQuery
objConnection.OLEDBConnection.BackgroundQuery = False
objConnection.Refresh
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

お礼日時：2021/04/18 12:27