• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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

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

0
JB4375
Asked:
JB4375
  • 3
  • 3
1 Solution
 
Shift-3Commented:
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

0
 
JB4375Author 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")
 
0
 
Shift-3Commented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JB4375Author 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
0
 
Shift-3Commented:
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.
0
 
JB4375Author Commented:
Thanks Shift-3.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now