情シス担当の備忘録

VBA・労働法とか。

【Excel・M言語】Power Queryでソースのフォルダ内のExcelが開いている場合の対応

仕事で、とあるフォルダの中のエクセルブックの特定のシートの情報をまとめる必要がありました。しかも、まとめる対象の内容のエクセルブックは毎回変わるというものでした。

こんな時こそPowerQueryの出番…というわけで意気揚々と使用してたんですが、なんとフォルダ内のエクセルは別の人が作業中である可能性があることが判明。
誰かがフォルダの中のエクセルを開いていると、フォルダの中にエクセルのテンポラリファイルができるので、それも処理対象にしてしまってエラーになるみたいなんですよね。

エラーメッセージは「[DataSource.Error]別のプロセスで使用されているため、プロセスはファイル’(ファイルパス)’にアクセスできません。」ってやつです。

解決方法がないかとネットで調べていたら、やはり同じことで悩んでいる人がいました。↓
social.technet.microsoft.com

以下、ほとんどこの記事の和訳のような内容です。

【前提】
エクセルの「データ」タブから「フォルダ」をソースとしてクエリを作成している。
f:id:cha-hanman:20220121205900p:plain

この状態でソースのフォルダ内のエクセルを開き、クエリの更新をしようとすると、以下のようにエラーになる。
f:id:cha-hanman:20220121210030p:plain

【解決策】
Power Queryエディターの詳細エディターを開き、以下の一文を適当なステップ名で挿入する。

= Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))

この一文を入れることでテンポラリファイルを無視できるようになる。


【例】
①PowerQueryエディターで、こんな状態でテンポラリファイルも読み込んでしまっているクエリがあります。
f:id:cha-hanman:20220121212722p:plain

②詳細エディターを開きます。
f:id:cha-hanman:20220121213204p:plain

③適当なステップ名でテンポラリファイル回避の一文を入れます。
f:id:cha-hanman:20220121213443p:plain

④無事にテンポラリが回避できました。
f:id:cha-hanman:20220121213637p:plain

【番外編】
詳細エディターに抵抗がある場合のために、詳細エディターの操作をしない方法もあります。

①「Name」列で「~$」で検索をかけます
f:id:cha-hanman:20220121214226p:plain

②画面右「適用したステップ」の「フィルターされた行」の右側にある歯車みたいなアイコンをクリックします
f:id:cha-hanman:20220121214810p:plain

③「指定の値に等しい」を「次の値で始まらない」に変え、「~$1.xlsx」を「~$」に変更します。

f:id:cha-hanman:20220121215045p:plain
変更前
f:id:cha-hanman:20220121215154p:plain
変更後

④無事にテンポラリが回避できました。
f:id:cha-hanman:20220121213637p:plain


【おまけ】
エクセル2019なら、「エラーのあるファイルをスキップする」にチェックをいれるとテンポラリファイルが無視されるみたいです。2016だとチェックを入れても無視できないみたいです。
f:id:cha-hanman:20220121215508p:plain