Quellcode einfügen excel 2003 - vba
Hallo liebe Leute!
Es geht um folgendes.Ich hab einen Quellcode aus einer anderen Abteilung bekommen, der es mir ermöglicht die Adresssuche im Outlook in einem Excelsheet zu öffnen.
Allerdings versteh ich den Code nicht und weiß nicht wo ich den reinkopieren soll.
Er soll in das sheet "WorkingGroupList". Wie kann ich den Code darein kopieren? Der Clue ist,dass ich das Makro nicht einer Schaltfläche zuweisen muss,sonder es immer da funktioniert,wo die Zeile ="x" ist und die Spalte ="y" !
Hoffe jemand kann mir das erklären
Das ist der Code:
Sub ADNameQuery(Name As String, iRow As Long, iCol As Long)
'Es folgen Makros für das Aufrufen des Outlook Adressbuchs
Dim GC As Object, Child As Object, rootDomaine As Object, objConnection As Object
Dim objCommand As Object, objRecordSet
Set GC = GetObject("GC:")
For Each Child In GC
Set rootDomain = Child
Next
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "<GC://" & rootDomain.Name & ">;(samAccountName=" & Name & ");displayName,telephoneNumber,mobile,mail,sn,givenName,department,physicalDeliveryOfficeName,co;subTree"
Set objRecordSet = objCommand.Execute
If Not objRecordSet.EOF Then
Cells(iRow, iCol).Value = objRecordSet.Fields("sn").Value
Cells(iRow + 1, iCol).Value = objRecordSet.Fields("givenName").Value
Cells(iRow + 4, iCol) = objRecordSet.Fields("mail").Value
Cells(iRow + 5, iCol).Value = objRecordSet.Fields("department").Value
Cells(iRow + 2, iCol).Value = objRecordSet.Fields("telephoneNumber").Value
Cells(iRow + 3, iCol).Value = objRecordSet.Fields("mobile").Value
'Cells(iRow, iCol).Value = objRecordSet.Fields("displayName").Value
'Cells(iRow + 7, iCol).Value = objRecordSet.Fields("physicalDeliveryOfficeName").Value
'Cells(iRow + 6, iCol).Value = objRecordSet.Fields("co").Value
Cells(iRow - 1, iCol).Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Nam As String, iRow As Long, iCol As Long, Memo As String
If Target.Cells.Count > 1 Then GoTo ende
iRow = Target.Row
iCol = Target.Column
If Cells(1, iCol).Value = "x" And Cells(iRow, 1).Value = "y" Then
Target.Value = "Bitte warten!"
Call Wer(Memo, Nam)
Target.Value = ""
End If
If Memo <> "" Then Call ADNameQuery(Memo, iRow, iCol)
ende:
End Sub
Sub Wer(ByRef Memo As String, ByRef Nam As String)
Dim objRecipColl As Object, OutLookObject As Object, NmSpace As Object
Set OutLookObject = CreateObject("Outlook.Application")
Set NmSpace = CreateObject("MAPI.Session")
If Not NmSpace Is Nothing Then
NmSpace.logon "", "", False, False
End If
Memo = ""
Nam = ""
On Error GoTo ende
Set objRecipColl = NmSpace.AddressBook()
Memo = Memoid(objRecipColl.Item(1).Address)
Nam = objRecipColl.Item(1).Name
ende:
Err.Clear
On Error GoTo 0
End Sub
Function Memoid(Nam As String) As String
Dim i As Long, j As Long
j = 1
nochmal:
i = InStr(j, Nam, "cn=", vbTextCompare)
If i > 0 Then
j = i + 1
GoTo nochmal
End If
If j > 1 Then
Memoid = Right(Nam, Len(Nam) - j - 1)
Else
Memoid = ""
End If
End Function
Allerdings versteh ich den Code nicht und weiß nicht wo ich den reinkopieren soll.
Er soll in das sheet "WorkingGroupList". Wie kann ich den Code darein kopieren? Der Clue ist,dass ich das Makro nicht einer Schaltfläche zuweisen muss,sonder es immer da funktioniert,wo die Zeile ="x" ist und die Spalte ="y" !
Hoffe jemand kann mir das erklären
Das ist der Code:
Sub ADNameQuery(Name As String, iRow As Long, iCol As Long)
'Es folgen Makros für das Aufrufen des Outlook Adressbuchs
Dim GC As Object, Child As Object, rootDomaine As Object, objConnection As Object
Dim objCommand As Object, objRecordSet
Set GC = GetObject("GC:")
For Each Child In GC
Set rootDomain = Child
Next
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "<GC://" & rootDomain.Name & ">;(samAccountName=" & Name & ");displayName,telephoneNumber,mobile,mail,sn,givenName,department,physicalDeliveryOfficeName,co;subTree"
Set objRecordSet = objCommand.Execute
If Not objRecordSet.EOF Then
Cells(iRow, iCol).Value = objRecordSet.Fields("sn").Value
Cells(iRow + 1, iCol).Value = objRecordSet.Fields("givenName").Value
Cells(iRow + 4, iCol) = objRecordSet.Fields("mail").Value
Cells(iRow + 5, iCol).Value = objRecordSet.Fields("department").Value
Cells(iRow + 2, iCol).Value = objRecordSet.Fields("telephoneNumber").Value
Cells(iRow + 3, iCol).Value = objRecordSet.Fields("mobile").Value
'Cells(iRow, iCol).Value = objRecordSet.Fields("displayName").Value
'Cells(iRow + 7, iCol).Value = objRecordSet.Fields("physicalDeliveryOfficeName").Value
'Cells(iRow + 6, iCol).Value = objRecordSet.Fields("co").Value
Cells(iRow - 1, iCol).Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Nam As String, iRow As Long, iCol As Long, Memo As String
If Target.Cells.Count > 1 Then GoTo ende
iRow = Target.Row
iCol = Target.Column
If Cells(1, iCol).Value = "x" And Cells(iRow, 1).Value = "y" Then
Target.Value = "Bitte warten!"
Call Wer(Memo, Nam)
Target.Value = ""
End If
If Memo <> "" Then Call ADNameQuery(Memo, iRow, iCol)
ende:
End Sub
Sub Wer(ByRef Memo As String, ByRef Nam As String)
Dim objRecipColl As Object, OutLookObject As Object, NmSpace As Object
Set OutLookObject = CreateObject("Outlook.Application")
Set NmSpace = CreateObject("MAPI.Session")
If Not NmSpace Is Nothing Then
NmSpace.logon "", "", False, False
End If
Memo = ""
Nam = ""
On Error GoTo ende
Set objRecipColl = NmSpace.AddressBook()
Memo = Memoid(objRecipColl.Item(1).Address)
Nam = objRecipColl.Item(1).Name
ende:
Err.Clear
On Error GoTo 0
End Sub
Function Memoid(Nam As String) As String
Dim i As Long, j As Long
j = 1
nochmal:
i = InStr(j, Nam, "cn=", vbTextCompare)
If i > 0 Then
j = i + 1
GoTo nochmal
End If
If j > 1 Then
Memoid = Right(Nam, Len(Nam) - j - 1)
Else
Memoid = ""
End If
End Function
Please also mark the comments that contributed to the solution of the article
Content-Key: 169729
Url: https://administrator.de/contentid/169729
Printed on: April 18, 2024 at 11:04 o'clock
2 Comments
Latest comment
Ohne den Code betrachtet zu haben - den VBA-Editor öffnest Du bei E2003 über ->Extras->Makros->Visual Basic Editor. Da gehört der Code rein. Deine Arbeitsmappe sollte ein Tabellenblatt namens WorkingGroupList beinhalten....