Output Variant to Range

[delaneyjm]

Afternoon all,

Having a bit of an issue with the following VBA code.

Sub NetGroups()
    Dim ado As Object
    Dim servername As String
    Dim filter As String
    Dim ol As Object
    Dim RetVal As String
    Dim full As Variant
    Dim counter As Long
    Dim rng As Range

    Set ado = CreateObject("ADODB.Connection")
    ado.Provider = "ADSDSOObject"
    ado.Open "NameSearch"
    
    servername = "FS-V005/o=GS"
    filter = "(fullName=John Doe)"
    
    Set ol = ado.Execute("<ldap: "="" &="" servername="">;" & filter & ";groupMembership;SubTree")
    
    If ol.EOF Then
        MsgBox "No person found with that name"
    ElseIf ol.RecordCount > 1 Then
        MsgBox "Found more than 1 instance of this name.  Please specify additional criteria"
    Else
        full = ol.Fields("groupMembership").Value
        For counter = LBound(full) To UBound(full) Step 1
            full(counter) = Replace(Replace(Right(full(counter), Len(full(counter)) - 3), ",ou=", "."), ",o=", ".")
        Next counter
        
        Set rng = ActiveSheet.Range("A4:A" & UBound(full) + 4)
        rng.Value = full
    End If
End Sub

My LDAP query is working fine, the ol object has information, and the variant full has data.

My problem is when the macro reaches the line rng.Value = full, it does output information to the range but only the first item in the variant full to the specified range.

When I was troubleshooting the code myself, I went into the Locals windo and looked at the variant full. It appears to be of Variant/String type.

Does the variant full need to be a 2 dimensional "array" in order to output to a range?</ldap:>

 

[ZVI]

Try:
Set rng = ActiveSheet.Range("A4:A" & UBound(full) + 4 - 1 )

or
Set rng = ActiveSheet.Range("A4").Resize(UBound(full))

 

[delaneyjm]

Norie, ol has OLE objects in it and .CopyFromRecordset fails hence why I was trying to output to a variant and then a range. I did use your suggestion and loop through ol to get the necessary info though. Typically, it's only returning 10 ±5 records so while it may not be the fastest method, it does work.

ZVI, both of those generate type mismatch errors when I run them in the code.

 

[Norie]

I wasn't sure if CopyFromRecordset would work and I couldn't find any examples that used it.

I did see some examples looping though.

Didn't fully understand them though

They weren't as straightforward as I'm used to - probably because of the OLE objects you mention.