Jump to content

Excel ActiveCell Not working


Recommended Posts

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

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...