Sign in to follow this  
Followers 0
Jaboowaki

Combining Excel Sheets

7 posts in this topic

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.


Share this post


Link to post
Share on other sites



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)


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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.

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

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

Thanks so much. This works "awesomely" ;)


Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

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