Excel 条件付き書式について(2) 数式を使用した条件付き書式

Excel PowerQuery エディタのオススメ設定

Excel VBA PowerQueryも含めリンクを解除してシートを切り離すマクロ

2021年7月28日

Excel マクロ、VBA

はじめに

以前の記事で、Excelブックの中のシートを、他のExcelファイル等とのリンクを切り離した上で、新規Excelブックにコピーするマクロを取り上げた。

Excel VBA リンクを解除するマクロ→リンク解除した上でシートを切り離す個人用マクロブックに組み込んで使おう

2021年7月28日

ただ、PowerQueryを多用するようになった今、同記事に書いただけのマクロでは不足になった。
他愛のない数行のコードを追加するだけで良いのだけど、ちょっとした記事リメイクと、自分の備忘のために、改良したマクロの記事を書いてみる。

PowerQuery入りExcelシートのリンク解除をしたい

PowerQueryを使ったExcelファイルの大半は、他ファイルへのリンクを使用して作られる。

他人にファイルを渡す場合は、そういうリンクが残ったままの状態で渡すと、何か操作したら「リンク元が見付からない」というエラーが出るなどトラブルの元だ。

自分だけで利用する場合も、あれこれ別ファイルにアクセスしたり、そのデータを加工したりするPowerQueryのロジックなんかはもう要らないから、単なるデータのテーブルにしてしまって身軽にしたいということは多い。

以前の記事で書いたマクロは次の通りだ。

Sub breakLinks()
‘リンクを解除
    Dim wb          As Workbook
    Dim vntLink     As Variant
    Dim i           As Integer
    
    Call appSet ‘描画省略、手動計算設定など
    Set wb = ActiveWorkbook
    
    vntLink = wb.LinkSources(xlLinkTypeExcelLinks) ‘ブックの中にあるリンク
    
    If IsArray(vntLink) Then
        For i = 1 To UBound(vntLink)
            wb.BreakLink vntLink(i), xlLinkTypeExcelLinks ‘リンク解除
        Next i
    End If
 
    Call appReset ‘描画などの設定を元に戻す
End Sub
 
Sub appSet()
‘マクロ処理中に、描画など余計なものを省略して高速化
    With Application
        .ScreenUpdating = False ‘描画を省略
        .Calculation = xlCalculationManual ‘手動計算
        .DisplayAlerts = False ‘警告を省略。
        ‘.EnableEvents = False ‘DisplayAlertsよりこちらを設定した方が良い？
    End With
End Sub
 
Sub appReset()
    ‘描画などの設定を通常通りにリセット
    With Application
        .ScreenUpdating = True ‘描画する
        .Calculation = xlCalculationAutomatic ‘自動計算
        .DisplayAlerts = True ‘警告を行う
    End With
End Sub

Sub breakLinks()

‘リンクを解除

Dim wb As Workbook

Dim vntLink As Variant

Dim i As Integer

Call appSet ‘描画省略、手動計算設定など

Set wb = ActiveWorkbook

vntLink = wb.LinkSources(xlLinkTypeExcelLinks) ‘ブックの中にあるリンク

If IsArray(vntLink) Then

For i = 1 To UBound(vntLink)

wb.BreakLink vntLink(i), xlLinkTypeExcelLinks ‘リンク解除

Next i

End If

Call appReset ‘描画などの設定を元に戻す

End Sub

Sub appSet()

‘マクロ処理中に、描画など余計なものを省略して高速化

With Application

.ScreenUpdating = False ‘描画を省略

.Calculation = xlCalculationManual ‘手動計算

.DisplayAlerts = False ‘警告を省略。

‘.EnableEvents = False ‘DisplayAlertsよりこちらを設定した方が良い？

End With

End Sub

Sub appReset()

‘描画などの設定を通常通りにリセット

With Application

.ScreenUpdating = True ‘描画する

.Calculation = xlCalculationAutomatic ‘自動計算

.DisplayAlerts = True ‘警告を行う

End With

End Sub

しかし、PowerQueryを含むExcelシートを上記マクロで切離しても、「クエリと接続」ウインドウで確認すれば分かるがPowerQueryは削除されない。

このPowerQueryが1個くらいならDeleteボタンで削除すれば良いが、フォルダごと取り込む処理などではPowerQueryは多数できあがるので、手作業で削除したくはない。

PowerQuery エディターの基礎

2020年8月10日

新しいソースコード

そこで、元のマクロに、「クエリ」全般を削除する記述をちょこっと加えるだけで良い。

    Dim q As Object

    For Each q In wb.Queries
        q.Delete
    Next q

Dim q As Object

For Each q In wb.Queries

q.Delete

Next q

ただこれだけ。これを追加して、Excelシートをリンク解除した上で切り離すマクロは下記のようになる。

まあ他にも、条件付き書式とか入力規則、名前定義などに含まれるリンクも解除しようとなると、複雑になっていくのだけど、ひとまず手軽にやれるのはこんなところ。

Sub breakLinksAndSheetCopy()
    'リンクを解除してシートコピー
    ActiveWindow.SelectedSheets.Copy '選択されたシートをコピー
    
    'リンクを解除
    Dim wb          As Workbook
    Dim vntLink     As Variant
    Dim i           As Integer
    
    Call appSet '描画省略、手動計算設定など
    Set wb = ActiveWorkbook
    
    vntLink = wb.LinkSources(xlLinkTypeExcelLinks) 'ブックの中にあるリンク
    
    If IsArray(vntLink) Then
        For i = 1 To UBound(vntLink)
            wb.BreakLink vntLink(i), xlLinkTypeExcelLinks 'リンク解除
        Next i
    End If
    
    'PowerQueryなどクエリを削除
    Dim q As Object

    For Each q In wb.Queries
        q.Delete
    Next q
    
    
    Call appReset '描画等再開
End Sub