最新のMicrosoft 365ではeawfctrl.dllが削除されるようです。
詳細な情報が入りましたら、更新しますが、
とりあえず、2022/02/13にコメントアウトしました。
これより前に使用、引用している場合には訂正をお願いします。
前提
Microsoft Office 365 Solo かMicrosoft Office 2013 Professionalで
Word Excel Powerpoint Access Publisher Outlook が入っていること。
入っていないものは外してください。（特にAccess Publisher)
###GUID版を作りました
AddFromFileではあまりにも組み合わせが多いため、両方ともGUID版を作らざるを得ませんでした。
しかし、ご覧になればわかりますが、可読性が全くありません。
ただしよく使われるScripting.FileSystemObject、WSHは1.0で変わりません。
#Excel 2013 Later 64/32
##事前に2つの設定
###1.リボンに開発タグを表示
###2．ファイル>オプション>セキュリティセンターでセキュリティセンターでマクロの設定 すべてのマクロを有効 VBAオブジェクトモデルへのアクセスを信頼するにチェック
いれないと次のようなエラーが出ます。
このあと、参照設定でMicrosoft Visual Basic for Applications Extensibility 5.3
だけチェックを入れるというのがStackoverflowにあります。
しかしそれは不要です。
エクセルにおけるVBIDE.VBEの後期結合
https://codeday.me/jp/qa/20190608/962086.html
実はこれを使用することでプログラムによって設定することができます。
Excel Guid Beta version
Sub SetVBIDE53()
' VBIDE Microsoft Visual Basic for Applications Extensibility 5.3
' {0002E157-0000-0000-C000-000000000046} 5 3
' C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
' を強制起動させるマクロ
On Error Resume Next
Application.ActiveWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
End Sub
Sub RemoveExcelRefereces()
' Builtin 以外の参照設定を解除するマクロ
Dim xlProj: Set xlProj = Application.ActiveWorkbook.VBProject
Dim refs As VBIDE.References, ref As VBIDE.Reference
Set refs = xlProj.References
For Each ref In refs
If ref.Name = "VBIDE" Then refs.Remove ref
Next
refs.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
Set refs = xlProj.References
For Each ref In refs
If ref.BuiltIn = False Then
Debug.Print "Delete", ref.Name, ref.Description
refs.Remove ref
End If
Next
End Sub
Sub RefSetxl2010Later()
' For Microsoft Excel 2010 Later Beta Version
Dim xlobj: Set xlobj = GetObject(, "Excel.Application")
Dim xlProj: Set xlProj = xlobj.ActiveWorkbook.VBProject
Dim refs As VBIDE.References, ref As VBIDE.Reference
Set refs = xlProj.References
' VBIDEを念のために付け直す
For Each ref In refs
If ref.Name = "VBIDE" Then refs.Remove ref
Next
refs.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
On Error Resume Next
For Each ref In Application.ActiveWorkbook.VBProject.References
Debug.Print ref.Name, ref.Description, ref.Guid, ref.Major, ref.Minor, ref.FullPath
Next
For Each ref In refs
If ref.BuiltIn = False Then
If ref.Name <> "VBIDE" Then
refs.Remove ref
End If
End If
Next
On Error Resume Next
Const MSO16_Pro64 = "C:\Program Files\Microsoft Office\Root\Office16\"
Const MSO16_Pro32 = "C:\Program Files(x86)\Microsoft Office\Root\Office16\"
Const MSO15_Pro64 = "C:\Program Files\Microsoft Office\Office15\"
Const MSO15_Pro32 = "C:\Program Files(x86)\Microsoft Office\Office15\"
Const cnsSys32 = "C:\WINDOWS\System32\"
Const cnsWow64 = "C:\WINDOWS\SysWOW64\"
Const MShared64 = "C:\Program Files\Common Files\Microsoft Shared\"
Const MShared32 = "C:\Program Files(x86)\Common Files\Microsoft Shared\"
Const Common64 = "C:\Program Files\Common Files\"
Const Common32 = "C:\Program Files(x86)\Common Files\"
Const GUID_DAO = "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}": refs.AddFromGuid GUID_DAO, 12, 0 ' Microsoft Office 16.0 Access database engine Object Library =12.0 Note: You need download and Install
Const GUID_ADODB = "{B691E011-1797-432E-907A-4D8C69339129}": refs.AddFromGuid GUID_ADODB, 6, 1 'Microsoft ActiveX Data Objects 6.1 Library =6.1
Const GUID_ADOX = "{00000600-0000-0010-8000-00AA006D2EA4}": refs.AddFromGuid GUID_ADOX, 6, 0 'Microsoft ADO Ext. 6.0 for DDL and Security =6.0
Const GUID_ADOR = "{00000300-0000-0010-8000-00AA006D2EA4}": refs.AddFromGuid GUID_ADOR, 6, 0 'Microsoft ActiveX Data Objects Recordset 6.0 Library
Const GUID_AccessApp = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}": refs.AddFromGuid GUID_ADOR, 9, 0 'Microsoft Access XX.0 Object Library
Const GUID_CDO = "{CD000000-8B95-11D1-82DB-00C04FB1625D}": refs.AddFromGuid GUID_CDO, 1, 0 'Microsoft CDO for Windows 2000 Library = 1.0
Const GUID_MSCoree24 = "{5477469E-83B1-11D2-8B49-00A0C9B7C9C4}": refs.AddFromGuid GUID_MSCoree24, 2, 4 'Common Language Runtime Execution Engine 2.4 Library = 2.4
Const GUID_IMAPI2 = "{2735412F-7F64-5B0F-8F00-5D77AFBE261E}": refs.AddFromGuid GUID_IMAPI2, 1, 0 'Microsoft IMAPI2 Base Functionality = 1.0
Const GUID_IMAPI2FS = "{2C941FD0-975B-59BE-A960-9A2A262853A5}": refs.AddFromGuid GUID_IMAPI2FS, 1, 0 'Microsoft IMAPI2 File System Image Creator = 1.0
Const GUID_JetES = "{2358C810-62BA-11D1-B3DB-00600832C573}": refs.AddFromGuid GUID_JetES, 4, 0 'JET Expression Service Type Library
Const GUID_JRO = "{AC3B8B4C-B6CA-11D1-9F31-00C04FC29D52}": refs.AddFromGuid GUID_JRO, 2, 6 ' Microsoft Jet and Replication Objects 2.6 Library = 2.6 C:\Program Files (x86)\Common Files\System\ado\msjro.dll
Const GUID_MsoEuro = "{76F6F3F5-9937-11D2-93BB-00105A994D2C}": refs.AddFromGuid GUID_JetES, 1, 0 'Microsoft Office Euro Converter Object Library = 1.0"
Const GUID_MSHTML = "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}": refs.AddFromGuid GUID_MSHTML, 4, 0 'Microsoft HTML Object Library = 4.0 C:\Windows\SysWOW64\msjtes40.dll 4.0"
Const GUID_MSXML2_V60 = "{F5078F18-C551-11D3-89B9-0000F81FE221}": refs.AddFromGuid GUID_MSXML2_V60, 6, 0 ' Microsoft XML, v6.0 C:\Windows\System32\msxml6.dll = 6.0
Const GUID_OfficeObject = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}" 'Microsoft Office 16.0 Object Library C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL = 2.8
refs.AddFromGuid "{F618C513-DFB8-11D1-A2CF-00805FC79235}", 1, 0
refs.AddFromGuid "{8E80422B-CAC4-472B-B272-9635F1DFEF3B}", 1, 0 'MMC20 Microsoft Management Console 2.0
Const GUID_VBRegExp55 = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}": refs.AddFromGuid GUID_VBRegExp55, 5, 5 'Microsoft VBScript Regular Expressions 5.5
Const GUID_Scripting = "{420B2830-E718-11CF-893D-00A0C9054228}": refs.AddFromGuid GUID_Scripting, 1, 0 ' Microsoft Scripting Runtime C:\Windows\System32\scrrun.dll = 1.0
Const GUID_Shell32 = "{50A7E9B0-70EF-11D1-B75A-00A0C90564FE}": refs.AddFromGuid GUID_Shell32, 1, 0 ' Microsoft Shell Controls And Automation = 1.0
Const GUID_SHDocVw = "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}": refs.AddFromGuid GUID_SHDocVw, 1, 1 'Microsoft Internet Controls = 1.1
Const GUID_WIA = "{94A0E92D-43C0-494E-AC29-FD45948A5221}": refs.AddFromGuid GUID_WIA, 1, 0 ' Microsoft Windows Image Acquisition Library v2.0 = 1.0
Const GUID_WinHttp = "{662901FC-6951-4854-9EB2-D9A2570F2B2E}": refs.AddFromGuid GUID_WinHttp, 5, 1 'Microsoft WinHTTP Services, version 5.1 C:\WINDOWS\system32\winhttpcom.dll = 5.1
Const GUID_IWshRuntimeLibrary = "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}": refs.AddFromGuid GUID_IWshRuntimeLibrary, 1, 0 ' Windows Script Host Object Model = 1.0
Const GUID_WSHControllerLibrary = "{563DC060-B09A-11D2-A24D-00104BD35090}": refs.AddFromGuid GUID_WSHControllerLibrary, 1, 0 ' WSHControler Library = 1.0
Const GUID_RDS = "{BD96C556-65A3-11D0-983A-00C04FC29E30}": refs.AddFromGuid GUID_RDS, 1, 5 ' Microsoft Remote Data Services 6.0 Library = 1.5
Const GUID_SpeechLib = "{C866CA3A-32F7-11D2-9602-00C04F8EE628}": refs.AddFromGuid GUID_SpeechLib, 5, 4 ' Microsoft Speech Object Library =5.4
Const GUID_TTSEngineLib = "{EB2114C0-CB02-467A-AE4D-2ED171F05E6A}": refs.AddFromGuid GUID_SpeechLib, 10, 0 ' Microsoft TTS Engine 10.0 Type Library =10.0
Const GUID_System_Drawing = "{D37E2A3E-8545-3A39-9F4F-31827C9124AB}": refs.AddFromGuid GUID_System_Drawing, 2, 4 'System.Drawing.dll 2.4
Const GUID_System_EnterpriseServices = "{4FB2D46F-EFC8-4643-BCD0-6E5BFA6A174C}": refs.AddFromGuid GUID_System_EnterpriseServices, 2, 4 'System_EnterpriseServices = 2.4
Const GUID_System_Windows_Fomrs20 = "{215D64D2-031C-33C7-96E3-61794CD1EE61}": refs.AddFromGuid GUID_System_Windows_Fomrs20, 2, 0 'System Windows Forms 2.0 Object Library = 2.0
Const GUID_WbemScripting = "{565783C6-CB41-11D1-8B02-00600806D9B6}": refs.AddFromGuid GUID_WbemScripting, 1, 2 ' Microsoft WMI Scripting V1.2 Library = 1.2
Const GUID_WMPLib = "{6BF52A50-394A-11D3-B153-00C04F79FAA6}": refs.AddFromGuid GUID_WMPLib, 1, 0 ' Windows Media Player = 1.0
Const GUID_WinWord = "{00020905-0000-0000-C000-000000000046}" ' Microsoft Word 16.0 Object Library= 8.7
Const GUID_MSPub = "{0002123C-0000-0000-C000-000000000046}" 'Microsoft Publisher 16.0 Object Library = 2.3
Const GUID_OUTLOOK = "{0006F062-0000-0000-C000-000000000046}" ' Microsoft Outlook 16.0 Object Library 9.6
Const GUID_OLXLib = "{0006F062-0000-0000-C000-000000000046}" ' Microsoft Outlook View Control = 1.2
Const GUID_POWERPOINT = "{91493440-5A91-11CF-8700-00AA0060263B}" ' Microsoft PowerPoint 16.0 Object Library = 2.12
Const GUID_Excel = "{00020813-0000-0000-C000-000000000046}" ' Microsoft Excel 16.0 Object Library = 1.9
Const GUID_GRAPH = "{00020802-0000-0000-C000-000000000046}" 'Microsoft Graph 16.0 Object Library = 1.9tr
Const GUID_MSAccess16 = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}" ' Microsoft Access 16.0 Object Library = 9.0
Const GUID_BARCODELib = "{D9347025-9612-11D1-9D75-00C04FCC8CDC}": refs.AddFromGuid GUID_BARCODELib, 1, 0 ' Microsoft Access BarCode Control 14.0 = 1.0
' Const GUID_eawfctrlLib16 = "{113D61B1-C7C0-4157-B694-43594E25DF45}" 'eawfctrl 1.2 Type Library = 1.2
refs.AddFromGuid "{714DD4F6-7676-4BDE-925A-C2FEC2073F36}", 1, 0 ' AccessibilityCplAdminLib AccessibilityCplAdmin 1.0 Type Library
refs.AddFromGuid "{21D6D480-A88B-11D0-83DD-00AA003CCABD}", 1, 0 ' TAPI3Lib Microsoft TAPI 3.0 Type Library
refs.AddFromGuid "{44EC0535-400F-11D0-9DCD-00A0C90391D3}", 1, 0 ' ATLLib ATL 2.0 Type Library
refs.AddFromGuid "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 9, 0 ' Microsoft Access 14.0 - 16.0 Object Library
refs.AddFromGuid "{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}", 1, 0 ' MSScriptControl Microsoft Script Control 1.0
refs.AddFromGuid "{8D763331-F59C-46F5-99FF-F74CDC84AD0E}", 1, 0 ' Microsoft Project Task Launch Control
refs.AddFromGuid "{54AF9343-1923-11D3-9CA4-00C04F72C514}", 2, 50 'MACVer
refs.AddFromGuid "{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 2, 0 ' Microsoft Forms 2.0 Object Library
refs.AddFromGuid "{7988B57C-EC89-11CF-9C00-00AA00A14F56}", 1, 0 ' Microsoft Disk Quota
refs.AddFromGuid "{06290C00-48AA-11D2-8432-006008C3FBFC}", 1, 0 ' Scriptlet
refs.AddFromGuid "{EB2114C0-CB02-467A-AE4D-2ED171F05E6A}", 10, 0 'Microsoft TTS Engine 10.0 Type Library
refs.AddFromGuid "{9B085638-018E-11D3-9D8E-00C04F72D980}", 1, 0 ' Microsoft Tuner 1.0
refs.AddFromGuid "{9B7C3E2E-25D5-4898-9D85-71CEA8B2B6DD}", 2, 0 ' FDATELib FDate 2.0 Type Library C:\Program Files\Common Files\Microsoft Shared\Smart Tag\FDATE.DLL
refs.AddFromGuid "{2206CEB0-19C1-11D1-89E0-00C04FD7A829}", 1, 0 ' MSDASC Microsoft OLE DB Service Component 1.0 Type Library
refs.AddFromGuid "{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}", 1, 5 ' MSDAOSP Microsoft OLE DB Simple Provider 1.5 Library
refs.AddFromGuid "{833E4000-AFF7-4AC3-AAC2-9F24C1457BCE}", 1, 0 ' HelpServiceTypeLib
refs.AddFromGuid "{2A005C00-A5DE-11CF-9E66-00AA00A3F464}", 1, 0 ' COMSVCSLib COM+ Services Type Library
refs.AddFromGuid "{98315905-7BE5-11D2-ADC1-00A02463D6E7}", 1, 0 ' COMReplLib ComPlus 1.0 Catalog Replication Type Library
refs.AddFromGuid "{6CAAAA3B-6502-40FE-97FC-72A290DC63CF}", 1, 0 ' CorrEngineLib CorrEngine 1.0 Type Library
refs.AddFromGuid "{87099223-C7AF-11D0-B225-00C04FB6C2F5}", 1, 0 ' FAXCOMLib faxcom 1.0 Type Library
refs.AddFromGuid "{E4DE3030-0142-4ACA-BA48-8613B56A2555}", 1, 0 ' FAXCONTROLLib FaxControl 1.0 Type Library
refs.AddFromGuid "{2BF34C1A-8CAC-419F-8547-32FDF6505DB8}", 1, 0 ' Microsoft Fax Service Extended COM Type Library"
refs.AddFromGuid "{9CDCD9C9-BC40-41C6-89C5-230466DB0BD0}", 2, 0 ' Feed 2.0
refs.AddFromGuid "{0FFF9602-69CF-4728-9EA4-141514866CA2}", 1, 0 ' FIndPrinterslib
refs.AddFromGuid "{D8DC76AB-F007-49C6-B6FC-8392A3DF90C4}", 1, 0 ' LocalService 1.0 Type Library
refs.AddFromGuid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4 ' Microsoft Common Language Runtime Class Library System.Collection Arraylist
refs.AddFromGuid "{78530B68-61F9-11D2-8CAD-00A024580902}", 1, 0 ' DexterLib Dexter 1.0 Type Library
refs.AddFromGuid "{9B085638-018E-11D3-9D8E-00C04F72D980}", 1, 0 ' ATLLib ATL 2.0 Type Library
refs.AddFromGuid "{B30CDC65-4456-4FAA-93E3-F8A79E21891C}", 1, 0 ' ATLEntityPickerLib ATLEntityPicker 1.0 Type Library
refs.AddFromGuid "{28854DE7-2CF8-4A60-A85A-C21184D76BB6}", 1, 0 ' InstallerMainShellLib Installer Main Shell Lib
refs.AddFromGuid "{E34CB9F1-C7F7-424C-BE29-027DCC09363A}", 1, 0 ' TaskScheduler 1.0
refs.AddFromGuid "{28DCD85B-ACA4-11D0-A028-00AA00B605A4}", 1, 0 ' TERMMGRLib TAPI3 Terminal Manager 1.0 Type Library
refs.AddFromGuid "{28DCD85B-ACA4-11D0-A028-00AA00B605A4}", 1, 1 ' TDCLib Tabular Data Control 1.1 Type Library
refs.AddFromGuid "{8628F27C-64A2-4ED6-906B-E6155314C16A}", 1, 0 ' REMOTEPROXY6432Lib RemoteProxy6432 1.0 Type Library
refs.AddFromGuid "{A87F050D-3FFD-4682-8E77-34E530624CB4}", 1, 0 ' SessionMsgLib
refs.AddFromGuid "{C3A407A9-3409-4028-ACCF-9225FD9688D7}", 1, 0 ' RdpCoreTSLib Rdp Protocol Provider 1.0 Type Library
refs.AddFromGuid "{438EDB38-282C-435D-8BE3-4AB90B83CEF5}", 1, 0 ' PrintUIObjLib PrintUI Objects 1.0 Type Library
refs.AddFromGuid "{91CE54EE-C67C-4B46-A4FF-99416F27A8BF}", 1, 0 ' PrinterExtensionLib Printer Extension 1.0 Type Library
refs.AddFromGuid "{C8B522D5-5CF3-11CE-ADE5-00AA0044773D}", 1, 0 ' OLEDBError Microsoft OLE DB Error Library
refs.AddFromGuid "{FC5988CF-6D6A-4812-ADD9-2DDE4F47346F}", 1, 0 ' MSTSWebProxyLib Microsoft Terminal Services Web Proxy 1.0 Type Library
refs.AddFromGuid "{8C11EFA1-92C3-11D1-BC1E-00C04FA31489}", 1, 0 ' MSTSCLib Microsoft Terminal Services Control Type Library
refs.AddFromGuid "{7E8BC440-AEFF-11D1-89C2-00C04FB6BFC4}", 1, 0 ' IEXTagLib iextag 1.0 Type Library
refs.AddFromGuid "{06CA6721-CB57-449E-8097-E65B9F543A1A}", 1, 0 ' IETAGLib ietag 1.0 Type Library
refs.AddFromGuid "{833E4000-AFF7-4AC3-AAC2-9F24C1457BCE}", 1, 0 ' HelpServiceTypeLib Help Service 1.0 Type Library
refs.AddFromGuid "{BA35B84E-A623-471B-8B09-6D72DD072F25}", 1, 0 ' VisioViewer Microsoft Visio Viewer 16.0 Type Library
refs.AddFromGuid "{B9164592-D558-4EE7-8B41-F1C9F66D683A}", 1, 0 ' OneNoteIEAddin Microsoft OneNote IE Addin Object Library
refs.AddFromGuid "{1C82EAD8-508E-11D1-8DCF-00C04FB951F9}", 1, 0 ' MIMEEDIT Microsoft MIMEEDIT Type Library 1.0
refs.AddFromGuid "{31411197-A502-11D2-BBCA-00C04F8EC294}", 1, 0 ' MSHelpServices Microsoft Help Data Services 1.0 Type Library
refs.AddFromGuid "{F618C513-DFB8-11D1-A2CF-00805FC79235}", 1, 0 ' COMAdmin COM + 1.0 Admin Type Library
#If Win32 Then
refs.AddFromGuid "{0109E0F4-91AE-4736-A2CE-9D63E89D0EF6}", 1, 0 'XPS_SHL_DLLLib XPS_SHL_DLL 1.0 Type Library 32 bit版のみ参照設定可能
#End If
With refs
If Application.Version >= 16 Then
.AddFromGuid GUID_OfficeObject, 2, 8
.AddFromGuid GUID_Excel, 1, 9
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9, 6 'Microsoft Outlook 16.0 Object Library
.AddFromGuid GUID_POWERPOINT, 2, 12
.AddFromGuid GUID_MSPub, 2, 3
.AddFromGuid GUID_OLXLib, 1, 2
' .AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 2 'eawfctrl 1.0 Type Library
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 ' Microsoft Outlook SharePoint Social Provider
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A}", 1, 0 'OneNoteEx Microsoft OneNote 15.0 Extended Object Library
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 1 'Microsoft Outlook SharePoint Social Provider 1.1
.AddFromGuid "{1F8E79BA-9268-4889-ADF3-6D2AABB3C32C}", 1, 1 'OutlookSocialProvider Microsoft Outlook Social Provider Extensibility
.AddFromGuid "{9E175B61-F52A-11D8-B9A5-505054503030}", 1, 0 'Microsoft Search Interface Type Library(from 2016) C:\WINDOWS\system32\mssitlb.dll
.AddFromGuid "{CBBC4772-C9A4-4FE8-B34B-5EFBD68F8E27}", 1, 0 'NoteLinkComLib 1.0 Type Library(from 2016)
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A} ", 1, 0 'Microsoft OneNote 15.0 Extended Object Library 1.0
.AddFromGuid GUID_WinWord, 8, 7 'Microsoft Word 16.0 Object Library
.AddFromGuid "{73720012-33A0-11E4-9B9A-00155D152105}", 1, 0 ' Microsoft Office Screen Recorder 16.0)from 2016) Object Librar
.AddFromGuid "{6CC6A20E-96A4-4F94-A838-8E5EBE9E9925}", 1, 0 ' ScreenReaderHelper
.AddFromGuid "{22E0CB87-9325-4B0F-8ECC-21B271EC81AA}", 1, 0 ' DolbyDLLlib (from 2016 windows 10)
.AddFromGuid "{4486DF98-22A5-4F6B-BD5C-8CADCEC0A6DE}", 1, 0 'LocationApi 1.0 Type Library (from 2016 windows 10)
.AddFromGuid "{012F24C1-35B0-11D0-BF2D-0000E8D0D146}", 1, 0 ' ACTIVEXLib Microsoft Office Template and Media Control 1.0 Type Library
.AddFromGuid "{00020802-0000-0000-C000-000000000046}", 1, 9 'Microsoft Graph 16.0 Object Library
ElseIf Application.Version = 15 Then
On Error Resume Next
.AddFromGuid GUID_OfficeObject, 2, 8
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid GUID_OfficeObject, 2, 7
End If
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid GUID_OfficeObject, 2, 6
End If
'OutlookSocialProvider
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 1 'OutlookSocialProvider
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 'OutlookSocialProvider
End If
' Office 2013 (version 15) でMajor,Minor番号が定まっていると考えられるもの
.AddFromGuid GUID_Excel, 1, 8
.AddFromGuid GUID_OUTLOOK, 9, 5
.AddFromGuid GUID_POWERPOINT, 2, 11
.AddFromGuid GUID_MSPub, 2, 2
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A} ", 1, 0 'Microsoft OneNote 15.0 Extended Object Library 1.0
.AddFromGuid GUID_OLXLib, 1, 1
.AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 1 'eawfctrl 1.0 Type Library
.AddFromGuid GUID_WinWord, 8, 6 'Microsoft Word 15.0 Object Library
ElseIf Application.Version = 14 Then
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 'OutlookSocialProvider 2013 以降はない
.AddFromGuid GUID_OfficeObject, 2, 5
.AddFromGuid GUID_Excel, 1, 7
.AddFromGuid GUID_OUTLOOK, 9, 4
.AddFromGuid GUID_POWERPOINT, 2, 10
.AddFromGuid GUID_MSPub, 2, 1
.AddFromGuid GUID_OLXLib, 1, 1
.AddFromGuid "{1F8E79BA-9268-4889-ADF3-6D2AABB3C32C}", 1, 0 'Microsoft Outlook Social Provider Extensibility
.AddFromGuid "{0EA692EE-BB50-4E3C-AEF0-356D91732725}", 1, 0 'Microsoft OneNote 14.0 Object Library
.AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 0 'eawfctrl 1.0 Type Library
.AddFromGuid GUID_WinWord, 8, 5 'Microsoft Word 14.0 Object Library
End If
End With
If Not refs Is Nothing Then Set refs = Nothing
Set refs = Application.VBE.ActiveVBProject.References 'Application.ReferencesではDescriptionが出ない。このため Refs を Nothing にして、 左のように書き換える
For Each ref In refs
If ref.IsBroken = False Then
Debug.Print ref.Name, ref.Guid, ref.Major, ref.Minor, ref.Description, ref.FullPath
Else
refs.Remove ref
End If
Next
' せっかく Excel なので、 Worksheetを追加して一覧表にする
Dim wb As Workbook: Set wb = ThisWorkbook
Dim iSheet As Long
wb.Worksheets.Add
iSheet = wb.Worksheets.Count
Dim ws As Worksheet
Set ws = wb.Worksheets(iSheet)
Dim r As Range, iRow As Long, iCol As Long
Dim arRow, cnt As Long
ws.Activate
arRow = Split("Name,Description,GUID,Major,Minor,FullPath,built,isBroken", ",")
iCol = 1
Application.ScreenUpdating = False
For cnt = LBound(arRow) To UBound(arRow)
ws.Range(Cells(1, iCol).Address) = arRow(cnt)
iCol = iCol + 1
Next
iRow = 2
For Each ref In refs
iCol = 1
With ws
.Cells(iRow, iCol).Value = ref.Name: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.Description: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.Guid: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.Major: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.Minor: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.FullPath: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.BuiltIn: iCol = iCol + 1
.Cells(iRow, iCol).Value = ref.IsBroken: iCol = iCol + 1
End With
iRow = iRow + 1
Next
Application.ScreenUpdating = True
End Sub
Sub WriteExcelVBACodeReferenceSetWithGuid()
' 最低限これだけ参照設定すると大体のコードサンプルの参照設定を網羅できるマクロ
' Excel 2016 Later それ以前は Outlook, Excel,Graph,Word,Powerpoint,Publisher,Outlook view control のMajor,Minorを変えてください。
' Reference Set "Microsoft Visau Basic Exitenbility 5.3"
' Reference Set "Scripting"
' Create Excel VBA CODE Rcomennd Refernce Setteiing Programatically
Const VBACodetextFile = "C:\hoge\Excel2016RecomendRef.txt"
Const addfrom = ".AddFromGUID "
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim iRow As Long, icol As Long, cnt As Long
Dim fso As New FileSystemObject
Dim ts As TextStream, oFile As File, ofolder As Folder
Dim arlist, i1 As Long
arlist = Split("Access,AccessibilityCplAdminLib,ACTIVEXLib,ADODB,ADOR,ADOX,BARCODELib,CDO,Excel,Graph,IWshRuntimeLibrary,JetES,JRO,mscoree,mscorlib,MSForms,MSHTML,MsoEuro,MSScriptControl,MSXML2,Office,OLXLib,Outlook,OutlookSocialProvider,PowerPoint,Publisher,Scripting,Scriptlet,SHDocVw,Shell32,SpeechLib,stdole,System_Drawing,System_Windows_Forms,TTSEngineLib,VBA,VBIDE,VBScript_RegExp_55,WbemScripting,WIA,WinHttp,WMPLib,Word", ",")
Set ts = fso.CreateTextFile(VBACodetextFile, True, False)
ts.Write "Sub SetVBIDE53()" & vbCrLf & "' VBIDE Microsoft Visual Basic for Applications Extensibility 5.3" & vbCrLf & "' {0002E157-0000-0000-C000-000000000046} 5 3" & vbCrLf & _
"' C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" & vbCrLf & "' を強制起動させるマクロ" & vbCrLf & "On Error Resume Next" & "Application.ActiveWorkbook.VBProject.References.AddFromGuid ""{0002E157-0000-0000-C000-000000000046}"", 5, 3" & vbCrLf & _
"End Sub" & vbCrLf
ts.Write "Sub SetVBARefernceSettingsProgramatically()" & vbCrLf & _
"' For Excel 2016 Later (Version 16) それ以前のVersionはWord Excel Pubisher OutlookのMajor Minorを変えてください" & vbCrLf & _
"' Need VBIDE Before Running" & vbCrLf & _
"Dim refs As VBIDE.References : Set refs = Application.ActiveWorkbook.VBProject.References" & vbCrLf
ts.WriteLine "With Refereces"
For iRow = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
For i1 = LBound(arlist) To UBound(arlist)
If arlist(i1) = ws.Range("A" & iRow).Value Then
ts.WriteLine addfrom & Chr(34) & ws.Range("C" & iRow).Value & Chr(34) & "," & ws.Range("D" & iRow).Value & "," & ws.Range("E" & iRow).Value & " '" & ws.Range("A" & iRow).Value & vbTab & ws.Range("B" & iRow).Value
Exit For
End If
Next
Next
ts.WriteLine "End With" & vbCrLf & "End Sub"
ts.Close
Set ts = Nothing
End Sub
#Access 2013 Later 64/32
##事前準備は１つ。マクロを有効にする
###Access With GUID Version(beta)
しかし世間の風は冷たい。
GUIDじゃないと配布に向かないと。
幸運にもAccess 2010 を除いてしらべたことがあったので、
https://qiita.com/Q11Q/items/d1d4fe9e395018e61614
ちょうどイギリスのサイトでAccess2010( Version 14) をみつけた
https://access-programmers.co.uk/forums/showthread.php?t=293716
これと現在のversion 16と付き合わあせてVersion 15を推定する。
できない場合はエラー判定でバージョンを下げていく。
一部あいまいだが、一応こんな感じだろう。
version14も動くようにしている。
####Jscriptは入っていません
なにやら世間を騒がせているのと64Bitでは動きませんので入っていません。
ただしこれはSystem.Collection.Arraylistが代替できるかもしれません。
もしくはmsxml,mshtml,winhttpを使うようです。
https://vbabeginner.net/vba%E3%81%A7-net-framework%E3%81%AEarraylist%E3%82%92%E4%BD%BF%E3%81%86/
####XPSが64bitでは動かない
2010 32bitでは動いたので、おそらく64bitでは動かないようです。
version番号が1.0なので、Version違いということがなく、そう考えられます。
このためConst If Directive(条件付き編纂分岐)で32bit限定でセットします。
####バージョン番号は本当にめちゃくちゃ
DAOは2010-2019まで12.0
Access XX.0 Object Libraryはずっと9.0
FSO,Shellあたりはずっと1.0です。
####TTSは番号が飛びすぎてわからない
TTSだけがよくわかりません。
たしか2007の時は読み上げ機能がなくなってふざけんなバカがバリアフリーの時代に逆行するな。と思いましたね。
2010は復活したというより追加したんです。
http://snow-white.cocolog-nifty.com/first/2010/06/office-2010-55b.html
https://plaza.rakuten.co.jp/kinchan07/diary/201110210000/
現在でもSpeech Platformは存在しており、複数言語の TTS の読み上げ機能を使用するとき、ここからダウンロードします。 gaあります。
Sub SetVBIDEAccess()
On Error Resume Next
' Access 2010 Later
' Microsoft Visual Basic for Applications Extensibility 5.3 をプログラムで参照設定するマクロ
' Programatically Set VBIDE.
On Error Resume Next
Application.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
End Sub
Sub refsetAccee2010Later()
' For Microsoft Access 2010 Later 64/32
Dim ref As Object, refs As Object
Dim i As Long
Set refs = Application.References
For i = refs.Count To 1 Step -1
With refs.Item(i)
Debug.Print .Name, , .FullPath ' この時はDescriptionは使えない
End With
Next
On Error Resume Next
For Each ref In refs
If ref.Name = "VBIDE" Then refs.Remove ref
Next
refs.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
On Error Resume Next
For Each ref In Application.VBE.ActiveVBProject.References
Debug.Print ref.Name, ref.Description, ref.Guid, ref.Major, ref.Minor, ref.FullPath
Next
For Each ref In refs
If ref.BuiltIn = False Then
If ref.Name <> "VBIDE" Then
refs.Remove ref
End If
End If
Next
On Error Resume Next
Const MSO16_Pro64 = "C:\Program Files\Microsoft Office\Root\Office16\"
Const MSO16_Pro32 = "C:\Program Files(x86)\Microsoft Office\Root\Office16\"
Const MSO15_Pro64 = "C:\Program Files\Microsoft Office\Office15\"
Const MSO15_Pro32 = "C:\Program Files(x86)\Microsoft Office\Office15\"
Const cnsSys32 = "C:\WINDOWS\System32\"
Const cnsWow64 = "C:\WINDOWS\SysWOW64\"
Const MShared64 = "C:\Program Files\Common Files\Microsoft Shared\"
Const MShared32 = "C:\Program Files(x86)\Common Files\Microsoft Shared\"
Const Common64 = "C:\Program Files\Common Files\"
Const Common32 = "C:\Program Files(x86)\Common Files\"
Const GUID_DAO = "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}": refs.AddFromGuid GUID_DAO, 12, 0 ' Microsoft Office 16.0 Access database engine Object Library =12.0 Note: You need download and Install
Const GUID_ADODB = "{B691E011-1797-432E-907A-4D8C69339129}": refs.AddFromGuid GUID_ADODB, 6, 1 'Microsoft ActiveX Data Objects 6.1 Library =6.1
Const GUID_ADOX = "{00000600-0000-0010-8000-00AA006D2EA4}": refs.AddFromGuid GUID_ADOX, 6, 0 'Microsoft ADO Ext. 6.0 for DDL and Security =6.0
Const GUID_ADOR = "{00000300-0000-0010-8000-00AA006D2EA4}": refs.AddFromGuid GUID_ADOR, 6, 0 'Microsoft ActiveX Data Objects Recordset 6.0 Library
Const GUID_AccessApp = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}": refs.AddFromGuid GUID_ADOR, 9, 0 'Microsoft Access XX.0 Object Library
Const GUID_CDO = "{CD000000-8B95-11D1-82DB-00C04FB1625D}": refs.AddFromGuid GUID_CDO, 1, 0 'Microsoft CDO for Windows 2000 Library = 1.0
Const GUID_MSCoree24 = "{5477469E-83B1-11D2-8B49-00A0C9B7C9C4}": refs.AddFromGuid GUID_MSCoree24, 2, 4 'Common Language Runtime Execution Engine 2.4 Library = 2.4
Const GUID_IMAPI2 = "{2735412F-7F64-5B0F-8F00-5D77AFBE261E}": refs.AddFromGuid GUID_IMAPI2, 1, 0 'Microsoft IMAPI2 Base Functionality = 1.0
Const GUID_IMAPI2FS = "{2C941FD0-975B-59BE-A960-9A2A262853A5}": refs.AddFromGuid GUID_IMAPI2FS, 1, 0 'Microsoft IMAPI2 File System Image Creator = 1.0
Const GUID_JetES = "{2358C810-62BA-11D1-B3DB-00600832C573}": refs.AddFromGuid GUID_JetES, 4, 0 'JET Expression Service Type Library
Const GUID_JRO = "{AC3B8B4C-B6CA-11D1-9F31-00C04FC29D52}": refs.AddFromGuid GUID_JRO, 2, 6 ' Microsoft Jet and Replication Objects 2.6 Library = 2.6 C:\Program Files (x86)\Common Files\System\ado\msjro.dll
Const GUID_MsoEuro = "{76F6F3F5-9937-11D2-93BB-00105A994D2C}": refs.AddFromGuid GUID_JetES, 1, 0 'Microsoft Office Euro Converter Object Library = 1.0"
Const GUID_MSHTML = "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}": refs.AddFromGuid GUID_MSHTML, 4, 0 'Microsoft HTML Object Library = 4.0 C:\Windows\SysWOW64\msjtes40.dll 4.0"
Const GUID_MSXML2_V60 = "{F5078F18-C551-11D3-89B9-0000F81FE221}": refs.AddFromGuid GUID_MSXML2_V60, 6, 0 ' Microsoft XML, v6.0 C:\Windows\System32\msxml6.dll = 6.0
Const GUID_OfficeObject = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}" 'Microsoft Office 16.0 Object Library C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL = 2.8
refs.AddFromGuid "{F618C513-DFB8-11D1-A2CF-00805FC79235}", 1, 0
refs.AddFromGuid "{8E80422B-CAC4-472B-B272-9635F1DFEF3B}", 1, 0 'MMC20 Microsoft Management Console 2.0
Const GUID_VBRegExp55 = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}": refs.AddFromGuid GUID_VBRegExp55, 5, 5 'Microsoft VBScript Regular Expressions 5.5
Const GUID_Scripting = "{420B2830-E718-11CF-893D-00A0C9054228}": refs.AddFromGuid GUID_Scripting, 1, 0 ' Microsoft Scripting Runtime C:\Windows\System32\scrrun.dll = 1.0
Const GUID_Shell32 = "{50A7E9B0-70EF-11D1-B75A-00A0C90564FE}": refs.AddFromGuid GUID_Shell32, 1, 0 ' Microsoft Shell Controls And Automation = 1.0
Const GUID_SHDocVw = "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}": refs.AddFromGuid GUID_SHDocVw, 1, 1 'Microsoft Internet Controls = 1.1
Const GUID_WIA = "{94A0E92D-43C0-494E-AC29-FD45948A5221}": refs.AddFromGuid GUID_WIA, 1, 0 ' Microsoft Windows Image Acquisition Library v2.0 = 1.0
Const GUID_WinHttp = "{662901FC-6951-4854-9EB2-D9A2570F2B2E}": refs.AddFromGuid GUID_WinHttp, 5, 1 'Microsoft WinHTTP Services, version 5.1 C:\WINDOWS\system32\winhttpcom.dll = 5.1
Const GUID_IWshRuntimeLibrary = "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}": refs.AddFromGuid GUID_IWshRuntimeLibrary, 1, 0 ' Windows Script Host Object Model = 1.0
Const GUID_WSHControllerLibrary = "{563DC060-B09A-11D2-A24D-00104BD35090}": refs.AddFromGuid GUID_WSHControllerLibrary, 1, 0 ' WSHControler Library = 1.0
Const GUID_RDS = "{BD96C556-65A3-11D0-983A-00C04FC29E30}": refs.AddFromGuid GUID_RDS, 1, 5 ' Microsoft Remote Data Services 6.0 Library = 1.5
Const GUID_SpeechLib = "{C866CA3A-32F7-11D2-9602-00C04F8EE628}": refs.AddFromGuid GUID_SpeechLib, 5, 4 ' Microsoft Speech Object Library =5.4
Const GUID_TTSEngineLib = "{EB2114C0-CB02-467A-AE4D-2ED171F05E6A}": refs.AddFromGuid GUID_SpeechLib, 10, 0 ' Microsoft TTS Engine 10.0 Type Library =10.0
Const GUID_System_Drawing = "{D37E2A3E-8545-3A39-9F4F-31827C9124AB}": refs.AddFromGuid GUID_System_Drawing, 2, 4 'System.Drawing.dll 2.4
Const GUID_System_EnterpriseServices = "{4FB2D46F-EFC8-4643-BCD0-6E5BFA6A174C}": refs.AddFromGuid GUID_System_EnterpriseServices, 2, 4 'System_EnterpriseServices = 2.4
Const GUID_System_Windows_Fomrs20 = "{215D64D2-031C-33C7-96E3-61794CD1EE61}": refs.AddFromGuid GUID_System_Windows_Fomrs20, 2, 0 'System Windows Forms 2.0 Object Library = 2.0
Const GUID_WbemScripting = "{565783C6-CB41-11D1-8B02-00600806D9B6}": refs.AddFromGuid GUID_WbemScripting, 1, 2 ' Microsoft WMI Scripting V1.2 Library = 1.2
Const GUID_WMPLib = "{6BF52A50-394A-11D3-B153-00C04F79FAA6}": refs.AddFromGuid GUID_WMPLib, 1, 0 ' Windows Media Player = 1.0
Const GUID_WinWord = "{00020905-0000-0000-C000-000000000046}" ' Microsoft Word 16.0 Object Library= 8.7
Const GUID_MSPub = "{0002123C-0000-0000-C000-000000000046}" 'Microsoft Publisher 16.0 Object Library = 2.3
Const GUID_OUTLOOK = "{0006F062-0000-0000-C000-000000000046}" ' Microsoft Outlook 16.0 Object Library 9.6
Const GUID_OLXLib = "{0006F062-0000-0000-C000-000000000046}" ' Microsoft Outlook View Control = 1.2
Const GUID_POWERPOINT = "{91493440-5A91-11CF-8700-00AA0060263B}" ' Microsoft PowerPoint 16.0 Object Library = 2.12
Const GUID_Excel = "{00020813-0000-0000-C000-000000000046}" ' Microsoft Excel 16.0 Object Library = 1.9
Const GUID_GRAPH = "{00020802-0000-0000-C000-000000000046}" 'Microsoft Graph 16.0 Object Library = 1.9tr
Const GUID_MSAccess16 = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}" ' Microsoft Access 16.0 Object Library = 9.0
Const GUID_BARCODELib = "{D9347025-9612-11D1-9D75-00C04FCC8CDC}": refs.AddFromGuid GUID_BARCODELib, 1, 0 ' Microsoft Access BarCode Control 14.0 = 1.0
Const GUID_eawfctrlLib16 = "{113D61B1-C7C0-4157-B694-43594E25DF45}" 'eawfctrl 1.2 Type Library = 1.2
#If VBA7 Then
refs.AddFromFile "C:\Windows\System32\tapi3.dll" 'Microsoft TAPI 3.0 Type Library
#Else
refs.AddFromFile "C:\Windows\SysWow64\tapi3.dll"
#End If
refs.AddFromGuid "{714DD4F6-7676-4BDE-925A-C2FEC2073F36}", 1, 0 ' AccessibilityCplAdminLib AccessibilityCplAdmin 1.0 Type Library
refs.AddFromGuid "{44EC0535-400F-11D0-9DCD-00A0C90391D3}", 1, 0 ' ATLLib ATL 2.0 Type Library
refs.AddFromGuid "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 9, 0 ' Microsoft Access 14.0 - 16.0 Object Library
refs.AddFromGuid "{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}", 1, 0 ' MSScriptControl Microsoft Script Control 1.0
refs.AddFromGuid "{8D763331-F59C-46F5-99FF-F74CDC84AD0E}", 1, 0 ' Microsoft Project Task Launch Control
refs.AddFromGuid "{54AF9343-1923-11D3-9CA4-00C04F72C514}", 2, 50 'MACVer
refs.AddFromGuid "{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 2, 0 ' Microsoft Forms 2.0 Object Library
refs.AddFromGuid "{7988B57C-EC89-11CF-9C00-00AA00A14F56}", 1, 0 ' Microsoft Disk Quota
refs.AddFromGuid "{06290C00-48AA-11D2-8432-006008C3FBFC}", 1, 0 ' Scriptlet
refs.AddFromGuid "{EB2114C0-CB02-467A-AE4D-2ED171F05E6A}", 10, 0 'Microsoft TTS Engine 10.0 Type Library
refs.AddFromGuid "{9B085638-018E-11D3-9D8E-00C04F72D980}", 1, 0 ' Microsoft Tuner 1.0
refs.AddFromGuid "{9B7C3E2E-25D5-4898-9D85-71CEA8B2B6DD}", 2, 0 ' FDATELib FDate 2.0 Type Library C:\Program Files\Common Files\Microsoft Shared\Smart Tag\FDATE.DLL
refs.AddFromGuid "{2206CEB0-19C1-11D1-89E0-00C04FD7A829}", 1, 0 ' MSDASC Microsoft OLE DB Service Component 1.0 Type Library
refs.AddFromGuid "{E0E270C2-C0BE-11D0-8FE4-00A0C90A6341}", 1, 5 ' MSDAOSP Microsoft OLE DB Simple Provider 1.5 Library
refs.AddFromGuid "{833E4000-AFF7-4AC3-AAC2-9F24C1457BCE}", 1, 0 ' HelpServiceTypeLib
refs.AddFromGuid "{2A005C00-A5DE-11CF-9E66-00AA00A3F464}", 1, 0 ' COMSVCSLib COM+ Services Type Library
refs.AddFromGuid "{98315905-7BE5-11D2-ADC1-00A02463D6E7}", 1, 0 ' COMReplLib ComPlus 1.0 Catalog Replication Type Library
refs.AddFromGuid "{6CAAAA3B-6502-40FE-97FC-72A290DC63CF}", 1, 0 ' CorrEngineLib CorrEngine 1.0 Type Library
refs.AddFromGuid "{87099223-C7AF-11D0-B225-00C04FB6C2F5}", 1, 0 ' FAXCOMLib faxcom 1.0 Type Library
refs.AddFromGuid "{E4DE3030-0142-4ACA-BA48-8613B56A2555}", 1, 0 ' FAXCONTROLLib FaxControl 1.0 Type Library
refs.AddFromGuid "{2BF34C1A-8CAC-419F-8547-32FDF6505DB8}", 1, 0 ' Microsoft Fax Service Extended COM Type Library"
refs.AddFromGuid "{9CDCD9C9-BC40-41C6-89C5-230466DB0BD0}", 2, 0 ' Feed 2.0
refs.AddFromGuid "{0FFF9602-69CF-4728-9EA4-141514866CA2}", 1, 0 ' FIndPrinterslib
refs.AddFromGuid "{D8DC76AB-F007-49C6-B6FC-8392A3DF90C4}", 1, 0 ' LocalService 1.0 Type Library
refs.AddFromGuid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4 ' Microsoft Common Language Runtime Class Library System.Collection Arraylist
refs.AddFromGuid "{78530B68-61F9-11D2-8CAD-00A024580902}", 1, 0 ' DexterLib Dexter 1.0 Type Library
refs.AddFromGuid "{9B085638-018E-11D3-9D8E-00C04F72D980}", 1, 0 ' ATLLib ATL 2.0 Type Library
refs.AddFromGuid "{B30CDC65-4456-4FAA-93E3-F8A79E21891C}", 1, 0 ' ATLEntityPickerLib ATLEntityPicker 1.0 Type Library
refs.AddFromGuid "{28854DE7-2CF8-4A60-A85A-C21184D76BB6}", 1, 0 ' InstallerMainShellLib Installer Main Shell Lib
refs.AddFromGuid "{E34CB9F1-C7F7-424C-BE29-027DCC09363A}", 1, 0 ' TaskScheduler 1.0
refs.AddFromGuid "{28DCD85B-ACA4-11D0-A028-00AA00B605A4}", 1, 0 ' TERMMGRLib TAPI3 Terminal Manager 1.0 Type Library
refs.AddFromGuid "{28DCD85B-ACA4-11D0-A028-00AA00B605A4}", 1, 1 ' TDCLib Tabular Data Control 1.1 Type Library
refs.AddFromGuid "{8628F27C-64A2-4ED6-906B-E6155314C16A}", 1, 0 ' REMOTEPROXY6432Lib RemoteProxy6432 1.0 Type Library
refs.AddFromGuid "{A87F050D-3FFD-4682-8E77-34E530624CB4}", 1, 0 ' SessionMsgLib
refs.AddFromGuid "{C3A407A9-3409-4028-ACCF-9225FD9688D7}", 1, 0 ' RdpCoreTSLib Rdp Protocol Provider 1.0 Type Library
refs.AddFromGuid "{438EDB38-282C-435D-8BE3-4AB90B83CEF5}", 1, 0 ' PrintUIObjLib PrintUI Objects 1.0 Type Library
refs.AddFromGuid "{91CE54EE-C67C-4B46-A4FF-99416F27A8BF}", 1, 0 ' PrinterExtensionLib Printer Extension 1.0 Type Library
refs.AddFromGuid "{C8B522D5-5CF3-11CE-ADE5-00AA0044773D}", 1, 0 ' OLEDBError Microsoft OLE DB Error Library
refs.AddFromGuid "{FC5988CF-6D6A-4812-ADD9-2DDE4F47346F}", 1, 0 ' MSTSWebProxyLib Microsoft Terminal Services Web Proxy 1.0 Type Library
refs.AddFromGuid "{8C11EFA1-92C3-11D1-BC1E-00C04FA31489}", 1, 0 ' MSTSCLib Microsoft Terminal Services Control Type Library
refs.AddFromGuid "{7E8BC440-AEFF-11D1-89C2-00C04FB6BFC4}", 1, 0 ' IEXTagLib iextag 1.0 Type Library
refs.AddFromGuid "{06CA6721-CB57-449E-8097-E65B9F543A1A}", 1, 0 ' IETAGLib ietag 1.0 Type Library
refs.AddFromGuid "{833E4000-AFF7-4AC3-AAC2-9F24C1457BCE}", 1, 0 ' HelpServiceTypeLib Help Service 1.0 Type Library
refs.AddFromGuid "{BA35B84E-A623-471B-8B09-6D72DD072F25}", 1, 0 ' VisioViewer Microsoft Visio Viewer 16.0 Type Library
refs.AddFromGuid "{B9164592-D558-4EE7-8B41-F1C9F66D683A}", 1, 0 ' OneNoteIEAddin Microsoft OneNote IE Addin Object Library
refs.AddFromGuid "{1C82EAD8-508E-11D1-8DCF-00C04FB951F9}", 1, 0 ' MIMEEDIT Microsoft MIMEEDIT Type Library 1.0
refs.AddFromGuid "{31411197-A502-11D2-BBCA-00C04F8EC294}", 1, 0 ' MSHelpServices Microsoft Help Data Services 1.0 Type Library
refs.AddFromGuid "{F618C513-DFB8-11D1-A2CF-00805FC79235}", 1, 0 ' COMAdmin COM + 1.0 Admin Type Library
#If Win32 Then
refs.AddFromGuid "{0109E0F4-91AE-4736-A2CE-9D63E89D0EF6}", 1, 0 'XPS_SHL_DLLLib XPS_SHL_DLL 1.0 Type Library 32 bit版のみ参照設定可能
#End If
With refs
If Application.Version >= 16 Then
.AddFromGuid GUID_OfficeObject, 2, 8
.AddFromGuid GUID_Excel, 1, 9
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9, 6 'Microsoft Outlook 16.0 Object Library
.AddFromGuid GUID_POWERPOINT, 2, 12
.AddFromGuid GUID_MSPub, 2, 3
.AddFromGuid GUID_OLXLib, 1, 2
.AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 2 'eawfctrl 1.0 Type Library
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 ' Microsoft Outlook SharePoint Social Provider
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A}", 1, 0 'OneNoteEx Microsoft OneNote 15.0 Extended Object Library
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 1 'Microsoft Outlook SharePoint Social Provider 1.1
.AddFromGuid "{1F8E79BA-9268-4889-ADF3-6D2AABB3C32C}", 1, 1 'OutlookSocialProvider Microsoft Outlook Social Provider Extensibility
.AddFromGuid "{9E175B61-F52A-11D8-B9A5-505054503030}", 1, 0 'Microsoft Search Interface Type Library(from 2016) C:\WINDOWS\system32\mssitlb.dll
.AddFromGuid "{CBBC4772-C9A4-4FE8-B34B-5EFBD68F8E27}", 1, 0 'NoteLinkComLib 1.0 Type Library(from 2016)
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A} ", 1, 0 'Microsoft OneNote 15.0 Extended Object Library 1.0
.AddFromGuid GUID_WinWord, 8, 7 'Microsoft Word 16.0 Object Library
.AddFromGuid "{73720012-33A0-11E4-9B9A-00155D152105}", 1, 0 ' Microsoft Office Screen Recorder 16.0)from 2016) Object Librar
.AddFromGuid "{6CC6A20E-96A4-4F94-A838-8E5EBE9E9925}", 1, 0 ' ScreenReaderHelper
.AddFromGuid "{22E0CB87-9325-4B0F-8ECC-21B271EC81AA}", 1, 0 ' DolbyDLLlib (from 2016 windows 10)
.AddFromGuid "{4486DF98-22A5-4F6B-BD5C-8CADCEC0A6DE}", 1, 0 'LocationApi 1.0 Type Library (from 2016 windows 10)
.AddFromGuid "{012F24C1-35B0-11D0-BF2D-0000E8D0D146}", 1, 0 ' ACTIVEXLib Microsoft Office Template and Media Control 1.0 Type Library
.AddFromGuid "{00020802-0000-0000-C000-000000000046}", 1, 9 'Microsoft Graph 16.0 Object Library
ElseIf Application.Version = 15 Then
On Error Resume Next
.AddFromGuid GUID_OfficeObject, 2, 8
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid GUID_OfficeObject, 2, 7
End If
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid GUID_OfficeObject, 2, 6
End If
'OutlookSocialProvider
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 1 'OutlookSocialProvider
If Err.Number <> 0 Then
Err.Clear
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 'OutlookSocialProvider
End If
' Office 2013 (version 15) でMajor,Minor番号が定まっていると考えられるもの
.AddFromGuid GUID_Excel, 1, 8
.AddFromGuid GUID_OUTLOOK, 9, 5
.AddFromGuid GUID_POWERPOINT, 2, 11
.AddFromGuid GUID_MSPub, 2, 2
.AddFromGuid "{3120BA9F-4FC8-4A4F-AE1E-02114F421D0A} ", 1, 0 'Microsoft OneNote 15.0 Extended Object Library 1.0
.AddFromGuid GUID_OLXLib, 1, 1
.AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 1 'eawfctrl 1.0 Type Library
.AddFromGuid GUID_WinWord, 8, 6 'Microsoft Word 15.0 Object Library
ElseIf Application.Version = 14 Then
.AddFromGuid "{E301A065-3DF5-4378-A829-57B1EA986631}", 1, 0 'OutlookSocialProvider 2013 以降はない
.AddFromGuid GUID_OfficeObject, 2, 5
.AddFromGuid GUID_Excel, 1, 7
.AddFromGuid GUID_OUTLOOK, 9, 4
.AddFromGuid GUID_POWERPOINT, 2, 10
.AddFromGuid GUID_MSPub, 2, 1
.AddFromGuid GUID_OLXLib, 1, 1
.AddFromGuid "{1F8E79BA-9268-4889-ADF3-6D2AABB3C32C}", 1, 0 'Microsoft Outlook Social Provider Extensibility
.AddFromGuid "{0EA692EE-BB50-4E3C-AEF0-356D91732725}", 1, 0 'Microsoft OneNote 14.0 Object Library
.AddFromGuid "{113D61B1-C7C0-4157-B694-43594E25DF45}", 1, 0 'eawfctrl 1.0 Type Library
.AddFromGuid GUID_WinWord, 8, 5 'Microsoft Word 14.0 Object Library
End If
End With
If Not refs Is Nothing Then Set refs = Nothing
Set refs = Application.VBE.ActiveVBProject.References 'Application.ReferencesではDescriptionが出ない。このため Refs を Nothing にして、 左のように書き換える
For Each ref In refs
If ref.IsBroken = False Then
Debug.Print ref.Name, ref.Guid, ref.Major, ref.Minor, ref.Description, ref.FullPath
Else
refs.Remove ref
End If
Next
End Sub
##8つの場合分け今のところ完全に動くか不明
###MSI版かC2R版か以前か
Office2013以降にしていますが、Office2013以前は大体Office15と書いてあるところを2010ならOffice12 に変えるなどすると動きます
Office16のC2R版はフォルダ名の前にRootが入るので根本的に違います。またMSI版の場合はOffice 16でもrootはつかない。
https://social.msdn.microsoft.com/Forums/ja-JP/3d02d7bd-4db9-4481-945a-8152e47dbdbd/vbe6extolb124343550112415367961241712414123791243512290?forum=vbajp
###64Bit(非推奨)32Bitか
バージョンのほかに64bitか32bitかで違います。
マイクロソフトは今まで32Bitを推奨しておきながら、先日「こっそりと」64bitを標準で入れるようにしています。
そのくせ互換性はカスです。なので絶対に64bitではなく32bitをいれてMicrosoftに非難のメールを送りましょう。
Office 2013(Office15)はなぜかOffice2016を参照している場合がある
バージョンアップでそうなったのか不明ですが、Office16のdll等を参照している場合があります。参照しているだけならいいのですが、その場合Office15のdllは削除されています。
ここでエラーが発生する可能性があります。
エラーが出る場合は一度手動で参照設定して、フルパスを確認し、異なる場合は訂正してください。
また、今後Office16以降でも発生する可能性があります
このように8通り以上の組み合わせがあり、すべてを検証するのが難しい
バージョンやビットの違いは何とかなっても、内部のファイルの構成状況でも変わるので絶対に動くものを作ることが非常に困難です。お使いになるときはエラーになる部分は訂正してご利用ください。
##ExcelとAccessの微妙な違い
###ExcelはObject Modelを信頼するにチェックが必要だが、Accessにはない
Access2019だけなのかな。ないです。
###Access は「開発」タブ自体がもともと存在しない
Access2019だけなのかな。ないです。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1289812492
というかもとからないですね。
###Access はApplication.Referencesを持っているため、Microsoft Visual Basic for Applications Extensibility 5.3を参照設定でする必要がない。ただし詳細プロパティがない
このため Application.References.Addfromfile というのがダイレクトに書けます。
しかしこれで作成されたReferencesコレクションオブジェクトは各ReferenceのDescriptionプロパティをを持っていません
###AccessではApplication.Referencesでは詳細が見えないので、詳細を見るためにはRefsを繋ぎ変える必要がある。
上記を受けた流れを下のコードから抜き出してみました。
Dim ref As Object, refs As Object
Dim i As Long
Set refs = Application.References
For i = refs.Count To 1 Step -1
With refs.Item(i)
Debug.Print .Name, , .FullPath ' この時はDescriptionは使えない
End With
Next
`----
If Not refs Is Nothing Then Set refs = Nothing
Set refs = Application.VBE.ActiveVBProject.References 'Application.ReferencesではDescriptionが出ない。このため Refs を Nothing にして、 左のように書き換える
For Each ref In refs
If ref.IsBroken = False Then
Debug.Print ref.Name, ref.Guid, ref.Major, ref.Minor, ref.Description, ref.FullPath
Else
refs.Remove ref
End If
Next
Application.Refencesで既存の参照設定を見ていますが、この時はDescriptionは使えずエラーになります。
refsはObjectとしてしか定義していないので、ここでrefsをNothingにしてVBE.ActiveVBProject.Referencesに書き換えます。
おそらく世界初の技術でしょう。
今回はたまたま参照設定をしていないため気が付きましが。ふつうはVBAExtにチェックをいれてやりますからこんな差異なんてきづかないと思います。
##特論
###Excel は 1行で基本的にVBIDEが設定できる
基本はこれです。Major,Minorは5,3です。
少なくとも、2010から同じです。
したがって、次の1行が中核のコードです。
Accessもできますが、Excelのほうがインパクトが大きいので。
Application.ActiveWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
###AddfromfileかAddFromGuidか
GUIDはおそらく64/32の違いを受けません。
しかしGUIDはMajor、Minorのバージョン番号を含めて３つの要素で定義します。
ところがこれを調べるのは非常に時間がかかります。
このため、あまり人気がありません。
なにより可読性がなく、何を設定しているのか一読しただけではわかりません。
ファイルからも面倒ですが、少なくともなにを見ているのかはわかります。
しかし、その方法だと、上記のように組み合わせも多くなってしまいます。できるだけ使いたいのですが、バージョン番号がネックです。
Publisher {0002123C-0000-0000-C000-000000000046} 2 3 Microsoft Publisher 16.0 Object Library C:\Program Files\Microsoft Office\Root\Office16\MSPUB.TLB
たとえばPublisher 2016 64bit相当の場合、Major,Minorは2.3になります。16ではないのです。Officeの番号と一致していればわかるのですが、それもできません。しかたなくGUID版を作りましたが、ファイル形式がフルパスだけわかればおおむね推定で64/32bitがわかるのですが、GUIDのバージョンは名称にもなにも関連が見いだせないことが多く、一つ一つ調べるのに時間がかかってしまいます。GUID一発ならいいのですが。。。
#####AddFromFileの存在意義
AddFromGUIDはGUIDとMajor、Minor番号の3つで構成されます。可読性は低いのですが、すべてわかっている場合には有効です。
しかしAcrobat、DropBox、Macafee（といってもMacafeeを参照設定をする例は知らないのですが)などMicrosoft以外の参照設定可能なライブラリはGUIDをマクロで表示させようとしてもできないことがわかりました。
また、これらのライブラリは64Bitでも%Probramfile(x86)%に存在しており、64/32bitの違いが影響しないことがわかりました。たとえばAcrobatの参照設定をVBAで組むときは以下のようにします。
With refs
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\AcroRd32.dll"
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\acrobat.tlb"
.AddFromFile "C:\Program Files (x86)\Adobe\Adobe Sync\CoreSync\CoreSync.exe" 'Cloudsync.com type library
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\plug_ins\Accessibility.api" 'Acrobat Access 3.0 Type Library
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\acrodistdll.dll" 'Acrobat Distiller
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\plug_ins\Scan.api" ' Acrobat Scan 1.0 Type library
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\plug_ins\WebPDF.api" ' Acrobat Webcapture 1.0 Type library
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\plug_ins" ' Acrobat 10.0 Type Library
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\plug_ins" ' AcroBrokerlib
.AddFromFile "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Adist64.dll" ' Adobe Acrobat File Preview Type Library
End With
###Addfromfileで環境変数は使えないらしい
"C:\Program Files\Microsoft Office\root\Office16\MSPUB.TLB"を"%PROGRAMFILES%Microsoft Office\root\Office16\MSPUB.TLB"としても読み込みません。
これにはWscript.Shellのexpandenvironmentstringsが必要なのですが、いかんせん、それを参照設定しようとしているため使えません
。
###Regular Expression Ver5.5正規表現のファイル名は\3で終わる
"*\vbscript.dll\3"
ちなみに
"*\vbscript.dll\2"
だとVersion1.0になる。この2つは同時に設定できない。この違いは以前記事にしたので参照していただきたい。5.5を使ったほうがよい。
"*\vbscript.dll"
それではこの場合どうなるかというとなぜかVBScript Global Objectになる。
ほかにも\1のようになるものがいくつかありますが、いちばん有名なのはこれだと思います。
###マクロの設定はやばそうだがこの道しかない
あとはマクロが使えないか、証明書をとるという非現実的な方法になる。
なんの解決にもならない。MOS資格やVBAで事務改善の本があふれ、プログラミング教育などと言っているが、システム管理者は敵視しているため、この業界の精神はどうなっているのか。でたらめ極まりないのでPCのシステムを扱う資質に欠けていると言わざるを得ない。
###Microsoft Visual Basic for Applications Extensibility 5.3のどす黒さ
実はこのフォルダは実在しない
C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
64bit C2R版の場合
"C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX86\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
64bit MSI版の場合はROOTがあったりなかったりする
###Excel 2013 Later AddFromFile 版
Sub refhasetxl2013Later()
' For Microsoft Excel 2013 Later
Dim xlobj: Set xlobj = GetObject(, "Excel.Application")
Dim xlProj: Set xlProj = xlobj.ActiveWorkbook.VBProject
Dim refs As Object, ref As Object
Set refs = xlProj.References
For Each ref In refs
If ref.Name = "VBIDE" Then refs.Remove ref
Next
refs.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
On Error Resume Next
'Microsoft ActiveX data libarry
#If Win64 Then
Set ref = refs.AddFromFile(Filename:="C:\Program Files\Common Files\System\ado\msado15.dll")
refs.AddFromFile Filename:="C:\Program Files\Common Files\System\ado\msador15.dll"
refs.AddFromFile Filename:="C:\Program Files\Common Files\System\ado\msadox.dll"
'Scripting.filesystemobject,Scripting.Dictionary
refs.AddFromFile "C:\Windows\System32\scrrun.dll"
refs.AddFromFile "C:\Windows\System32\mshtml.tlb" 'Microsoft HTML Object Library
' Microsoft CDO for Windows 2000 Library
Set ref = refs.AddFromFile("C:\Windows\system32\cdosys.dll")
' WSHController Library
Set ref = refs.AddFromFile("C:\Windows\System32\wshcon.dll")
' Microsoft Shell Contorols And Automations
Set ref = refs.AddFromFile("C:\Windows\System32\shell32.dll")
' Microsoft VBScript Regular expresssion
Set ref = refs.AddFromFile("C:\Windows\System32\vbscript.dll\3")
' Microsoft Word object libraly
If Application.Version >= 16 Then
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSPUB.TLB")
ElseIf Application.Version = 15 Then
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSWORD.OLB")
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSACC.OLB")
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSPUB.TLB")
End If
' Microsoft XML v6.0
Set ref = refs.AddFromFile("C:\Windows\System32\msxml6.dll")
Set ref = refs.AddFromFile("C:\Windows\System32\winhttpcom.dll")
' Windows Script Host Object Model
Set ref = refs.AddFromFile("C:\Windows\System32\wshom.ocx")
#Else
'Microsoft ActiveX data libary
Set ref = refs.AddFromFile("C:\Program Files (x86)\Common Files\System\ado\msado15.dll")
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msador15.dll"
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msadox.dll"
'Scripting.filesystemobject,Scripting.Dictionary
refs.AddFromFile "C:\Windows\SysWOW64\scrrun.dll"
refs.AddFromFile "C:\Windows\SysWOW64\mshtml.tlb" 'Microsoft HTML Object Library
' Microsoft CDO for Windows 2000 Library
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\cdosys.dll")
' WSHController Library Wscript.Shell
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\wshcon.dll")
' Microsoft Shell Contorols And Automations
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\Shell32.dll")
' Microsoft VBScript Regular expresssion
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\vbscript.dll\3")
' Microsoft Word object libraly
If Application.Version >= 16 Then
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB")
ElseIf Application.Version = 15 Then
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSWORD.OLB")
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSACC.OLB")
End If
' Microsoft XML v6.0
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\msxml6.dll")
Set ref = refs.AddFromFile("C:\Windows\System32\winhttp.dll")
' Windows Script Host Object Model
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\wshom.ocx")
#End If
refs.AddFromFile "C:\Windows\SysWOW64\msjets40.dll" 'Jet expression Service Type Library
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msjro.dll" 'Jet And Replication objects (JRO)
Set ref = refs.AddFromFile("C:\Windows\System32\wiaaut.dll") 'WIA Winddows Image Acqusition
#If Win64 Then
If Application.Version = 15 Then
With CreateObject("Scriptiong.filesystemObject")
If .FileExists("C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL") Then
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
Else
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE15\ACEDAO.DLL"
End If
End With
Else
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
End If
#Else
If Application.Version = 15 Then
With CreateObject("Scriptiong.filesystemObject")
If .FileExists("C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL") Then
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
Else
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE15\ACEDAO.DLL"
End If
End With
Else
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
End If
#End If
For Each ref In refs
If ref.IsBroken = False Then
Debug.Print ref.Name, ref.GUID, ref.Major, ref.Minor, ref.Description, ref.FullPath
Else
refs.Remove ref
End If
Next
End Sub
###Access 2013 Later AddFromFile版
もとはAddFromFile版をMainにしていましたが、今のOfficeはMSI版とC2R版でもフォルダの位置が違い、64/32Bitでも異なり、とても大変なのでGUID版に移行しました。
Sub refsetAccee2013Later()
' For Microsoft Access 2013 Later 64/32
Dim ref As Object, refs As Object
Dim i As Long
Set refs = Application.References
For i = refs.Count To 1 Step -1
With refs.Item(i)
Debug.Print .Name, , .FullPath ' この時はDescriptionは使えない
End With
Next
For Each ref In refs
If ref.Name = "VBIDE" Then refs.Remove ref
Next
refs.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
On Error Resume Next
'Microsoft ActiveX data libarry
#If Win64 Then
Set ref = refs.AddFromFile(FileName:="C:\Program Files\Common Files\System\ado\msado15.dll")
refs.AddFromFile FileName:="C:\Program Files\Common Files\System\ado\msador15.dll"
refs.AddFromFile FileName:="C:\Program Files\Common Files\System\ado\msadox.dll"
' mscorelee MsoEuro
refs.AddFromFile FileName:="C:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscoree.tlb"
' Microsoft Office Euro Converter Object Library
refs.AddFromFile FileName:="C:\Program Files\Common Files\Microsoft Shared\EURO\MSOEURO.DLL"
'Scripting.filesystemobject,Scripting.Dictionary
refs.AddFromFile "C:\Windows\System32\scrrun.dll"
refs.AddFromFile "C:\Windows\System32\mshtml.tlb" 'Microsoft HTML Object Library
' Microsoft CDO for Windows 2000 Library
Set ref = refs.AddFromFile("C:\Windows\system32\cdosys.dll")
'Microsoft IMAPI2 Base Functinality,MiCrosoft IMAPI2 File System Image Creator
refs.AddFromFile "C:\Windows\System32\imapi2.dll"
refs.AddFromFile "C:\Windows\System32\imapi2fs.dll"
'Microsoft Internet Controls ( Internet explorer )
refs.AddFromFile "C:\Windows\System32\ieframe.dll"
' WSHController Library
Set ref = refs.AddFromFile("C:\Windows\System32\wshcon.dll")
' Microsoft Shell Contorols And Automations
Set ref = refs.AddFromFile("C:\Windows\System32\shell32.dll")
' Microsoft VBScript Regular expresssion
Set ref = refs.AddFromFile("C:\Windows\System32\vbscript.dll\3")
' Microsoft Word object libraly
If Application.Version >= 16 Then
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSPUB.TLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSOUTL.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSPPT.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\GRAPH.EXE")
refs.AddFromFile "C:\Program Files\Microsoft Office\root\Office16\OUTLCTL.DLL"
refs.AddFromFile "C:\Program Files\Microsoft Office\root\Office16\MSPPT.OLB"
refs.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL"
refs.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSOEURO.DLL"
refs.AddFromFile FileName:="C:\Program Files\Microsoft Office\Root\Office16\EAWFCTRL.DLL"
ElseIf Application.Version = 15 Then
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\Office15\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\Office15\MSACC.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\Office15\MSPUB.TLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\Office15\EXCEL.EXE")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\Office15\GRAPH.EXE")
refs.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSOUTL.OLB"
refs.AddFromFile "C:\Program Files\Microsoft Office\Office15\OUTLCTL.DLL"
refs.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSPPT.OLB"
refs.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSO.DLL"
refs.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSOEURO.DLL"
refs.AddFromFile FileName:="C:\Program Files\Microsoft Office\Office15\EAWFCTRL.DLL"
End If
' Microsoft XML v6.0
Set ref = refs.AddFromFile("C:\Windows\System32\msxml6.dll")
Set ref = refs.AddFromFile("C:\Windows\System32\winhttpcom.dll")
' Windows Script Host Object Model
Set ref = refs.AddFromFile("C:\Windows\System32\wshom.ocx")
#Else
'Microsoft ActiveX data libary
Set ref = refs.AddFromFile("C:\Program Files (x86)\Common Files\System\ado\msado15.dll")
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msador15.dll"
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msadox.dll"
'Scripting.filesystemobject,Scripting.Dictionary
refs.AddFromFile "C:\Windows\SysWOW64\scrrun.dll"
refs.AddFromFile "C:\Windows\SysWOW64\mshtml.tlb" 'Microsoft HTML Object Library
' Microsoft CDO for Windows 2000 Library
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\cdosys.dll")
'Microsoft IMAPI2 Base Functinality,MiCrosoft IMAPI2 File System Image Creator
refs.AddFromFile "C:\Windows\SysWOW64\imapi2.dll"
refs.AddFromFile "C:\Windows\SysWOW64\imapi2fs.dll"
'Microsoft Internet Controls ( Internet explorer )
refs.AddFromFile "C:\Windows\SysWOW64\ieframe.dll"
' WSHController Library Wscript.Shell
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\wshcon.dll")
' Microsoft Shell Contorols And Automations
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\Shell32.dll")
' Microsoft VBScript Regular expresssion
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\vbscript.dll\3")
' Microsoft Word object libraly
If Application.Version >= 16 Then
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB")
ElseIf Application.Version = 15 Then
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSWORD.OLB")
Set ref = refs.AddFromFile("%Programfile%\Microsoft Office\Office15\MSACC.OLB")
End If
' Microsoft XML v6.0
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\msxml6.dll")
Set ref = refs.AddFromFile("C:\Windows\System32\winhttp.dll")
' Windows Script Host Object Model
Set ref = refs.AddFromFile("C:\Windows\SysWOW64\wshom.ocx")
#End If
refs.AddFromFile "C:\Windows\SysWOW64\msjtes40.dll" 'Jet expression Service Type Library
refs.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msjro.dll" 'Jet And Replication objects (JRO)
Set ref = refs.AddFromFile("C:\Windows\System32\wiaaut.dll") 'WIA Winddows Image Acqusition
#If Win64 Then
If Application.Version = 15 Then
With CreateObject("Scriptiong.filesystemObject")
If .FileExists("C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL") Then
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
Else
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE15\ACEDAO.DLL"
End If
End With
Else
refs.AddFromFile "C:\Program Files\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
End If
If Application.Version >= 16 Then
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\root\Office16\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\root\Office16\MSACC.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\root\Office16\MSPUB.TLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\root\Office16\MSOUTL.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\root\Office16\MSPPT.OLB")
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\root\Office16\OUTLCTL.DLL"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\root\Office16\MSPPT.OLB"
refs.AddFromFile "C:\Program Files(X86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL"
refs.AddFromFile "C:\Program Files(X86)\Common Files\Microsoft Shared\OFFICE16\MSOEURO.DLL"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\root\Office16\EAWFCTRL.DLL"
ElseIf Application.Version = 15 Then
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\Office15\MSWORD.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\Office15\MSACC.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\Office15\MSPUB.TLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft Office\Office15\MSOUTL.OLB")
Set ref = refs.AddFromFile("C:\Program Files(X86)\Microsoft OfficeMSPPT.OLB")
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\Office15\OUTLCTL.DLL"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\Office15\MSPPT.OLB"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\Office15\MSO.DLL"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\Office15\MSOEURO.DLL"
refs.AddFromFile "C:\Program Files(X86)\Microsoft Office\Office15\EAWFCTRL.DLL"
End If
#Else
If Application.Version = 15 Then
With CreateObject("Scriptiong.filesystemObject")
If .FileExists("C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL") Then
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
Else
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE15\ACEDAO.DLL"
End If
End With
Else
refs.AddFromFile "C:\Program Files(x86)\Common Files\microsoft shared\OFFICE16\ACEDAO.DLL"
End If
#End If
If Not refs Is Nothing Then Set refs = Nothing
Set refs = Application.VBE.ActiveVBProject.References 'Application.ReferencesではDescriptionが出ない。このため Refs を Nothing にして、 左のように書き換える
For Each ref In refs
If ref.IsBroken = False Then
Debug.Print ref.Name, ref.Guid, ref.Major, ref.Minor, ref.Description, ref.FullPath
Else
refs.Remove ref
End If
Next
End Sub