24 replies to this topic

[GoldenMike]

Hi,
A few months ago i wrote a script something very simple. Opening an exel file, getting the active cell contents and copying and pasting those contents into a web browser. I believe in the interim i have updated to Office 2007, and am no longer able to use the active cell command. I have tried this on another computer and get the same problem. Does anyone know what could be causing this. Ideally I'd like to setup a hot key that automates a few processes where the first processess is grabbing the contents of the active cell. Below is a few lines of simple code and the error message. I make sure that before i run the script I open excel, and click on a cell to ensure that one cell is active.

$oExcel = _ExcelBookAttach("C:\Golden Medical Billing\Clients\Dr. Meglio\Aging Reports\2012\Meglio Aging 03-08-12.xlsx")
If @error = 1 Then MsgBox(0, "Error code is: "&@error, "Could not Attach Excel Book returning: " &$oExcel)

$oActiveCell = $oExcel.ActiveCell.value

This is the error message:

C:\Users\mike\Documents\AutoIt\test.au3 (12) : ==> The requested action with this object has failed.:
$oActiveCell = $oExcel.ActiveCell.value
$oActiveCell = $oExcel.ActiveCell^ ERROR

[water]

Add a COM error handler and you will see where the problem is:

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc"); User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

at the top of your script

[GoldenMike]

Water,

I put in error handler as per your suggestion. It kept looping and I had to terminate the script. Here were the results, which I don't think show anything:

err.number is: 0
err.windescription: 0
err.description is: 0
err.source is: 0
err.helpfile is: 0
err.helpcontext is: 0
err.lastdllerror is: 0
err.scriptline is: 0
err.retcode is: 0

[water]

I see no reason for the script to loop. Can you please post the complete script you are running?

[GMK]

Maybe you need to use IsObj to check the status of $oExcel. Is the workbook opening without problems?

What does the following script do for you?

#include <Excel.au3>
$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, "Test", "A1")
$sActiveCell = $oExcel.ActiveCell.Value
MsgBox(0, "Active Cell Value", $sActiveCell)

[GoldenMike]

Here is the code i use:

Plain Text        expandcollapse  popup

#include <IE.au3>
#include <Array.au3>
#include <EXCEL.au3>
#include <GUIConstantsEx.au3>
;Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc"); User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)  
    ; Do anything here. 
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _          
        "err.windescription:" & @TAB & $oError.windescription & @CRLF & _           
        "err.description is: " & @TAB & $oError.description & @CRLF & _         
        "err.source is: " & @TAB & $oError.source & @CRLF & _           
        "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _           
        "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _         
        "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _           
        "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _           
        "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
    EndFunc   ;==>_ErrFunc

$oExcel = _ExcelBookAttach("C:\Golden Medical Billing\Clients\Dr. Meglio\Aging Reports\2012\Meglio Aging 03-08-12.xlsx")
If @error = 1 Then MsgBox(0, "Error code is: "&@error, "Could not Attach Excel Book returning: " &$oExcel)
  
$oActiveCell = $oExcel.ActiveCell.value

[water]

The COM error handler isn't active because the ";" at the start of line ";Local $oErrorHandler .." makes it a comment.
Can you remove the ";"?

[kylomas]

GoldenMike,

...and run it under SCITE...just ran it against one of my spread sheets and it returned "invalid name"...

kylomas

[Reg2Post]

Where is the first instance of Excel that the workbook could attach to?

If there is no Excel file open then assigning $oExcel.ActiveCell.value to a variable will fail.

[GoldenMike]

Water,

I removed the ";" before "Local $oErrorHandler" and I still got the same result. It still kept looping and had to terminate the scripts.

Kylomas & Reg2Post,

Its attaches fine to the spreadsheet. If i run a script with a specified cell and row, I will get the value of the cell. I am running it under SciTe versus complining it, not really sure what difference that makes.

[Reg2Post]

Have you tried activating the worksheet that you want the activecell value before trying to assign?

[GoldenMike]

Reg2Post,

I get this error when i try activating the sheet:

$oExcel = _ExcelBookAttach("C:Golden Medical BillingClientsDr. MeglioAging Reports2012Meglio Aging 03-08-12.xlsx")
If @error = 1 Then MsgBox(0, "Error code is: "&@error, "Could not Attach Excel Book returning: " &$oExcel)
_ExcelSheetActivate($oExcel, "Sheet1")

C:Program FilesAutoIt3IncludeEXCEL.au3 (1063) : ==> The requested action with this object has failed.:
Local $iTemp = $oExcel.ActiveWorkbook.Sheets.Count
Local $iTemp = $oExcel.ActiveWorkbook^ ERROR
>Exit code: 1 Time: 0.413

I know i am connecting to the workbook correctly because when i tested it by changing the file name i get a pop up error message stating that it can't connect.

[GMK]

So are you attaching to an existing Excel window? Have you tried using _ExcelBookOpen, instead of _ExcelBookAttach?

[water]

What version of AutoIt do you run?
Do you run the full version of Excel or a trial?

[GoldenMike]

GMK,

Amazingly this works. Not sure why? Here is the code I changed it to:

HotKeySet ("{F11}", "UpdateComments")

$oExcel = _ExcelBookOpen("C:\Golden Medical Billing\Clients\Dr. Meglio\Aging Reports\2012\Meglio Aging 03-08-12.xlsx")
If @error = 1 Then MsgBox(0, "Error code is: "&@error, "Could not Attach Excel Book returning: " &$oExcel)

_ExcelSheetActivate($oExcel, "JA 1st Fax")
Msgbox(0, "a", $oExcel.ActiveCell.value)

Func UpdateComments()
    Msgbox(0, "a", $oExcel.ActiveCell.value)
EndFunc

While 1
   Sleep(100)
Wend

Do you have any ideas why this works this way, and why when i attach it it does not seem to recognize the active cell?

[GoldenMike]

Water,

AutoIt V3.3.6.1
Excel is Office Professional 2007

[GMK]

Do you have any ideas why this works this way, and why when i attach it it does not seem to recognize the active cell?

I've duplicated this problem myself and I'm not sure exactly why _ExcelBookAttach is causing problems.

[water]

Can you upgrade to the most current version of AutoIt (3.3.8.1) and install the newest beta (3.3.9.2) and give both a try?

[water]

Another thing to try:
Use oldfashioned xls files. I know that the UDF has - at least when writing - problems with the new extensions.

[GoldenMike]

Water,

I updated and tried both versions as per your request, and am getting the same error.