Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
I woud like a macro to set a library reference. I know how to do it by going
into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
This will set a reference to ADO:
Sub AddADO() Dim r For Each r In ThisWorkbook.VBProject.References If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2 Then Exit Sub End If Next On Error GoTo NOTFOUND 'although usually the ADO version will be higher, doing Minor:=0 will install 'the higher version if available. On the other hand when you specify Minor:=5 'and only a lower version is available, this can't be installed '---------------------------------------------------------------------------- ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _ Major:=2, Minor:=0 Exit Sub NOTFOUND: On Error GoTo 0 End Sub To get the needed values to set this code up use this code, which will get you the above values of installed libraries: Sub GetLibraryGUID() Dim c As Byte Dim myCheck As Long Dim P As Boolean Dim rng As Range Dim i As Byte c = ActiveWorkbook.VBProject.References.Count On Error Resume Next Dim Message, Title, Default, T As Single Message = "NUMBER ?" & Chr(13) & "________" Title = " GET REFERENCES GUID ( 1 TO " & c & " )" Default = c T = InputBox(Message, Title, Default, 3500, 3500) If Not T Mod 1 = 0 Then Exit Sub End If If T < 1 Or T c Then Exit Sub End If MsgBox "REFERENCE ( " & T & " ) NAME : " & _ ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _ "MAJOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Major & _ vbCrLf & vbCrLf & "MINOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Minor & _ vbCrLf & vbCrLf & _ "GUID ( " & T & " ) : " & _ ActiveWorkbook.VBProject.References.Item(T).GUID, , _ " REFERENCES GUID : ITEM " & T myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If If ActiveSheet.ProtectContents = True Then P = True ActiveSheet.Unprotect Else P = False End If Range(Cells(ActiveCell.Row, ActiveCell.Column), _ Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select For Each rng In Selection.Cells If Not IsEmpty(rng) Then i = i + 1 End If Next If i 0 Then myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If End If On Error Resume Next ActiveCell.Value = "NAME :" ActiveCell.Offset(1, 0).Value = "MAJOR :" ActiveCell.Offset(2, 0).Value = "MINOR :" ActiveCell.Offset(3, 0).Value = "GUID :" ActiveCell.Offset(0, 1).Value = _ ActiveWorkbook.VBProject.References(T).Name ActiveCell.Offset(1, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Major ActiveCell.Offset(2, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Minor ActiveCell.Offset(3, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).GUID If P = True Then ActiveSheet.Protect End If End Sub RBS "Chad" wrote in message ... I woud like a macro to set a library reference. I know how to do it by going into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
This works great. It appears your knowledge is much greater than mine. So,
will you help me with the actual reference I am trying to use - the Microsoft Outlook (I'm using 9.0) Object Library? Many thanks! -Chad "RB Smissaert" wrote: This will set a reference to ADO: Sub AddADO() Dim r For Each r In ThisWorkbook.VBProject.References If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2 Then Exit Sub End If Next On Error GoTo NOTFOUND 'although usually the ADO version will be higher, doing Minor:=0 will install 'the higher version if available. On the other hand when you specify Minor:=5 'and only a lower version is available, this can't be installed '---------------------------------------------------------------------------- ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _ Major:=2, Minor:=0 Exit Sub NOTFOUND: On Error GoTo 0 End Sub To get the needed values to set this code up use this code, which will get you the above values of installed libraries: Sub GetLibraryGUID() Dim c As Byte Dim myCheck As Long Dim P As Boolean Dim rng As Range Dim i As Byte c = ActiveWorkbook.VBProject.References.Count On Error Resume Next Dim Message, Title, Default, T As Single Message = "NUMBER ?" & Chr(13) & "________" Title = " GET REFERENCES GUID ( 1 TO " & c & " )" Default = c T = InputBox(Message, Title, Default, 3500, 3500) If Not T Mod 1 = 0 Then Exit Sub End If If T < 1 Or T c Then Exit Sub End If MsgBox "REFERENCE ( " & T & " ) NAME : " & _ ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _ "MAJOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Major & _ vbCrLf & vbCrLf & "MINOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Minor & _ vbCrLf & vbCrLf & _ "GUID ( " & T & " ) : " & _ ActiveWorkbook.VBProject.References.Item(T).GUID, , _ " REFERENCES GUID : ITEM " & T myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If If ActiveSheet.ProtectContents = True Then P = True ActiveSheet.Unprotect Else P = False End If Range(Cells(ActiveCell.Row, ActiveCell.Column), _ Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select For Each rng In Selection.Cells If Not IsEmpty(rng) Then i = i + 1 End If Next If i 0 Then myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If End If On Error Resume Next ActiveCell.Value = "NAME :" ActiveCell.Offset(1, 0).Value = "MAJOR :" ActiveCell.Offset(2, 0).Value = "MINOR :" ActiveCell.Offset(3, 0).Value = "GUID :" ActiveCell.Offset(0, 1).Value = _ ActiveWorkbook.VBProject.References(T).Name ActiveCell.Offset(1, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Major ActiveCell.Offset(2, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Minor ActiveCell.Offset(3, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).GUID If P = True Then ActiveSheet.Protect End If End Sub RBS "Chad" wrote in message ... I woud like a macro to set a library reference. I know how to do it by going into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
He showed you in his second subroutine of GETGUIID or named similar. That
function should give you the long piece of data that refers to the object library you want. Now I haven't run it, but it looks like an excellent utility if everything functions as written. "Chad" wrote: This works great. It appears your knowledge is much greater than mine. So, will you help me with the actual reference I am trying to use - the Microsoft Outlook (I'm using 9.0) Object Library? Many thanks! -Chad "RB Smissaert" wrote: This will set a reference to ADO: Sub AddADO() Dim r For Each r In ThisWorkbook.VBProject.References If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2 Then Exit Sub End If Next On Error GoTo NOTFOUND 'although usually the ADO version will be higher, doing Minor:=0 will install 'the higher version if available. On the other hand when you specify Minor:=5 'and only a lower version is available, this can't be installed '---------------------------------------------------------------------------- ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _ Major:=2, Minor:=0 Exit Sub NOTFOUND: On Error GoTo 0 End Sub To get the needed values to set this code up use this code, which will get you the above values of installed libraries: Sub GetLibraryGUID() Dim c As Byte Dim myCheck As Long Dim P As Boolean Dim rng As Range Dim i As Byte c = ActiveWorkbook.VBProject.References.Count On Error Resume Next Dim Message, Title, Default, T As Single Message = "NUMBER ?" & Chr(13) & "________" Title = " GET REFERENCES GUID ( 1 TO " & c & " )" Default = c T = InputBox(Message, Title, Default, 3500, 3500) If Not T Mod 1 = 0 Then Exit Sub End If If T < 1 Or T c Then Exit Sub End If MsgBox "REFERENCE ( " & T & " ) NAME : " & _ ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _ "MAJOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Major & _ vbCrLf & vbCrLf & "MINOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Minor & _ vbCrLf & vbCrLf & _ "GUID ( " & T & " ) : " & _ ActiveWorkbook.VBProject.References.Item(T).GUID, , _ " REFERENCES GUID : ITEM " & T myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If If ActiveSheet.ProtectContents = True Then P = True ActiveSheet.Unprotect Else P = False End If Range(Cells(ActiveCell.Row, ActiveCell.Column), _ Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select For Each rng In Selection.Cells If Not IsEmpty(rng) Then i = i + 1 End If Next If i 0 Then myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If End If On Error Resume Next ActiveCell.Value = "NAME :" ActiveCell.Offset(1, 0).Value = "MAJOR :" ActiveCell.Offset(2, 0).Value = "MINOR :" ActiveCell.Offset(3, 0).Value = "GUID :" ActiveCell.Offset(0, 1).Value = _ ActiveWorkbook.VBProject.References(T).Name ActiveCell.Offset(1, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Major ActiveCell.Offset(2, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Minor ActiveCell.Offset(3, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).GUID If P = True Then ActiveSheet.Protect End If End Sub RBS "Chad" wrote in message ... I woud like a macro to set a library reference. I know how to do it by going into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
I got it now. Thanks to both of you for all your help!! This works perfect.
-Chad "GB" wrote: He showed you in his second subroutine of GETGUIID or named similar. That function should give you the long piece of data that refers to the object library you want. Now I haven't run it, but it looks like an excellent utility if everything functions as written. "Chad" wrote: This works great. It appears your knowledge is much greater than mine. So, will you help me with the actual reference I am trying to use - the Microsoft Outlook (I'm using 9.0) Object Library? Many thanks! -Chad "RB Smissaert" wrote: This will set a reference to ADO: Sub AddADO() Dim r For Each r In ThisWorkbook.VBProject.References If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2 Then Exit Sub End If Next On Error GoTo NOTFOUND 'although usually the ADO version will be higher, doing Minor:=0 will install 'the higher version if available. On the other hand when you specify Minor:=5 'and only a lower version is available, this can't be installed '---------------------------------------------------------------------------- ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _ Major:=2, Minor:=0 Exit Sub NOTFOUND: On Error GoTo 0 End Sub To get the needed values to set this code up use this code, which will get you the above values of installed libraries: Sub GetLibraryGUID() Dim c As Byte Dim myCheck As Long Dim P As Boolean Dim rng As Range Dim i As Byte c = ActiveWorkbook.VBProject.References.Count On Error Resume Next Dim Message, Title, Default, T As Single Message = "NUMBER ?" & Chr(13) & "________" Title = " GET REFERENCES GUID ( 1 TO " & c & " )" Default = c T = InputBox(Message, Title, Default, 3500, 3500) If Not T Mod 1 = 0 Then Exit Sub End If If T < 1 Or T c Then Exit Sub End If MsgBox "REFERENCE ( " & T & " ) NAME : " & _ ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _ "MAJOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Major & _ vbCrLf & vbCrLf & "MINOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Minor & _ vbCrLf & vbCrLf & _ "GUID ( " & T & " ) : " & _ ActiveWorkbook.VBProject.References.Item(T).GUID, , _ " REFERENCES GUID : ITEM " & T myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If If ActiveSheet.ProtectContents = True Then P = True ActiveSheet.Unprotect Else P = False End If Range(Cells(ActiveCell.Row, ActiveCell.Column), _ Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select For Each rng In Selection.Cells If Not IsEmpty(rng) Then i = i + 1 End If Next If i 0 Then myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If End If On Error Resume Next ActiveCell.Value = "NAME :" ActiveCell.Offset(1, 0).Value = "MAJOR :" ActiveCell.Offset(2, 0).Value = "MINOR :" ActiveCell.Offset(3, 0).Value = "GUID :" ActiveCell.Offset(0, 1).Value = _ ActiveWorkbook.VBProject.References(T).Name ActiveCell.Offset(1, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Major ActiveCell.Offset(2, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Minor ActiveCell.Offset(3, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).GUID If P = True Then ActiveSheet.Protect End If End Sub RBS "Chad" wrote in message ... I woud like a macro to set a library reference. I know how to do it by going into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference
Hope these are correct for all versions to add "latest" version -
Function AddOfficeRef(wb As Workbook, sApp As String) Dim ref As Object Dim refs As Object Dim sID As String On Error Resume Next Set refs = wb.VBProject.References If refs Is Nothing Then MsgBox "need to change security settings" Exit Function End If On Error GoTo 0 sApp = UCase(sApp) Select Case sApp Case "WORD" sID = "{00020905-0000-0000-C000-000000000046}" Case "OUTLOOK" sID = "{00062FFF-0000-0000-C000-000000000046}" Case "ACCESS" sID = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}" Case Else MsgBox sApp & " not known" End Select If Len(sID) = 0 Then Exit Function For Each ref In refs If ref.GUID = sID Then Exit Function End If Next On Error GoTo errH refs.AddFromGuid sID, 0, 0 Exit Function errH: MsgBox "Error setting ref to " & sApp End Function Sub test() AddOfficeRef ThisWorkbook, "Outlook" End Sub Regards, Peter T "Chad" wrote in message ... This works great. It appears your knowledge is much greater than mine. So, will you help me with the actual reference I am trying to use - the Microsoft Outlook (I'm using 9.0) Object Library? Many thanks! -Chad "RB Smissaert" wrote: This will set a reference to ADO: Sub AddADO() Dim r For Each r In ThisWorkbook.VBProject.References If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2 Then Exit Sub End If Next On Error GoTo NOTFOUND 'although usually the ADO version will be higher, doing Minor:=0 will install 'the higher version if available. On the other hand when you specify Minor:=5 'and only a lower version is available, this can't be installed '--------------------------------------------------------------------------- - ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _ Major:=2, Minor:=0 Exit Sub NOTFOUND: On Error GoTo 0 End Sub To get the needed values to set this code up use this code, which will get you the above values of installed libraries: Sub GetLibraryGUID() Dim c As Byte Dim myCheck As Long Dim P As Boolean Dim rng As Range Dim i As Byte c = ActiveWorkbook.VBProject.References.Count On Error Resume Next Dim Message, Title, Default, T As Single Message = "NUMBER ?" & Chr(13) & "________" Title = " GET REFERENCES GUID ( 1 TO " & c & " )" Default = c T = InputBox(Message, Title, Default, 3500, 3500) If Not T Mod 1 = 0 Then Exit Sub End If If T < 1 Or T c Then Exit Sub End If MsgBox "REFERENCE ( " & T & " ) NAME : " & _ ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _ "MAJOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Major & _ vbCrLf & vbCrLf & "MINOR : " & _ ActiveWorkbook.VBProject.References.Item(T).Minor & _ vbCrLf & vbCrLf & _ "GUID ( " & T & " ) : " & _ ActiveWorkbook.VBProject.References.Item(T).GUID, , _ " REFERENCES GUID : ITEM " & T myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If If ActiveSheet.ProtectContents = True Then P = True ActiveSheet.Unprotect Else P = False End If Range(Cells(ActiveCell.Row, ActiveCell.Column), _ Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select For Each rng In Selection.Cells If Not IsEmpty(rng) Then i = i + 1 End If Next If i 0 Then myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _ vbYesNo, " GetLibraryGUID") If myCheck = vbNo Then Exit Sub End If End If On Error Resume Next ActiveCell.Value = "NAME :" ActiveCell.Offset(1, 0).Value = "MAJOR :" ActiveCell.Offset(2, 0).Value = "MINOR :" ActiveCell.Offset(3, 0).Value = "GUID :" ActiveCell.Offset(0, 1).Value = _ ActiveWorkbook.VBProject.References(T).Name ActiveCell.Offset(1, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Major ActiveCell.Offset(2, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).Minor ActiveCell.Offset(3, 1).Value = _ ActiveWorkbook.VBProject.References.Item(T).GUID If P = True Then ActiveSheet.Protect End If End Sub RBS "Chad" wrote in message ... I woud like a macro to set a library reference. I know how to do it by going into VBA and selecting Tools\References and selecting the appropriate library reference. However, I would like to establish code to perform this operation. Any thoughts? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) |