BETA 81: COM/Obj: Working with Excel

[Blue_Drache]

Greetings,

I've stumbled on my first need for using COM and Objects within my AutoIt scripts. I have an existing Excel window open, and need to be able to manipulate it. From my initial tests, I can't seem grab anything from something that's already open, unfortunately. Am I going about this the wrong way?

Basically, I need the text from the $oExcel.ActiveWorkbooks.ActiveSheet.ActiveCell location to put it into a variable, fill the cell with the default background color, and then move down one cell in the column and run other code.

I've got the other code built, BUT...this is my sticky wicket....I can do it in VB, but I am not allowed to distribute "foriegn" DLL files (to include the AutoItX.dll).

Source code follows. It's not very pretty, but it works. Thanks for any help in advance.

CODE

; test loop to see if this would work. It didn't.

While 1

Sleep(10)

If WinExists("Microsoft Excel") Then

$oExcel = ObjGet("Microsoft Excel")

$value = $oExcel.activeworkbook.activesheet.activecell

MsgBox(262144,'Debug line ~7','Selection:' & @lf & '$value' & @lf & @lf & 'Return:' & @lf & $value & @lf & @lf & '@Error:' & @lf & @Error) ;### Debug MSGBOX

EndIf

WEnd

Yields following error:

C:\Documents and Settings\xxxxxxx\Desktop\AutoIt\User Scripts\temp.au3 (7) : ==> Variable must be of type "Object".: 
  $value = $MyObject.activeworkbook.activesheet.activecell 
  $value = $MyObject^ ERROR
  >AutoIT3.exe ended.
  >Exit code: 0 Time: 1.557

CODE

; Main program

; Simple ditty for input of DCN conversion to Mobius Number.

; Not so simple any more.

; Written and tested by Shawn Spino, Marshall Texas.

; Written in the AutoIt v3 programming language

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; Version History :

;

; Version 2.7 - Adding in COM function for reading cells

; Version 2.6a - Added an IF for optional DDR functionality

; Version 2.5a - Added OINQ functionality.

; Version 2.4a - changed method of obtaining screen to clipboard - no more menu flashes.

; Version 2.3a - Claims Processor version. Will attempt to retrieve the DCN as stated.

; Version 2.3 - Added automatic retrieval of DCN from A, and ask user for DCN if unable to retrieve.

; Version 1.2 - Added print (Y/N) selection and close (Y/N) selection

; Version 1.1 - Changed the string manipulation from array to direct string editing. Works faster.

; Version 0.1 - Works pretty much as advertised, just a little slow.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; Set Options, Functions, Variables and Includes

Opt ("MustDeclareVars", 1)

#include <SupportFunctions.au3>

Dim $prevclip = ClipGet(), $dcn, $test1, $test2, $ans, $previousDCN, $pass = 0

SetVersion ("Mobius Loop 2.7")

While 1

; Get the DCN from the user

$dcn = ""

Do

$pass = 0

$dcn = InputBox("RETRO DDR PRINTER", "Please enter the 12 digit or 12 digit + 1 alpha DCN.", $dcn, "", -1, -1, 460, 200)

If @error = 1 Then

If WinExists("Locate") Then

WinActivate("Locate")

WinWaitActive("Locate")

AutoItSetOption("SendKeyDelay", 5)

Send("!{space}C")

EndIf

MyExit ()

EndIf

$test1 = StringLen($dcn)

Select

Case ($test1 = 12 And StringIsDigit($dcn) = 1) or ($test1 = 13 And StringIsAlpha(StringRight($dcn, 1)) = 1)

$pass = 1

Case $dcn = $previousDCN

MsgBox(48, "ERROR", "Please input a 12 digit DCN that does not match prior dcn.")

Case Else

MsgBox(48, "ERROR", "Please input a 12 digit or 12 digit + alpha DCN.")

EndSelect

Until $pass = 1 And $dcn <> $previousDCN

$previousDCN = $dcn

; Trim the DCN down to a 10 digit "Mobius" number.

; This is the formula that bred the entire program.

Select

