Jump to content
Sign in to follow this  
GoldenMike

Excel ActiveCell Not working

Recommended Posts

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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
GoldenMike

Here is the code i use:

#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

Share this post


Link to post
Share on other sites
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 ";"?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
kylomas

GoldenMike,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Reg2Post

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

Share this post


Link to post
Share on other sites
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 Files\AutoIt3\Include\EXCEL.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.

Share this post


Link to post
Share on other sites
GMK

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

Share this post


Link to post
Share on other sites
water

What version of AutoIt do you run?

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
GoldenMike

Water,

AutoIt V3.3.6.1

Excel is Office Professional 2007

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Another thing to try:

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GoldenMike

Water,

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

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  

×