Update the Excel UDF that comes with AutoIt

[water]

I stripped down your code to just the Excel part and it works just fine:

#include <Excel.au3>
#include <date.au3>
Global $aUsersE[5][3] = [["sAMAccountName", "Description", "Last Logon"], [10, 11, 12], [20, 21, 22], [30, 31, 32], [40, 41, 42]]
$Month = _DateToMonth(@MON, 1)
$sWorkbook = @ScriptDir & "\Accounts Disable " & $Month & " " & @YEAR & ".xls"
$oExcel = _Excel_Open(False, Default, Default, Default, True)
MsgBox(0,'open',@error & @CRLF & @extended)
$oWorkbook = _Excel_BookNew($oExcel, 1)
MsgBox(0,'booknew',@error & @CRLF & @extended)
$oExcel.ActiveSheet.Range("A:A").ColumnWidth = 21
$oExcel.ActiveSheet.Range("B:B").ColumnWidth = 125
$oExcel.ActiveSheet.Range("C:C").ColumnWidth = 15.14
_Excel_RangeWrite($oWorkbook, Default, $aUsersE, "A1")
MsgBox(0,'rw1',@error & @CRLF & @extended)
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlExcel8, True)
MsgBox(0,'save',@error & @CRLF & @extended)
_Excel_BookClose($oWorkbook)
MsgBox(0,'bookclose',@error & @CRLF & @extended)
_Excel_Close($oExcel)
MsgBox(0,'close',@error & @CRLF & @extended)

Does it work for you?

[Baraoic]

Yes that works.

Are you able to test using an array from _AD_GetObjectsInOU?

Only when I take the array from that and try writing it to excel does it not work. I can try doing other things to the array first before writing it to excel to see if that helps if you tell me what to try.

[water]

I will check as soon as I return to my office.

[water]

The problem is caused by the fact that a LDAP query returns NULL for unset properties.

I modified the example script to replace NULL by "*" and it works:

#include <Constants.au3>
#include <Excel.au3>
#include <date.au3>
#include <AD.au3>
_AD_Open()
$aUsers = _AD_GetObjectsInOU("", "(name=Ru*)", 2, "sAMAccountName,description")
; Replace NULL values with "*"
For $i = 1 To $aUsers[0][0]
    For $j = 0 To $aUsers[0][1] - 1
        If IsKeyword($aUsers[$i][$j]) = $KEYWORD_NULL Then $aUsers[$i][$j] = "*"
    Next
Next
$Month = _DateToMonth(@MON, 1)
$sWorkbook = @ScriptDir & "\Accounts Disable " & $Month & " " & @YEAR & ".xls"
$oExcel = _Excel_Open(False, Default, Default, Default, True)
$oWorkbook = _Excel_BookNew($oExcel, 1)
_Excel_RangeWrite($oWorkbook, Default, $aUsers, "A1")
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlExcel8, True)
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
_AD_Close()
Exit

[Baraoic]

Thank you, I appreciate you looking into it for me.

[water]

Maybe I should add a new parameter to function _AD_GetObectsInOU to translate NULL values to spaces?

[Baraoic]

Well now that I know what's wrong it's easy to fix, but I think that's not a bad idea.

[water]

I have posted the modified _AD_GetObjectsInOU function in the >AD thread.

Any comments?

[Baraoic]

I haven't tested it yet, but looks perfect to me, thank you.

[nitekram]

@Water

Working on another issue - trying to get excel to open the file for searching, but seems to be having issues...

I thought maybe it was because the file was located on a sharepoint site, but since then, I have added to the location of my script that I am running.

 

I am getting the following error message: error 3, extended -2147352567

Func GetPFRT_Info($Search)
    MsgBox('', '', 'Get all info for a site - and clean it up so that it comes up nice')

    ; Create application object
    Local $oAppl = _Excel_Open()
    If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    ; *****************************************************************************
    ; Open an existing workbook and return its object identifier.
    ; *****************************************************************************
    Local $sWorkbook = $gsLayerX_PFRT_Site ;$gsLayerX_PFRT_Techs
    Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ; , True, False
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Not able to open file", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)


Local $aResult = _Excel_RangeFind($oWorkbook, $Search)
; need to search through "Lan IP Address" and get that row info (pull the Primary and Secondary techs name - need to get those email addresses
; from that line, take the Site Name and search the other locations for that site and gather the techs name and address of the site
;
_ArrayDisplay($aResult)

EndFunc   ;==>GetPFRT_Info

EDIT, also with this code, I am getting a blank excel file open on my desktop, but not the file that I want opened

Edited April 29, 2015 by nitekram
updated with new info

[water]

@error = 3 stands for: Unable to open $sFilePath
@extendes is the code for a General Error

Can you please post the value of $gsLayerX_PFRT_Site?

[nitekram]

The value is just the name of the file located in the directory, as I thought it was having issues with the sharepoint site, I downloaded and saved a copy locally to see what was going on. I am able to open that same file at the same location with the Run command.

 

Global $gsLayerX_PFRT_Site = 'PFRT_SITE.xlsx'

[water]

You needd to specify the full path:
Global $gsLayerX_PFRT_Site = @ScriptDir & '\PFRT_SITE.xlsx'

[nitekram]

Interesting...I put a open dialog box, and the full path came up, and I was able to open it? But the default location, in the same directory as the script, will not open?

[water]

Correct. Maybe

Global $gsLayerX_PFRT_Site = '.\PFRT_SITE.xlsx'

works?

[nitekram]

I find this issue is strange. I have a mapped drive to this folder (subst S: C:\vm_shared via a batch file - so it can be shared with a virtual machine), and if I go to the mapped drive via file ope dialog, I have no issues getting to the file, but if I go directly to the folder - I get the same type of errors stating that the path is not correct or the file does not exist?

If I delete the substitute a folder drive, I am able to get to the file.

This is an issue with my script, so I thank you for your help. You might want to keep this in the back of your mind, or update the help file with this info, that when using substitute drives, the path can only be found via the subst drive.

 

EDIT

I am still trying to test this - be back soon

Edited April 29, 2015 by nitekram
still testing

[nitekram]

I am having some issues with testing this...it might be issues with Windows 7 and how it handles the substitute drives. So with that in mind, I would rather open this off the sharepoint site. I am going to try and work that angle, as this one, is difficult, as I have tried to open the file when the directory has been mapped, and the file will not open from the location, but only from the mapped drive.

[nitekram]

Does anyone have sharepoint that might be able to test opening an excel file from it with this UDF, as I am still getting an error that states file cannot be found

ERROR 2 Extended 0

 

Or maybe with this UDF you are unable to load the file?

 

I see I am not the only one with this type of issue

Edited April 29, 2015 by nitekram
added link

[water]

If FileExists can't find the file then function _ESxcel-BookOpen returns @error = 2.
So the question is: Does AutoIt support the way you try to access files?

[nitekram]

If FileExists can't find the file then function _ESxcel-BookOpen returns @error = 2.
So the question is: Does AutoIt support the way you try to access files?

​Is this a different function? _ESxcel-BookOpen

I would assume, that none of the functions that I have tried so far open the file from a sharepoint site  - except for Run("C:\Program Files\Microsoft Office\Office15\EXCEL.EXE /h " & '"' & $sURL & '"')

I have read other threads where this is an issue, so I have to find a work around - maybe open it with Run command and then save it locally to each computer, but that seems to be a nasty work around, as the file is updated about every 2 months.