Sign in to follow this  
Followers 0
Atticus

Script autoit for excel

21 posts in this topic

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

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

I've made this script

Posted Image

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

Posted Image

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

Edited by Atticus

Share this post


Link to post
Share on other sites

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?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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 .....

Share this post


Link to post
Share on other sites

Any idea?

Share this post


Link to post
Share on other sites

I had this priblem already:

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

but

"C:\myfile.xls"

Share this post


Link to post
Share on other sites

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?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thank you very much for all your answers.

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

Thanks a lot again.

Share this post


Link to post
Share on other sites

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???!!!!

Share this post


Link to post
Share on other sites

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()?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Thanks a lot.

One more question: It's the last.

what is the function to select a case???

Thanks by advance

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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