gpt4 book ai didi

excel - 如何更改 Excel 2010 中的默认 VBA 引用

转载 作者:行者123 更新时间:2023-12-02 14:48:28 25 4
gpt4 key购买 nike

我想向personal.xlsb添加一个vba函数,但该函数中有一个ADODB.Connection对象。

我可以通过(在 VBA 编辑器中)选择工具 -> 引用,然后选中“Microsoft ActiveX 数据对象 6.0 库”框来解决该问题

我的问题是,如何将“Microsoft ActiveX Data Objects 6.0 Library”设为我的默认引用之一,以便每次启动 Excel 时它始终可用?

最佳答案

虽然 Siddharth 的解决方案非常方便,但我想我应该通过我推出的工具来提供这些功能,在该工具中,我们不断收到与用户在 Excel 中的 VBE 中没有检查正确引用相关的电话版本,由于多种可能的原因。

只是把它作为一个选项和一些东西来看看。其中有一些非常特定于项目的功能,但您可以很容易地对其进行修改以满足您的需求。

Function ValidateReferences() As Boolean
'makes sure that user has the proper references installed for downloading data through API

Dim arrDescription() As String, arrGUID() As String
Dim arrMajor() As Long, arrMinor() As Long
Dim strMessage As String


ValidateReferences = True

'removes any broken references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
If ThisWorkbook.VBProject.References.Item(i).IsBroken Then ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item(i)
Next

'will attempt to add the reference for the user
arrGUID() = Split("{2A75196C-D9EB-4129-B803-931327F72D5C},{E6C9285A-7A87-407A-85E7-D77A70C100F5},{45A929B3-E493-4173-B6E5-0CD42041C6DC},{F24B7FA2-8FB9-48B7-825F-7C9F4A82F917},{7A80DAB5-1F61-4F9A-A596-561212ACD705},{18142BD6-1DE1-412B-991C-31C7449389E6},{C3ED6DC2-BED0-4599-A170-B1E1E32C627A}", ",", , vbTextCompare) ' {2A75196C-D9EB-4129-B803-931327F72D5C}
arrDescription() = Split("Microsoft ActiveX Data Objects 2.8 Library,myDummyRef COM 3.8.0,myDummyRef COM 3.8.0,myDummyRef COM 3.8.0,myDummyRef 3.6.0 Library,myDummyRef,myDummyRef Library", ",", , vbTextCompare) 'Microsoft ActiveX Data Objects 2.8 Library
ReDim arrMajor(6)
arrMajor(0) = 0 '0
arrMajor(1) = 3 '3
arrMajor(2) = 3 '3
arrMajor(3) = 3 '3
arrMajor(4) = 3 '3
arrMajor(5) = 1 '1
arrMajor(6) = 1 '1 -> ADODB = 2
ReDim arrMinor(6)
arrMinor(0) = 0
arrMinor(1) = 8
arrMinor(2) = 8
arrMinor(3) = 8
arrMinor(4) = 6
arrMinor(5) = 0
arrMinor(6) = 0 '-> ADODB = 8

For i = LBound(arrGUID()) To UBound(arrGUID())
On Error GoTo ErrCheck
If i = 0 Then 'adodb not working on AddFromFile. I could use add from file on all references, perhaps? -> refactor later
ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Common Files\System\ado\msado15.dll")
Else
ThisWorkbook.VBProject.References.AddFromGuid arrGUID(i), arrMajor(i), arrMinor(i)
End If
Next

If ValidateReferences = False Then MsgBox "The following references could not be added to the VB Project: " & Right(strMessage, Len(strMessage) - 2) & "." & vbNewLine & vbNewLine & "Please refer to 4. Appropriate DLL Files on the 'Connectivity_Help' tab for more information."

Exit Function

ErrCheck:
Select Case Err.Number
Case Is = 32813 'reference already in use, nothing to report
Case Else
ValidateReferences = False
strMessage = strMessage & ", " & arrDescription(i)
End Select
Resume Next

End Function

此函数将打印您的引用信息,以帮助您找到它的 dll,但必须检查引用才能打印它。

Sub print_ref_path()

Dim i As Integer

For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Dim strName As String, strPath As String, strGUID as String
strName = ThisWorkbook.VBProject.References.Item(i).name
Debug.Print strName
strPath = ThisWorkbook.VBProject.References.Item(i).FullPath
Debug.Print strPath
strGUID = ThisWorkbook.VBProject.References.Item(i).GUID 'think this is right, might need to change
Next

End Sub

关于excel - 如何更改 Excel 2010 中的默认 VBA 引用,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12515244/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com