Jump to content

Excel : merging worksheets in the same spreadsheet


Recommended Posts

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 !

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

: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
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 ?
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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

[...]

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...