Excel VBA データ接続を使ったときに消去するマクロ

そのまえにまた英語しか出ないんだけど何をやっているんだ公式は

Egde+アドインかChromeで日本語が出る

Edgeが最適化といいながらこの状況。しかもたまに翻訳ができない。EdgeもChromeもページ単位だと失敗する場合がある。

ドキュメント検査とConnections Collectionを使う２つの方法がある

Document検査はRmoveDocumentInformation

Specifies the type information to be removed from the document information.

Docs/Office VBA Reference/Excel/Object model/Workbook object/Methods/Remove​Document​Information

Removes all information of the specified type from the workbook.

Syntax

expression. RemoveDocumentInformation( RemoveDocInfoType )

expression A variable that represents a Workbook object.

Parameters

Name	Required/Optional	Data Type	Description
RemoveDocInfoType	Required	XlRemoveDocInfoType	Type of information to be removed.

Docs/Office VBA Reference/Excel/Object model/Enumerations/Xl​Remove​Doc​Info​Type

Name	Value	Description
xlRDIAll	99	Removes all documentation nformation. (これはいっぺんに削ってしまうのでこれを使ったら犯罪とみなしてよいのでは...)
xlRDIComments	1	Removes comments from the document information. コメントの削除。必要なコメントも削るので使わない方がよい。
xlRDIContentType	16	Removes content type data from the document information.
xlRDIDefinedNameComments	18	Removes defined name comments from the documentation information.
xlRDIDocumentManagementPolicy	15	Removes document management policy data from the document information.
xlRDIDocumentProperties	8	Removes document properties from the document information.
xlRDIDocumentServerProperties	14	Removes server properties from the document information.
xlRDIDocumentWorkspace	10	Removes workspace data from the document information.
xlRDIEmailHeader	5	Removes email headers from the document information.
xlRDIExcelDataModel	23	Removes Data Model data from the document information.
xlRDIInactiveDataConnections	19	Removes inactive data connection data from the document information.
xlRDIInkAnnotations	11	Removes ink annotations from the document information.
xlRDIInlineWebExtensions	21	Removes inline Web Extensions from the document information.
xlRDIPrinterPath	20	Removes printer paths from the document information.
xlRDIPublishInfo	13	Removes the pubish information data from the document information.
xlRDIRemovePersonalInformation	4	Removes personal information from the document information.
xlRDIRoutingSlip	6	Removes routing slip information from the document information.
xlRDIScenarioComments	12	Removes scenario
xlRDISendForReview	7	Removes the send for review information from the document information.
xlRDITaskpaneWebExtensions	22	Removes task pane Web Extensions from the document information.

コード

Application.RemoveDocumentInformationを使う

ただし意味合いから接続が現在使用されている状態だと削除できない可能性がある。

Sub RemoveDataConnection()
On Error Resume Next
Application.RemoveDocumentInformation(xlRDIInactiveDataConnections)
End Sub

ConnectionsObjectを使う方法

Sub CallTheDeleteConnections()
  Call DeleteConnections(ActiveWorkbook)
End Sub

Private Sub DeleteConnections(varExcelWorkbook As Workbook)
Dim acCons As Excel.Connections, i As Long
On Error GoTo Err_Handle
Set acCons = varExcelWorkbook.Connections
If acCons.Count > 0 Then
For i = 1 To acCons.Count
  acCons.Item(i).Delete
Next i
End If
Exit Sub

Err_Handle:
  If Err.Number <> 0 Then
    Debug.Print Err.Number, Err.Description
    Err.Clear
  End If
  Exit Sub
End Sub

参考

ドキュメント検査をしたらプロパティを保存できなくなった excel 2007/2010 Moug