Sign in to follow this  
Followers 0
GhostLine

Excel : merging worksheets in the same spreadsheet

8 posts in this topic

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 !

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

:sweating:

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

Share this post


Link to post
Share on other sites

Sure.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

[...]

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
Sign in to follow this  
Followers 0