Combining Excel Sheets

[Jaboowaki]

Hello, I am a new user of AutoIt. I am writing scripts that help create Excel spreadsheets. I am stumpped on how to combine Excel Sheets with AutoIt. I have 3 Excel Sheets that I have titled with a number that represents how many rows in the list. I need a combined list and the old list deleted.

[enaiman]

I don't think anybody here is a mind-reader and to try to figure out what are you saying is a waste of time.

Care to explain better what you want and some examples? ... that's only if you need help ..

Usually, posts in this sections are answered within minutes. If a post is not answered in a couple hours then:

- that post is about something above average coding (not your case though)

- that post is not clear enough (the OP is not explaining his problem very well)

[Jaboowaki]

I have never used a forum. Thanks for the info, I will update it more clearly.

I want to combine three spreadsheets to a master spreadsheet. I have three different people creating spreadsheets that need to be combined. The script I attached is the GUI that makes the spreadsheets. When the END PALLET button is pressed, I need it to complete the task by saving the spreadsheet to the master spreadsheet. The master is not empty when I go to save to it so I need the script to start on an empty row.

Edited September 16, 2010 by Jaboowaki

[Jaboowaki]

Accidentally made a new reply. Guess this proves I never use forums. Oh, btw, I attached a portion of the script. When it prompts for barcode, put some text in or it will look for another section I left out because it wasn't needed for this question. If the full script will help better I can attach it. The attachment I sent will function tho. I just started 2 months ago with scripting in general.

Edited September 16, 2010 by Jaboowaki

[enaiman]

First suggestion:

I see you are using "Excel.au3" - why don't you use other functions from that UDF? _ExcelReadCell, _ExcelWriteCell and others?

I see that you are using TABs to select sells and "Send" to write the info there - Using _ExcelWriteCell will get rid of these.

My approach to your problem would be:

- read every Excel sheet into an array (_ExcelReadSheetToArray)

- read the master Excel sheet into another array

- add the 3 arrays to the master one

- write the new master sheet from the big array (_ExcelWriteSheetFromArray)

Here is an example script - you will need to read about _ExcelReadSheetToArray to understand the code in full.

You can use my example to build your script to do the same thing.

expandcollapse popup

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

Global $Sheet1_ARRAY, $Sheet2_ARRAY, $Master_ARRAY, $FinalARRAY[1000][50]
;~~~~~~~~ Create example Files ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FileClose(FileOpen(@ScriptDir&"\sheet1.xls", 2))
FileClose(FileOpen(@ScriptDir&"\sheet2.xls", 2))
If Not FileExists(@ScriptDir&"\Master.xls") Then FileClose(FileOpen(@ScriptDir&"\Master.xls", 2))

$oExcel1 = _ExcelBookOpen(@ScriptDir&"\sheet1.xls", 0)
For $i = 1 to 10
    _ExcelWriteCell($oExcel1, "Test Value Column 1 Row "&$i&" Sheet1", $i, 1)
    _ExcelWriteCell($oExcel1, "Test Value Column 2 Row "&$i&" Sheet1", $i, 2)
Next
_ExcelBookSave($oExcel1)
_ExcelBookClose($oExcel1)

$oExcel2 = _ExcelBookOpen(@ScriptDir&"\sheet2.xls", 0)
For $i = 1 to 10
    _ExcelWriteCell($oExcel2, "Test Value Column 1 Row "&$i&" Sheet2", $i, 1)
    _ExcelWriteCell($oExcel2, "Test Value Column 2 Row "&$i&" Sheet2", $i, 2)
Next
_ExcelBookSave($oExcel2)
_ExcelBookClose($oExcel2)
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$oExcel1 = _ExcelBookOpen(@ScriptDir&"\sheet1.xls", 0)
$oExcel2 = _ExcelBookOpen(@ScriptDir&"\sheet2.xls", 0)
$oExcel0 = _ExcelBookOpen(@ScriptDir&"\Master.xls", 0)

