COM & Excel

[Timo]

Hello,

can someone please explain me how to send data with COM to Excel and receive data from it?

The examples I found in the forum are sometimes difficult to understand.

Can someone post a simple example so that we can easily understand it?

Programs like Kixtart cannot be easier and better than our Autoscript!?

Thanks,

Timo

[bshoenhair]

No time to explain, but you should be able to understand the following example.

$Excel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($Excel) then
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif
$Excel.Visible = 1  ; Let the guy show himself - Set to 0 to run excel invisible
$NewBook = $Excel.workbooks.add  ; Add a new workbook
; Example: Fast Fill some cells with there address
Msgbox (0,"","Click 'ok' to fastfill some cells")
For $c In $NewBook.Sheets("Sheet1").Range("a1:n1").Cells
    $c.Value = $c.Address
    For $i = 1 To 8
        $c.Offset($i,0).Value = $c.Offset($i,0).Address
    Next
Next

msgbox(0,"ExcelTest","Click 'ok' to read the cells in each column.")

;Now lets read the values we just added
$Values = ""
For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells
    $Values = $Values & $c.Text & @TAB
Next
msgbox(0,"Values Read",StringStripWS($Values,3))
$Excel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
$Excel.quit       ; Get rid of him.
$Excel = 0        ; Loose this object.
              ; Object will also be automatically discarded when you exit the script
exit

[Timo]

Thanks.

Thanks.

No need to explain, some lines of code are much better than the best explaining...

Now I can go on with my script.

Bye,

Timo

[randallc]

Thanks.

Thanks.

No need to explain, some lines of code are much better than the best explaining...

<{POST_SNAPBACK}>

How come I get an error; (line 1 "objcreate" nnot recognised"; yet I have AutoIT 3.1.1 installed?

Randall

[bshoenhair]

You must use latest Beta with COM support.

[DaleHohm]

Go to the Developer Forum here and look for the topic 3.1.1++

[a_user]

This thread was very helpful to me but can anyone show me the syntax to execute EXCEL's VLOOKUP command (sending the required fields for VLOOKUP) and get the result back into the AutoIt script? I can't seem to get the syntax correct for executing an EXCEL function.

[ShelbySue]

This thread was very helpful to me but can anyone show me the syntax to execute EXCEL's VLOOKUP command  (sending the required fields for VLOOKUP) and get the result back into the AutoIt script?  I can't seem to get the syntax correct for executing an EXCEL function.

<{POST_SNAPBACK}>

My best learning experience is to open Excel and develop the code there. Then, transfer that into the syntax of my scripting language. ( vbscript or autoit ).

Also, if you will record macros in excel VBA, you can see some of the different methods, properties, and parameters and get ideas as to what to use.

VLOOKUP is a worksheet function and not a VBA function. You can insert a VLOOKUP in your original worksheet and just reference that cell OR..

msgbox(0,"Values Read",VLook("Sheet1",3,"a1:b6",6,2,2))

Func VLook($Sheet_Name, $search_for, $Range, $Rows_In_Range, $Columns_In_Range, $Column_To_Return )

for $i = 1 to $Rows_In_Range step $Columns_In_Range

if $NewBook.Sheets($Sheet_Name).Range($Range).Cells($i).Text = $Search_for Then

$i = $i + $COlumn_To_return - 1

return $NewBook.Sheets($Sheet_Name).Range($Range).Cells($i).Text

EndIf

next

return "#N/V#"

EndFunc

[bshoenhair]

VLOOKUP is a worksheet function and not a VBA function.  You can insert a VLOOKUP in your original worksheet and just reference that cell OR..

<{POST_SNAPBACK}>

You are wrong Vlookup can be used in VBA and COM "Using the WorksheetFunction Object"

Used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic or COM.

Excel COM:
$Excel.WorksheetFunction.VLookup(ARg1 , Arg2, Arg3, [Arg4])

Excel VBA:
Application.WorksheetFunction.VLookup(ARg1 , Arg2, Arg3, [Arg4])