Adding columns to a .xls file

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
Posts: 7
Joined: Thu Jun 07, 2007 4:29 am

Adding columns to a .xls file

Post by matrix101 » Thu Jun 21, 2007 9:11 am

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

User avatar
Posts: 13718
Joined: Tue May 22, 2007 9:57 am

Adding columns to a .xls file

Post by jvierra » Fri Jun 22, 2007 9:12 am

Either create the file using the Excel COM interfaces or open it using the Excel object and do a "SaveAs" to convert the file.