Sign in to follow this  
Followers 0
cosmicdancer

Error with first script - very basic!

19 posts in this topic

I've just written my first script to open Excel! First I tried

Run ("EXCEL.exe")

which didn't work so the I tried the full path

Run ("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe"

which did work.

Then I thought I would be clever and open a file at a given location. Using the help file I tried the syntax

Run ("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe" [, "R:\ICT\Projects Raised\IT project raised spreadsheet.xls"])

This doesn't work. I've tried all sorts of variations on a theme as far as the syntax is concerned but no success.

I'm sure this is really simple - can anyone give me a clue what I'm doing wrong?

I would also like to open the file at a given sheet - is that possible?

Share this post


Link to post
Share on other sites



Sorry, folks - I should have said I'm using version 3. I think I may have posted this in the wrong forum. Please forgive a stupid newbie!

Share this post


Link to post
Share on other sites

Run ('"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe" "R:\ICT\Projects Raised\IT project raised spreadsheet.xls"')

The hard brackets are just there to let you know it is optional. Also you pretty much want to copy what would work from the Windows Run box or a command prompt for it to work in Run. Also notice the double and single quotes, there is probably different ways to get the same result but that is what I do.


"Human kind cannot gain anything without first giving something in return, to obtain; something of equal value must be lost."The Help File is truly your friend.

Share this post


Link to post
Share on other sites

Run ('"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe" "R:\ICT\Projects Raised\IT project raised spreadsheet.xls"')

The hard brackets are just there to let you know it is optional. Also you pretty much want to copy what would work from the Windows Run box or a command prompt for it to work in Run. Also notice the double and single quotes, there is probably different ways to get the same result but that is what I do.

Genius!!! I was just round to thinking that the brackets shouldn't be there. Thanks so much for that.

Do you know if it's possible to open the file at a given sheet? I know this can be done using VB (just Googled it!) - can AutoIt do this?

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Genius!!! I was just round to thinking that the brackets shouldn't be there. Thanks so much for that.

Do you know if it's possible to open the file at a given sheet? I know this can be done using VB (just Googled it!) - can AutoIt do this?

Try this:

;(sub "Sheet2" with the name of the sheet you want to open to)

$myExcel = ObjCreate("Excel.Application")
With $myExcel
        .Visible = 1
        .WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")
        .ActiveWorkbook.Sheets("Sheet2").Select()
EndWith
Edited by erik7426

Share this post


Link to post
Share on other sites

Try this:

;(sub "Sheet2" with the name of the sheet you want to open to)

$myExcel = ObjCreate("Excel.Application")
With $myExcel
        .Visible = 1
        .WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")
        .ActiveWorkbook.Sheets("Sheet2").Select()
EndWith
I'm getting this error:

Line 12 (File"I:\AutoIt3\Examples\script.au3"):

With $myExcel

With ^ERROR

Error: Only Object-type variables allowed in a "With" statement

Share this post


Link to post
Share on other sites

$oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = 1
    $oExcel.WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")
    $oExcel.ActiveWorkbook.Sheets("Sheet2").Select()

This should work then


Contact via MSN: [email=terarink_msn@hotmail.com]terarink_msn@hotmail.com[/email], yahoo: terarink_yah

Share this post


Link to post
Share on other sites

$oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = 1
    $oExcel.WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")
    $oExcel.ActiveWorkbook.Sheets("Sheet2").Select()

This should work then

Thanks for your help but now I'm getting this error:

Line 12 (File"I:\AutoIt3\Examples\script.au3"):

$oExcel.Visible = 1

$oExcel^ERROR

Error: Variable must be of type "Object".

Share this post


Link to post
Share on other sites

Thanks for your help but now I'm getting this error:

Line 12 (File"I:\AutoIt3\Examples\script.au3"):

$oExcel.Visible = 1

$oExcel^ERROR

Error: Variable must be of type "Object".

Anyone have any ideas - I really would love to get this to work!

Share this post


Link to post
Share on other sites

Anyone have any ideas - I really would love to get this to work!

I know how to open an Excel spreadsheet at a named sheet using VBA - is it possible to call this function from AutoIt - that would solve the problem? I've tried to Google for this but have found nothing.

Share this post


Link to post
Share on other sites

Set the COM error handler. Look in the helpfile for an example of one. Your object just isn't getting created. :P

Just been reading the help files, Brett - I can't see why the Object isn't getting created? The example there uses Excel and gives:

$oExcel = ObjCreate("Excel.Application") ; Create an Excel Object

$oExcel.Visible = 1 ; Let Excel show itself

$oExcel.WorkBooks.Add ; Add a new workbook

$oExcel.ActiveWorkBook.ActiveSheet.Cells(1,1).Value="test" ; Fill a cell

sleep(4000) ;See the results for 4 seconds

$oExcel.ActiveWorkBook.Saved = 1 ; Simulate a save of the Workbook

$oExcel.Quit ; Quit Excel

which looks very similar to me to:

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")

$oExcel.ActiveWorkbook.Sheets("Sheet2").Select()

I tried to read the error handling section but that was way over my head - this is my first script, after all! I'm going to need some gentle hand holding - sorry!

Share this post


Link to post
Share on other sites

Just been reading the help files, Brett - I can't see why the Object isn't getting created? The example there uses Excel and gives:

$oExcel = ObjCreate("Excel.Application") ; Create an Excel Object

$oExcel.Visible = 1 ; Let Excel show itself

$oExcel.WorkBooks.Add ; Add a new workbook

$oExcel.ActiveWorkBook.ActiveSheet.Cells(1,1).Value="test" ; Fill a cell

sleep(4000) ;See the results for 4 seconds

$oExcel.ActiveWorkBook.Saved = 1 ; Simulate a save of the Workbook

$oExcel.Quit ; Quit Excel

which looks very similar to me to:

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open("R:\ICT\Projects Raised\IT project raised spreadsheet.xls")

$oExcel.ActiveWorkbook.Sheets("Sheet2").Select()

I tried to read the error handling section but that was way over my head - this is my first script, after all! I'm going to need some gentle hand holding - sorry!

It sounds like the object is not creating. Try running this in a script by itself and let me know if it returns a 1 or 0.

$oExcel = ObjCreate("Excel.Application")
MsgBox(0,"",IsObj($oExcel))

Also, are you running the most current version of AutoIt v3.2.12.1?

Share this post


Link to post
Share on other sites

It sounds like the object is not creating. Try running this in a script by itself and let me know if it returns a 1 or 0.

$oExcel = ObjCreate("Excel.Application")
MsgBox(0,"",IsObj($oExcel))

Also, are you running the most current version of AutoIt v3.2.12.1?

Hi Eric,

It returns a "1".

Yes, I am running the latest version - but I re-downloaded just in case :-)

Share this post


Link to post
Share on other sites

Hi Eric,

It returns a "1".

Yes, I am running the latest version - but I re-downloaded just in case :-)

It is creating the object then. It sounds like Excel isn't playing nicely. What version of Excel are we using?

Share this post


Link to post
Share on other sites

Also, with CreateObject... it seems that Excel does not load MACROS ...

There may need an addition to ExcelCOM to apply a workaround involving .RunMacros ... although my attempts failed..

I had to read HKCR\.xls and so to get the path to Excel and launch...

EXCEL.EXE /e c:\pathTo\file.xls

... to get the macros to load.

Lar.


f_mrcleansmalm_77ce002.jpgAutoIt has helped make me wealthy

Share this post


Link to post
Share on other sites

Also, with CreateObject... it seems that Excel does not load MACROS ...

There may need an addition to ExcelCOM to apply a workaround involving .RunMacros ... although my attempts failed..

I had to read HKCR\.xls and so to get the path to Excel and launch...

EXCEL.EXE /e c:\pathTo\file.xls

... to get the macros to load.

Lar.

It sounds like I'm biting off quite a bit more than I can chew here :-( Having said that if anyone gets this working please post here!

Thanks for your efforts, everyone.

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