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:>

 

[VishMishra]

Is it your loop running only once no matter how many records are returned??

 

[delaneyjm]

No, the loop is running correctly. For example, if Ubound(full) is 17, the loop will run 17 times against the contents of full

 

[VishMishra]

Hi,
this is because the variant is storing multidimensional array. for that you need to specify one by one all the values by passing the index

 

[delaneyjm]

There isn't an index in the variant full.

I understand where you are coming from with this.

However, when I look at full in the Locals window and expand it, it looks like this:

full
->full(0)
->full(1)
->full(2)

If it had an index, it would look like this

full
->full(1)
->full(1,1)
->full(1,2)
->full(1,3)

 

[VishMishra]

Ok it means it is a single dimensional array.

At the last line of your code you have written:

rng.Value = full

Here full will by default return the first value= full(0) of array full.

if you want to display all the values in same cell, you need to use for loop

let me know, if it is fine?

 

[Norie]

Couldn't you just put the data straight into the cells instead of populating an array and then putting that on the worksheet?

 

[delaneyjm]

Norie, thanks for the suggestion.

I tried the following code instead:

Sub NetGroups()
    Dim ado As Object
    Dim servername As String
    Dim filter As String
    Dim ol As Object
    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 &<ldap: "="" &="" servername=""><ldap: "="" &="" servername=""> ";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        
        Set rng = ActiveSheet.Range("A4:A" & ol.Fields("groupMembership").ActualSize + 4)
        rng.Value = ol.Fields("groupMembership").Value
    End If
End Sub

However, it is the same result.</ldap:></ldap:>

 

[Norie]

That's isn't quite what I meant.

Can't you loop through the results and put them in cells one by one instead of all in one go?

 

[Norie]

Is ol a Recordset?

If it is you can loop through it and transfer the data to the worksheet, or you can use CopyFromRecordset.