Sign in to follow this  
Followers 0
ThomasQ

n00b help with automating Excel sheets

8 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yess,

a n00b says thanks!!!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites

I Got it! Thanks!

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