We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

VB Script: Pass Multiple Values, e.g. List to a Cell

JB4375
JB4375 asked
on
Medium Priority
309 Views
Last Modified: 2012-05-06
For Each Computer in an OU, I want to list the User profiles that are present. At this point all I'm catching is the last value in the list. I'd like to list all of them to a cell, and have them seperated by commas.


Dim counter
 
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Add
counter =1 'init to first cell
 
 
DoRecursive("OU=Test,OU=Unassigned,OU=Departments,DC=domain")
 
Function DoRecursive(strObjectDN)
  
Set oneLevelOU= GetObject("LDAP://" & strObjectDN)
Set objOU = GetObject("LDAP://" & strObjectDN)
objOU.Filter = Array("Computer")
	
For Each objComputer in objOU
	strFolder = "\\" & objComputer.CN & "\c$\documents and settings"
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objFolder = objFSO.GetFolder(strFolder)
		'For Each objChild In objFolder.SubFolders
			'WScript.Echo objChild.Name
    
	Excel.Cells(counter,1).Value = objComputer.CN
        Excel.Cells(counter,2).Value = "LDAP://" & strObjectDN
        	For Each objChild In objFolder.SubFolders
           		Excel.Cells(counter,3).Value = objChild.Name
    		      
    		Next
       counter =counter +1
Next
 
End Function

Open in new window

Comment
Watch Question

Commented:
This should do it.  It also incorporates the code from your other question.


Dim counter
 
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Add
counter =1 'init to first cell
 
 
DoRecursive("OU=Test,OU=Unassigned,OU=Departments,DC=domain")
 
Function DoRecursive(strObjectDN)
  
Set oneLevelOU= GetObject("LDAP://" & strObjectDN)
Set objOU = GetObject("LDAP://" & strObjectDN)
objOU.Filter = Array("Computer")
        
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
For Each objComputer in objOU
    If objFSO.FolderExists("\\" & objComputer.CN & "\c$\documents and settings") Then
        strFolder = "\\" & objComputer.CN & "\c$\documents and settings"
    Else
        strFolder = "\\" & objComputer.CN & "\c$\users"
    End If
    
    Set objFolder = objFSO.GetFolder(strFolder)
    strProfiles = ""
    
    For Each objChild In objFolder.SubFolders
        strProfiles = strProfiles & objChild.Name & ","
    Next
 
    Excel.Cells(counter,1).Value = objComputer.CN
    Excel.Cells(counter,2).Value = "LDAP://" & strObjectDN
    Excel.Cells(counter,3).Value = strProfiles
 
    counter =counter +1
Next
 
End Function

Open in new window

Author

Commented:
Shift-3,
I'm getting the following error Microsoft VBScript runtime error: Object required: 'objFSO' from the line of code indicated by ***. I realize it actually points to my other question but since you're responding to both I hoped you wouldn't mind.
Function DoRecursive(strObjectDN)
 
Set oneLevelOU= GetObject("LDAP://" & strObjectDN)
Set objOU = GetObject("LDAP://" & strObjectDN)
objOU.Filter = Array("Computer")
 
For Each objComputer in objOU
 If objFSO.FolderExists("\\" & objComputer.CN & "\c$\documents and settings") Then  '***
  strFolder = "\\" & objComputer.CN & "\c$\documents and settings"
 Else
  strFolder = "\\" & objComputer.CN & "\c$\users"
    End If
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 

Commented:
Your script doesn't match what I posted.  The Set objFSO line has to go before any of the lines that make use of it.  You only need to create it once, so it makes the most sense to put it before the For Each objComputer in objOU loop.

Author

Commented:
Ok.... I'm lost. Below is the entire script. Could you elaborate? Sorry to be so clueless. JB

Dim counter
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Add
counter =1 'init to first cell
 
 
DoRecursive("OU=Test,OU=Unassigned,OU=Domain")
 
Function DoRecursive(strObjectDN)
 
Set oneLevelOU= GetObject("LDAP://" & strObjectDN)
Set objOU = GetObject("LDAP://" & strObjectDN)
objOU.Filter = Array("Computer")
 
For Each objComputer in objOU
 If objFSO.FolderExists("\\" & objComputer.CN & "\c$\documents and settings") Then
  strFolder = "\\" & objComputer.CN & "\c$\documents and settings"
 Else
  strFolder = "\\" & objComputer.CN & "\c$\users"
    End If
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 
 Set objFolder = objFSO.GetFolder(strFolder)
 strProfiles = ""
   
    For Each objChild In objFolder.SubFolders
        strProfiles = strProfiles & objChild.Name & ","
    Next
 
    Excel.Cells(counter,1).Value = objComputer.CN
    Excel.Cells(counter,2).Value = "LDAP://" & strObjectDN
    Excel.Cells(counter,3).Value = strProfiles
 
    counter =counter +1
Next

End Function
Commented:
Ok.  The line If objFSO.FolderExists("\\" & objComputer.CN & "\c$\documents and settings") Then makes use of the objFSO object.  However, this object has to be created, with the line Set objFSO = CreateObject("Scripting.FileSystemObject"),  before you can use it.  

If you look at my script, the object is created on Line 17, and then first gets used on Line 20.  In yours, the object gets first used on line 17 but isn't created until Line 22.  That's why you're getting the error Object required: 'objFSO'; the object doesn't exist yet when you're trying to use it.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks Shift-3.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.