Sign in to follow this  
Followers 0
Timo

COM & Excel

9 posts in this topic

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


Bye...,Timo

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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


Bye...,Timo

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

You must use latest Beta with COM support.

Share this post


Link to post
Share on other sites

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


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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. :(

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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])

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  
Followers 0