$Sheet1_ARRAY = _ExcelReadSheetToArray($oExcel1)
$Sheet2_ARRAY = _ExcelReadSheetToArray($oExcel2)
$Master_ARRAY = _ExcelReadSheetToArray($oExcel0)

$counter = 1
$Test4Max = 0
;--------- $FinalARRAY is empty at this point --------
For $i = 1 To $Master_ARRAY[0][0]                       ;start putting the data from Master Sheet in the final array
    For $j = 1 To $Master_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Master_ARRAY[$i][$j]
    Next
    $counter +=1
Next
$Test4Max = $Master_ARRAY[0][1]

For $i = 1 To $Sheet1_ARRAY[0][0]                       ;add the data from Sheet1 at the end of $FinalARRAY
    For $j = 1 To $Sheet1_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Sheet1_ARRAY[$i][$j]
    Next
    $counter +=1
Next
If $Test4Max < $Sheet1_ARRAY[0][1] Then $Test4Max = $Sheet1_ARRAY[0][1]

For $i = 1 To $Sheet2_ARRAY[0][0]                       ;add the data from Sheet2 at the end of $FinalARRAY
    For $j = 1 To $Sheet2_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Sheet2_ARRAY[$i][$j]
    Next
    $counter +=1
Next
If $Test4Max < $Sheet2_ARRAY[0][1] Then $Test4Max = $Sheet2_ARRAY[0][1]

ReDim $FinalARRAY[$counter][$Test4Max +1]

_ExcelWriteSheetFromArray($oExcel0, $FinalARRAY)



_ExcelBookSave($oExcel0)
_ExcelBookSave($oExcel1)
_ExcelBookSave($oExcel2)
_ExcelBookClose($oExcel0)
_ExcelBookClose($oExcel1)
_ExcelBookClose($oExcel2)

MsgBox(0, "", "All Done")

[Jaboowaki]

Thanks so much. This works "awesomely"

[seandisanti]

another way would be to do like...

$oEX = ObjCreate("","excel.Application")
$master = $oEX.Workbooks.Open("c:\master.xls")
$another = $oEX.Workbooks.Open("c:\another.xls")
$another.Activesheet.UsedRegion.Copy
$next = StringSplit($master.ActiveSheet.UsedRange.Address,"$")
$master.Activesheet.Range("a" & Activesheet.usedrange.rows.count + 1).Select
$master.Activesheet.Paste

***edit***

corrected

Edited September 19, 2010 by cameronsdad

[Tmoney]

On 9/15/2010 at 9:33 PM, enaiman said:

First suggestion:

I see you are using "Excel.au3" - why don't you use other functions from that UDF? _ExcelReadCell, _ExcelWriteCell and others?

 

I see that you are using TABs to select sells and "Send" to write the info there - Using _ExcelWriteCell will get rid of these.

 

My approach to your problem would be:

- read every Excel sheet into an array (_ExcelReadSheetToArray)

- read the master Excel sheet into another array

- add the 3 arrays to the master one

- write the new master sheet from the big array (_ExcelWriteSheetFromArray)

 

Here is an example script - you will need to read about _ExcelReadSheetToArray to understand the code in full.

You can use my example to build your script to do the same thing.

 

 

expandcollapse popup

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

Global $Sheet1_ARRAY, $Sheet2_ARRAY, $Master_ARRAY, $FinalARRAY[1000][50]
;~~~~~~~~ Create example Files ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FileClose(FileOpen(@ScriptDir&"\sheet1.xls", 2))
FileClose(FileOpen(@ScriptDir&"\sheet2.xls", 2))
If Not FileExists(@ScriptDir&"\Master.xls") Then FileClose(FileOpen(@ScriptDir&"\Master.xls", 2))

$oExcel1 = _ExcelBookOpen(@ScriptDir&"\sheet1.xls", 0)
For $i = 1 to 10
    _ExcelWriteCell($oExcel1, "Test Value Column 1 Row "&$i&" Sheet1", $i, 1)
    _ExcelWriteCell($oExcel1, "Test Value Column 2 Row "&$i&" Sheet1", $i, 2)
