Script help

[Gohaan]

Can anyone take a look at this script and tell me wtf am I doing wrong. I am trying to open an existing Excel document and over write all the data with the data that I am pulling from active directory.

What happens is it opens the one that I want it to over write but then proceeds to create a new excel document and fill it in with the information I want. I have left out my OU and DC in the post so dont tell me I am missing it lol.

Const ADS_SCOPE_SUBTREE = 2

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test\Test.xls")

objExcel.Visible = True
objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Display Name"
objExcel.Cells(1, 2).Value = "Last name"
objExcel.Cells(1, 3).Value = "First name"
objExcel.Cells(1, 4).Value = "Department"
objExcel.Cells(1, 5).Value = "Phone number"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT givenName, SN, department, telephoneNumber, displayName FROM " _
& "'LDAP://' WHERE " _
& "objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2

Do Until objRecordSet.EOF
objExcel.Cells(x, 1).Value = _
objRecordSet.Fields("displayName").Value
objExcel.Cells(x, 2).Value = _
objRecordSet.Fields("SN").Value
objExcel.Cells(x, 3).Value = _
objRecordSet.Fields("givenName").Value
objExcel.Cells(x, 4).Value = _
objRecordSet.Fields("department").Value
objExcel.Cells(x, 5).Value = _
objRecordSet.Fields("telephoneNumber").Value
x = x + 1
objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("C1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("D1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

 

[Nugotugo]

before i try and write a script for it... what script language and why exactly a xls instead of a basic txt file

 

[Nugotugo]

Having a hard time testing it since im connected to my works domain with a vpn so i can't truly test it till monday... but if you want to try heres the vbs code:

Const ADS_SCOPE_SUBTREE = 2

strFile = "c:\Test\Test.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strFile

objExcel.Cells(1, 1).Value = "Display Name"
objExcel.Cells(1, 2).Value = "Last name"
objExcel.Cells(1, 3).Value = "First name"
objExcel.Cells(1, 4).Value = "Department"
objExcel.Cells(1, 5).Value = "Phone number"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = "SELECT givenName, SN, department, telephoneNumber FROM 'LDAP://dc=SERVER,dc=DOMAIN,dc=com' WHERE objectCategory='user'"  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2

Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("telephoneNumber").Value
    x = x + 1
    objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("C1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("D1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")


' Save, close, and exit.
objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

I know the xls based part is working correctly. i tested that in a seperate vbs then combined it with the domain based part. I will test the script next week some time to see if it populates the cells in the xls file.

 

[Gohaan]

Needed to set the worksheet and workbook or it automattically assumed I wanted a new file

I figured it out as follows.


Const ADS_SCOPE_SUBTREE = 2

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

objWorksheet.Cells(1, 1).Value = "Display Name"
objWorksheet.Cells(1, 2).Value = "Last name"
objWorksheet.Cells(1, 3).Value = "First name"
objWorksheet.Cells(1, 4).Value = "Department"
objWorksheet.Cells(1, 5).Value = "Phone number"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT givenName, SN, department, telephoneNumber, displayName FROM " _
& "' WHERE " _
& "objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2

Do Until objRecordSet.EOF
objWorksheet.Cells(x, 1).Value = _
objRecordSet.Fields("displayName").Value
objWorksheet.Cells(x, 2).Value = _
objRecordSet.Fields("SN").Value
objWorksheet.Cells(x, 3).Value = _
objRecordSet.Fields("givenName").Value
objWorksheet.Cells(x, 4).Value = _
objRecordSet.Fields("department").Value
objWorksheet.Cells(x, 5).Value = _
objRecordSet.Fields("telephoneNumber").Value
x = x + 1
objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("C1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("D1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("E1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objWorksheet.Range("A1").SpecialCells(11)
Set objRange2 = objWorksheet.Range("C1")
Set objRange3 = objWorksheet.Range("A1")
Wscript.Echo "Data Polling Complete"
objWorkbook.SaveAs("C:\Test\Test.xls")
objExcel.Quit

 

[Nugotugo]

I would be surprised if that script worked without you having to tell it to save the excel file with the data and c:\test\test.xls was empty. your creating 3 instances of excel related objects and you only need 1.

 

[Gohaan]

From my understanding the first line below opens the application excel. The second line Sets the specific work book file located C:\test\test.xls. The third line sets the worksheet on the opened work book. It works however it could be more effort than whats needed.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)