Case $test1 = 12 And StringIsDigit($dcn)

$mobius = StringTrimRight(StringLeft("0" & StringTrimLeft($dcn, 1), 4) & StringRight($dcn, 7), 1)

Case $test1 = 13

$mobius = "0" & Stringtrimleft(stringleft($dcn,4),1) & Stringtrimright(stringright($dcn,8),2)

EndSelect

Select

Case WinExists($ddr)

; Activate Document Direct

WinActivate($ddr)

WinSetState($ddr, "", @SW_RESTORE)

If WinWaitActive($ddr, "", 30) Then

; Need window "Document Explorer" active.

; 99.9% of the time, it is window #1

; re-activating window 1 if only one window active won't hurt anything

Send("{ALTDOWN}W1")

Sleep(750)

; Activate the search menu

AutoItSetOption("SendKeyDelay", 300)

Send("FLM{ALTUP}")

WinWaitActive("Locate")

; Type the Mobius number into the Proper Locate field on DDR

AutoItSetOption("SendKeyDelay", 5)

WinActivate("Locate")

Send($mobius)

WinActivate("Locate")

Send("!O")

; Printing question

$ans = 0

$ans = MsgBox(36, "Print Menu : " & $dcn, "Would you like to print the claim?" & @LF _

& "Please wait until the claim finishes loading before printing." & @LF _

& "You may select NO at any time.")

Select

Case $ans = $yes ; do print functions

WinActivate($ddr)

Send("{altdown}FP{altup}")

WinActivate("Print")

WinWaitActive("Print")

Send("{altdown}EB{altup}{enter}")

WinWaitClose("Print", "Include A&nnotations")

Case $ans = $no

EndSelect

Sleep(500)

WinActivate($ddr)

Send("^{F4}")

Else

MsgBox(0, "Error", "Unable to display in Document Direct. Please use OINQ", 30)

EndIf

Case Not WinExists($ddr)

MsgBox(0x10, "Error!", "Unable to continue without Document Direct running!!!!")

MyExit ()

EndSelect

WEnd

[Valuater]

maybe this

$oExcel = ObjGet("","Excel.Application") ; Get an existing Excel Ob

?????

8)

[Blue_Drache]

maybe this

$oExcel = ObjGet("","Excel.Application") ; Get an existing Excel Ob

?????

8)

That did it. Now I just have to un-fubar my object.class.method notation. Thanks.

EDIT: OK, now what? I got it to read, but the data is garbled.

CODE

If WinExists("Microsoft Excel") Then

$oExcel = ObjGet("","Excel.Application")

$value = $oExcel.activecell

MsgBox(262144,'Debug line ~7','Selection:' & @lf & '$value' & @lf & @lf & 'Return:' & @lf & $value & @lf & @lf & '@Error:' & @lf & @Error) ;### Debug MSGBOX

EndIf

Edited October 10, 2005 by Blue_Drache

[randallc]

Hi,

The object using "Application" needs range directly, as done; and get the value;

$value = $oExcel.activecell.value

Best, Randall Edited October 11, 2005 by randallc

[Blue_Drache]

Alright. Now that I've been spoon fed this code I need some help digesting it.

Earlier I was given $value = ObjGet("","Excel.Application")

I don't understand completely, so bear with me here.....

The help file lists ObjGet as ObjGet("filename",["classname"]).

Using Alt+F11 to get into the VBA scripter and then F2 to list the libraries, I can use anything there that's listed in the "Classes" field of the window, correct? As long, of course, it can attach to the Excel.Application (Pretty much everything)....

Grabbing the object...is that not similar to VB's programming of

VB Example:

With Excel.Application.ActiveCell
     VariableName = .Value
     If Condition(VariableName) = True
        .Value = Foo
     Else
         .Value = Bar
     End If
 End With

Once I get the object, dealing with COM is similar to programming within VB, yes?

[randallc]

Hey, now you're teaching me; I did the "Excelcom" thing "seat of pants", finding what worked, and modifying code generated in vba; no logic; sounds like you will know what you're doing when you get there.

Best, Randall