Next
_ExcelBookSave($oExcel1)
_ExcelBookClose($oExcel1)

$oExcel2 = _ExcelBookOpen(@ScriptDir&"\sheet2.xls", 0)
For $i = 1 to 10
    _ExcelWriteCell($oExcel2, "Test Value Column 1 Row "&$i&" Sheet2", $i, 1)
    _ExcelWriteCell($oExcel2, "Test Value Column 2 Row "&$i&" Sheet2", $i, 2)
Next
_ExcelBookSave($oExcel2)
_ExcelBookClose($oExcel2)
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$oExcel1 = _ExcelBookOpen(@ScriptDir&"\sheet1.xls", 0)
$oExcel2 = _ExcelBookOpen(@ScriptDir&"\sheet2.xls", 0)
$oExcel0 = _ExcelBookOpen(@ScriptDir&"\Master.xls", 0)

$Sheet1_ARRAY = _ExcelReadSheetToArray($oExcel1)
$Sheet2_ARRAY = _ExcelReadSheetToArray($oExcel2)
$Master_ARRAY = _ExcelReadSheetToArray($oExcel0)

$counter = 1
$Test4Max = 0
;--------- $FinalARRAY is empty at this point --------
For $i = 1 To $Master_ARRAY[0][0]                       ;start putting the data from Master Sheet in the final array
    For $j = 1 To $Master_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Master_ARRAY[$i][$j]
    Next
    $counter +=1
Next
$Test4Max = $Master_ARRAY[0][1]

For $i = 1 To $Sheet1_ARRAY[0][0]                       ;add the data from Sheet1 at the end of $FinalARRAY
    For $j = 1 To $Sheet1_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Sheet1_ARRAY[$i][$j]
    Next
    $counter +=1
Next
If $Test4Max < $Sheet1_ARRAY[0][1] Then $Test4Max = $Sheet1_ARRAY[0][1]

For $i = 1 To $Sheet2_ARRAY[0][0]                       ;add the data from Sheet2 at the end of $FinalARRAY
    For $j = 1 To $Sheet2_ARRAY[0][1]
        $FinalARRAY[$counter][$j] = $Sheet2_ARRAY[$i][$j]
    Next
    $counter +=1
Next
If $Test4Max < $Sheet2_ARRAY[0][1] Then $Test4Max = $Sheet2_ARRAY[0][1]

ReDim $FinalARRAY[$counter][$Test4Max +1]

_ExcelWriteSheetFromArray($oExcel0, $FinalARRAY)



_ExcelBookSave($oExcel0)
_ExcelBookSave($oExcel1)
_ExcelBookSave($oExcel2)
_ExcelBookClose($oExcel0)
_ExcelBookClose($oExcel1)
_ExcelBookClose($oExcel2)

MsgBox(0, "", "All Done")

Reviving this 8 year old thread to say that excel.au3 has been updated and therefore the script provided in this example no longer works - as some functions have been combined and require new parameters to function properly.

Additionally since 2010, excel is mainly functioning on an XML based format (XSLX) which is not supported by this include (excel.au3). 
However, if you use the original release of excel.au3 include - this works like a charm.

- Move this to the: C:\Program Files (x86)\AutoIt3\Include
- Modify the script in the first line to: #include <Excel_3310.au3>

The script itself could be modified to include reading XSLX using; 

 

 

Excel_3310.au3

[BrewManNH]

7 minutes ago, Tmoney said:

Additionally since 2010, excel is mainly functioning on an XML based format (XSLX) which is not supported by this include (excel.au3). 

That's completely untrue. The newest version of Excel.au3 that comes with AutoIt works just fine with XLS and XLSX files.

Resurrecting an 8 year old thread to post incorrect information is why it's frowned upon to necropost threads like this, things have changed in the past 8 years and old threads like this are no longer valid.

[Tmoney]

You're right - I misread the documentation.