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](https://docs.microsoft.com/ja-jp/office/vba/api/Excel.Workbook.RemoveDocumentInformation)

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](https://docs.microsoft.com/ja-jp/office/vba/api/excel.xlremovedocinfotype)

 

|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 |comments from the document information. |
|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を使う
ただし意味合いから接続が現在使用されている状態だと削除できない可能性がある。

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

###ConnectionsObjectを使う方法

```vb

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](http://www.moug.net/tech/exopr/0060029.html)