Excel : merging worksheets in the same spreadsheet

[Ghost_Line]

Hi guys !

I'm trying to merge a random number of worksheets stored in a spreadsheet in the first worksheet of this spreadsheet.

I was thinking of using some Excel to array, then to Excel trick (in order to eliminate all empty lines and to simply sort the whole thing), but there's no more traces of this kind of functions in the new Excel UDF.

So if there is someone kind enough to help me ...

Thanks !

[water]

To read the content of a Worksheet please have a look at function _Excel_RangeRead and the examples that can be found in the help file.

The script breaking changes section in AutoIt 3.3.12.0 shows exactly what has changed from the old to the new Excel UDF.

[Ghost_Line]

I feel so dumb ... I wasn't aware of the existence of the script breaking changes.

Anyway, thanks ! Can I leave this topic open, in order to give the solution when I'll find it ?

Edited July 21, 2014 by GhostLine

[water]

Sure.

Post any questions you have or a loud HURRA when you finished your project

[Ghost_Line]

I've done it !

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Local $chem_imp = "C:\Users\ghost.line\Documents\AutoIt\TecReg\Import\"
Local $oAppl = _Excel_Open(False)
Dim $sResult[1][106]

$liste_fichiers = _FileListToArray($chem_imp, Default, Default, True)

For $i = 1 To $liste_fichiers[0]
    $test = $liste_fichiers[$i]
    Local $oWorkbook = _Excel_BookOpen($oAppl, $test, False, False)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $test & "." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oAppl)
        Exit
    EndIf
    Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    If $i <> 1 Then ReDim $sResult[UBound($sResult) + UBound($sArray)][104]
    _ArrayAdd($sResult, $sArray)
Next
_ArrayDisplay($sResult)

 

I've a question, in bonus : when I put 

Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65")

, I collect lot of blank lines. Is there a simple way to know how much lines are not empty ?

[water]

You just want to read all rows that have been used? Means: Do not read "empty" rows: at the end?

[Ghost_Line]

In fact, I've empty lines in all the xls files I'm using. So, after "data compilation", my array looks like that :

AAAA|YES|NO|YES         <--- begining of the first xls file

BBBB|YES|YES|YES

 

 

                                        <--- end of the first xls file

ZZZZ|YES|YES|YES        <--- begining of the second xls file

[...]

[water]

To only read the used range try this:

Replace

Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65")

with

Local $sArray = _Excel_RangeRead($oWorkbook, 2, $oWorkbook.Sheets(2).UsedRange)

This returns all cells that ever have been touched. So if you get an empty cell then it is empty or had a content which was removed.

If the first cell thas has ever been touched is e.g. B5 then the returned array starts with this cell.