lowrider2025

_Excel_RangeWrite : empty excel file

16 posts in this topic

#1 ·  Posted (edited)

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 by lowrider2025

Share this post


Link to post
Share on other sites



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


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by kcvinu

My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

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 :P

#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)

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by lowrider2025

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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!

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

18 hours ago, water said:

To check for an array I suggest to use IsArray!

Oops. My mistake water. Thanks for correctiong me. :) 


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

#11 ·  Posted

;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted

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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by wisem2540

Share this post


Link to post
Share on other sites

#14 ·  Posted

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by wisem2540

Share this post


Link to post
Share on other sites

#16 ·  Posted

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-07-20 - Version 1.2.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now