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.
This topic is 16 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
matrix101
Posts: 7
Last visit: Mon Jun 21, 2010 12:01 pm

Adding columns to a .xls file

Post 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!!!
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Adding columns to a .xls file

Post 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.
This topic is 16 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked