_Excel_RangeWrite : empty excel file

[lowrider2025]

Hi,

I'm trying to compare 2 excel files. One has pc's with identification numbers being replaced by new pc's the other file contains user names that use the old pc's being replaced. Trying to compare the files and fill in the users that will get the new pc in first file by inserting the users in an empty column (D). After I run the script the file(delivery.xls) is empty...

Long time user of autoit. First time with excel udf.

Autoit version: 3.3.12.0

Excel 2013

Windows 7

#include <Excel.au3>
; Create application object
Local $oExcel = _Excel_Open(False,False)
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 = @ScriptDir & "\delivery.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook,False,False)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:C"),1)

Local $sWorkbookUsers = @ScriptDir & "\users.xls"
Local $oWorkbookUsers = _Excel_BookOpen($oExcel, $sWorkbookUsers,True,False)
Local $aUsers = _Excel_RangeRead($oWorkbookUsers, Default, $oWorkbookUsers.ActiveSheet.Usedrange.Columns("A:E"),1)
_Excel_BookClose($oWorkbookUsers,False)

If $aResult<>Null Then
    For $i=0 to UBound($aResult)-1
        For $j=0 to UBound($aUsers)-1
            If $aResult[$i][2]==$aUsers[$j][0] Then
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$aUsers[$j][2],"D"&$i+1)
            Else
            EndIf
        Next
    Next
Else
    MsgBox(0,"test","no array",3)
EndIf
_Excel_BookSave($oWorkbook)
_Excel_Close($oExcel)

 

Edited October 21, 2016 by lowrider2025

[kcvinu]

Use Zero instead of Null.  And in AutoIt, it uses single "=" for a comparison. 

[lowrider2025]

Thx kcvinu for replying. Did the changes you suggested but file is still empty...

[water]

Will have a look after  my return from vacation on Thursday. 

[kcvinu]

ConsoleWrite(Ubound($aResult) & @CRLF)

Add this under the line right after filling $aResult with excel range read. Only to make sure you have something in this array. Do same thing with $aUsers. 

And

If $aResult[$i][2] = $aUsers[$j][0] Then

After this line, print the matching item to console. Just for make sure that you have something to write into excel file. And think about the columns in delivery.xls.  You are using column number 2 in that if statement. That means, C column in excel. Make sure that there is no error. Do same thing about user.xls. 

Edit : A second thought. Please upload your excel files. 

Edited October 25, 2016 by kcvinu

[l3ill]

Hi,

I just got done with something very similar and mine would not work with out all of these object closes and opens.

Before I even got down to playing with the nested For Next Loop which was a challenge all  by itself

#include <Excel.au3>
; Create application object
Local $oExcel = _Excel_Open(False,False)
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sWorkbook = @ScriptDir & "\delivery.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook,False,False)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:C"),1)
_Excel_BookClose($oWorkbook,False)
_ArrayDisplay($aResult)
Local $oExcel = _Excel_Open(False,False)
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sWorkbookUsers = @ScriptDir & "\users.xls"
Local $oWorkbookUsers = _Excel_BookOpen($oExcel, $sWorkbookUsers,True,False)
Local $aUsers = _Excel_RangeRead($oWorkbookUsers, Default, $oWorkbookUsers.ActiveSheet.Usedrange.Columns("A:E"),1)
_Excel_BookClose($oWorkbookUsers,False)
_ArrayDisplay($aUsers)

 

[lowrider2025]

Wow, all those replies :-) . For the time being I created a new csv file and then filewriteline added the values separated by commas and then converted it back to a 'real' excel file. So the array does contain elements and I am able to fill them in a csv file. But if I could get the _Excel_RangeWrite thing going, it would be neater.

Edited October 26, 2016 by lowrider2025

[l3ill]

While testing your code I also came up with an empty delivery.xls

If you replace everything between:

; *****************************************************************************
; Open an existing workbook and return its object identifier.
; *****************************************************************************

and

If $aResult<>Null Then

with the code from post 6 I would be interested to see if this fixes your problem.

[water]

On 21.10.2016 at 11:20 PM, kcvinu said:

Use Zero instead of Null.  And in AutoIt, it uses single "=" for a comparison. 

To check for an array I suggest to use IsArray!

[kcvinu]

18 hours ago, water said:

To check for an array I suggest to use IsArray!

Oops. My mistake water. Thanks for correctiong me.  

[water]

[water]

This works fine for me with the following layout of the Excel files:
Delivery: Computername | ID of old computer | ID of new computer
Users: Username | ID of old computer
If your column numbers are different then please change the marked line

#include <Excel.au3>

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $iIDDelivery = 1, $iIDUsers = 1, $iNameUsers = 0 ; Array index for IDs starting with 0 <=== Change if needed

Local $sWorkbookDelivery = @ScriptDir & "\Delivery.xlsx"
Local $oWorkbookDelivery = _Excel_BookOpen($oExcel, $sWorkbookDelivery, False)
If @error Then Exit MsgBox(16, "Excel", "Error opening Delivery workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aDelivery = _Excel_RangeRead($oWorkbookDelivery, Default, $oWorkbookDelivery.ActiveSheet.Usedrange.Columns("A:C"), 1)
If @error Then Exit MsgBox(16, "Excel", "Error reading Delivery workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $sWorkbookUsers = @ScriptDir & "\Users.xlsx"
Local $oWorkbookUsers = _Excel_BookOpen($oExcel, $sWorkbookUsers, True)
If @error Then Exit MsgBox(16, "Excel", "Error opening Users workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aUsers = _Excel_RangeRead($oWorkbookUsers, Default, $oWorkbookUsers.ActiveSheet.Usedrange.Columns("A:E"), 1)
If @error Then Exit MsgBox(16, "Excel", "Error reading Users workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookClose($oWorkbookUsers, False)
If @error Then Exit MsgBox(16, "Excel", "Error closing Users workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

For $i = 0 To UBound($aDelivery) - 1
    For $j = 0 To UBound($aUsers) - 1
        If $aDelivery[$i][$iIDDelivery] == $aUsers[$j][$iIDUsers] Then
            _Excel_RangeWrite($oWorkbookDelivery, $oWorkbookDelivery.Activesheet, $aUsers[$j][$iNameUsers], "D" & $i + 1)
            If @error Then Exit MsgBox(16, "Excel", "Error writing to Delivery workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
    Next
Next
MsgBox(0, "", "...")
_Excel_BookSave($oWorkbookDelivery)
If @error Then Exit MsgBox(16, "Excel", "Error saving Delivery workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)

 

[wisem2540]

I had a similar issue out of Rangewrite.  How many items are you trying to write?  Seems like I remember reading that the limit was 5000?  I also chose to use _FilewritefromArray to a CSV.  

 

To clarify - my array had about 900 elements (rows) and if I tried to write more than 5 columns (4500 cells) it would come back blank, like yours

Edited October 31, 2016 by wisem2540

[water]

The cell limit depends on the version of Excel you use. Details can be found here: https://www.autoitscript.com/wiki/Excel_UDF#Transpose_limits

If you reach the limit set parameter $bForceFunc to true.

[wisem2540]

Water,

I see this behavior with Excel 2010.  Is this because the file is xls rather than the new format xlsx?

 

That seemed to work! 

Edited November 1, 2016 by wisem2540

[water]

Excel 2010 has a limit of 65536 rows you can transpose using the Excel method. But thers is still a limit of 255 charactes per cell.
If you exceed one of this limits you will get unexpected results.
Did you try to set parameter $bForceFunc to True to use the UDFs builtin transpose function?