Jump to content
Sign in to follow this  
Jaboowaki

Combining Excel Sheets

Recommended Posts

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.


Share this post


Link to post
Share on other sites
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)


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

Share this post


Link to post
Share on other sites
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 by Jaboowaki

Share this post


Link to post
Share on other sites
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.

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

Thanks so much. This works "awesomely" ;)


Share this post


Link to post
Share on other sites
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 by cameronsdad

Share this post


Link to post
Share on other sites
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.

 

 

#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

Share this post


Link to post
Share on other sites
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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Tmoney

You're right - I misread the documentation.

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  

×