Script autoit for excel

[Atticus]

Hello everybody,

Sorry for my bad engish but i'll try to understandable.

I begin with autoIt and i have seen the work of Locodarwin for the new function for excel.

I wanna create a script which copy data from an excel book A to an excel book B.

But i'd like that the data are copied in the first empty case a the sheet A from the excel book B.

Is it possible. Which function must I use.

thanks by advance.

Atticus

[UQOII]

excel

[Atticus]

Thanks but I have allready seen this topic but I don't know which of those functions I need to use.

I don't understand most of them.

If someone can tell me which function I need to use, It could be very nice.

[PsaltyDS]

Something like this post may help to illustrate it. You can open more than one instance of Excel at once, just save their handles to separate variables, like $oExcel_1 and $oExcel_2, and use the appropriate one for the read and write operations.

[Atticus]

Thanks a lot, but I've got one more problem

I've made this script

but when I run it, I've got this error:

What is the problem???!!!! I don't understand !!!!

Edited May 14, 2008 by Atticus

[PsaltyDS]

Thanks a lot, but I've got one more problem

I've made this script

but when I run it, I've got this error:

What is the problem???!!!! I don't understand !!!!

What version of Excel are you working with? Does Excel launch and open the spreadsheet on your desktop as this error occurs?

[Atticus]

I work on Excel 2003. Excel launch open my excel book and after 2 or 3 second this message appears !!!!

[PsaltyDS]

I work on Excel 2003. Excel launch open my excel book and after 2 or 3 second this message appears !!!!

I don't see why it would do that. If you open it manually, are there any pop ups about possibly unsafe macros, or anything like that?

[Atticus]

When I open it manually there is no errors. It's only when I execute the script. I think it is a problem ( it's just a thought) with the include files excelCOM_UDF, maybe an error in the script .....

[Atticus]

Any idea?

[cramaboule]

I had this priblem already:

the path should not be in relative like: "\myfle.xls"

but

"C:\myfile.xls"

[PsaltyDS]

I had this priblem already:

the path should not be in relative like: "\myfle.xls"

but

"C:\myfile.xls"

Maybe so, but the OP's script has the file fully pathed out in $sFilePath already. That's not the current issue.

@Atticus: What version of AutoIt are you running this under? And what version of the ExcelCOM_UDF.au3 file? Is there anything unusual about the sheets in that workbook?

[PsaltyDS]

I just noticed the single quote in the file name. I don't know for sure it's the problem, but try this:

#include <ExcelCOM_UDF.au3>

; Open new book, make it visible
Global $sFilePath = "J:\Documents\PROJECT\TABLEAUX EXCEL D'ACTIVITES COMMERCIALES\ACTIVITE COMMERCIALE-2008.xls"
$sFilePath = FileGetShortName($sFilePath)
Global $oExcel = _ExcelBookOpen($sFilePath)

This translates the file name to 8.3 format before trying to open it.

[Eigensheep]

Hi, I'm working with excel 2007 but I have had some experience with '03. If this doesn't answer your question then I'm sorry.

The first thing to say is that I don't get the error. How new is your ExcelCOM_UDF au3 file? Try getting the newest version here and retrying.

If it's still not working then this code should do the same thing as what your one is supposed to (written without using locodarwin's file). Note that I'm assuming the cells in your file are filled top down, every cell having a value:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = True
$sFilePath = "J:\Documents\PROJET\TABLEAUX EXCEL D'ACTIVITES COMMERCIALES\ACTIVITE COMMERCIALE-2008.xls"
$oExcel.Workbooks.Open($sFilePath)
$lastRow = $oExcel.Range("A65536").End(-4162).Row
If $oExcel.Range("A" & $lastRow).Value = "" Then $lastRow = 0
If $lastRow Then
    MsgBox(64,"Results","Last cell used in column A was: " & $lastRow & @CRLF & "Next row will be: " & $lastRow + 1)
Else
    MsgBox(64,"Results","There were no used cells in column A" & @CRLF & "Next row will be: 1")
EndIf

Finally, if you are intent on using the file, try to get the attention of locodarwin.

Also, if you use excel a lot (and I hope this won't offend locodarwin) I'm still of the opinion that learning the VBA language behind it is the best method (and it's the same as the COM language for excel).

Hope it helps.

[Atticus]

Thank you very much for all your answers.

i'm gonna try everything tomorow. Hope it will works.

Thanks a lot again.

[Atticus]

I have solved my problem. It was because they were no sheets 1 in my book.

I've got one more question:

I wanna select in my book all the case which are used , excepted the case from line 1.

How can I do that???!!!!

[PsaltyDS]

I wanna select in my book all the case which are used , excepted the case from line 1.

How can I do that???!!!!

I don' t know what that means in English, but perhaps you mean something like _ExcelReadSheetToArray(), or _ExcelSheetUsedRangeGet()?

[Atticus]

Thanks a lot.

One more question: It's the last.

what is the function to select a case???

Thanks by advance

[cramaboule]

Well,...

You cannot "select" a case, but:

you can Read one or more cells

_ExcelReadCell()

_ExcelReadArray()

or you can "copy" (put to the clipboard.) one or more cells :

_ExcelCopy()

Depend what you want to do !

C.

[PsaltyDS]

Well,...

You cannot "select" a case, but:

you can Read one or more cells

_ExcelReadCell()

_ExcelReadArray()

or you can "copy" (put to the clipboard.) one or more cells :

_ExcelCopy()

Depend what you want to do !

C.

Actually, you can Range.Select a range, and you can make a cell active by the .Activate method. But that means getting into the object model, as I don't think the ExcelCOM_UDF contains functions for this.