Page 1 of 1

Adding columns to a .xls file

Posted: Thu Jun 21, 2007 9:11 am
by matrix101
Hello, I have a vbscript that takes disabled users in AD and places them into a .csv file in columns. We need the file to an excel file. I changed the .csv to a .xls and it creates the excel file but its not in columns.

***this is what I currently have***

' List All the Disabled User Accounts in Active Directory
Const ADS_UF_ACCOUNTDISABLE = 2constForWriting = 2Set objFSO = CreateObject("Scripting.FileSystemObject")set oShell= Wscript.CreateObject("WScript.Shell")Script = Wscript.ScriptFullName ScriptPath = objFSO.GetFolder(Script & "..").Pathqut = Chr(34)Set ReportFile = objFSO.CreateTextFile( ScriptPath & "Corp-DisableAccountReport.xls")
Set objRootDSE = GetObject("LDAP://RootDSE")strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objConnection = CreateObject("ADODB.Connection")objConnection.Open "Provider=ADsDSOObject;"Set objCommand = CreateObject("ADODB.Command")objCommand.ActiveConnection = objConnectionobjCommand.CommandText = _ "<GC://" & strDNSDomain & ">;(objectCategory=User)" & _ ";userAccountControl,displayName,sAMAccountName,modifyTimeStamp,distinguishedName;subtree" Set objRecordSet = objCommand.Execute intCounter = 0Do Until objRecordset.EOF intUAC=objRecordset.Fields("userAccountControl") If intUAC AND ADS_UF_ACCOUNTDISABLE Then ReportFile.WriteLine objRecordset.Fields("displayName") & "," & objRecordset.Fields("sAMAccountName") & "," & objRecordset.Fields("modifyTimeStamp") intCounter = intCounter + 1 End If objRecordset.MoveNextLoop ReportFile.WriteLine VbCrLf & "A total of " & intCounter & " accounts are disabled." objConnection.Close

Any Ideas??? Thanks!!!

Adding columns to a .xls file

Posted: Fri Jun 22, 2007 9:12 am
by jvierra
Either create the file using the Excel COM interfaces or open it using the Excel object and do a "SaveAs" to convert the file.