n00b help with automating Excel sheets

[ThomasQ]

Hi everyone!

I'm new to autoit, and been fiddlin around with it for some days, trying to make a script that will load data from excell sheets into a form on a website.

So far so good, the basics have been covered, but the problems started when I had to switch between windows (IE & Excel). I tried the Controllistvieuw option etc, but Au3info doesnt give me a Controller ID for the sheet.

So I'm looking for a line of code that'll switcht between windows, or some other good way to select individual or grouped cells in Excel!

Thanks in advance!

Thomas

[water]

There are UDFs (User defined functions) available for Excel and Internet Explorer. See Help File -> User Defined Functions -> Excel Management and IE Management.

That should get you started.

Edited July 23, 2009 by water

[ThomasQ]

Yess,

a n00b says thanks!!!

[ThomasQ]

Well, I'm still baffled,

Before I just set keycombos to navigate & copy, but I really can't get the ExcelReadCell comand to work.

This is what I got so far on the Excel side of things, while I tried to get the data from cell C11 from the TestCleanSheet into my Clipboard:

Run(@comspec & ' /c "C:\Documents and Settings\Adminstrator\Desktop\Projects\Dat\TestCleanSheet.xls"','',@sw_hide)

Opt("WinTitleMatchMode", 3)

WinWaitActive( "Microsoft Excel - TestCleanSheet.xls")

#Include <Excel.au3>

$val = _ExcelReadCell($TestCleanSheet.xls, $C11)

Is the whole clipboard thing even nescesary, or is their another way to get the returned Data from the ExcelReadCell comand into the form on the website?

[water]

You are mixing two techniques. The UDF accesses Excel usign the COM interface. Therefore: No need to run the exe and copy with windows.

This should do what you need:

#include <excel.au3>

$oExcel = _ExcelBookOpen("C:\temp\test.xls",1,True)        ; Open test.xls readonly and visible
$val     = _ExcelReadCell($oExcel, "C11")
ConsoleWrite($val & @CRLF)
_ExcelBookClose($oExcel)

[water]

The IE part of your script should look similar:

$oIE = _IECreate("your URL")
_IELoadWait($oIE)
_IEFormElementSetValue("... please see the example in the helpfile on how to get a reference to the fields in a form")

Edited July 23, 2009 by water

[ThomasQ]

Allright, thanks!

I had been fiddling with the ConsoleRead option, before I read your last post.

So I read the Help File & examples, but they lost me after example 4: Set the value of an Input Type=File element.

I just need a little bit more help. However, we are nearing the end

The data read from the excel cell is in the STD stream (hence why I tried ConsoleRead).. But what should be the parameters to the _IEfomElementSetValue function?

Here's howfar I got, trying to paste the Data from excell into the google search form:

#include <excel.au3>

$oExcel = _ExcelBookOpen("C:\Documents and Settings\Q\Desktop\Projects\INdat\TestCleanSheet.xls",1,True) ; Open test.xls readonly and visible

$val = _ExcelReadCell($oExcel, "D5")

ConsoleWrite($val & @CRLF)

_ExcelBookClose($oExcel)

Sleep(1000)

#include <IE.au3>

$oIE = _IECreate ("www.google.com")

_IELoadWait($oIE)

_IEFormElementSetValue ($o_???)

When I got that settled, I got two last questions. I need to fill multiple forms on the same page with the data from one excel sheet.

Should I go back and forth writing data from Excel to STD to the forms, and how do I select witch form on the page to use?

Many thanks in advance for your help, and the quick responses!!

[ThomasQ]

I Got it! Thanks!