Jump to content
Sign in to follow this  
JonMay

Arrays and pasting

Recommended Posts

JonMay

Hello :)

I was hoping someone could help me with arrays.

Essentially I'm trying to copy eight different cells from Excel, adjacent to one another and paste them into eight different fields of a web form.

I made a start on copying from Excel:

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

WinWaitActive("TeamDataSheet")
$oExcel.Visible = 1

;First Name
WITH $oExcel.activesheet
.range("A12:A12").copy
EndWith

;Last Name
WITH $oExcel.activesheet
.range("B12:B12").copy
EndWith


MsgBox (0,"","All done")

And it copies the first field, which is immediately overwritten by the second. I read about Arrays but I wasn't quite sure how to get data copied from an Excel Application added onto the array.

Then, I'm trying to paste this into fields in an Internet Explorer form:

#include <IE.au3>
WinWaitActive("Active Team Sheet")
$oIE = _IEAttach("Active Team Sheet")
$oForm = _IEFormGetObjByName($oIE, "form1")
$gridID_fn = "GridViewStudents$ctl04$txtStudentName"
$gridID_ln = "GridViewStudents$ctl04$txtLastName"

;First Name
$newfn = Send("^V")
$o_fn_Text = _IEFormElementGetObjByName($oForm, $gridID_fn)
_IEFormElementSetValue($o_fn_Text, $newfn)

;Last Name
$newfn = Send("^V")
$o_ln_Text = _IEFormElementGetObjByName($oForm, $gridID_ln)
_IEFormElementSetValue($o_ln_Text, $newln)


MsgBox (0,"","All done")

The functions do work individually pasting the correct information in it's assigned box in the form, but scaling it up from one seems to have me scratching my head. Should I try and delay each step for it to finish and loop back?

I've created them in different files, I'm not sure whether it'd be best to have them as one file and to then try and copy each cell to a different variables in an array.

I've got the

WITH $oExcel.activesheet
.range("A12:A12").copy

I'm just not sure how to convert Excel data into an array.

Many thanks in advance

Share this post


Link to post
Share on other sites
water

Just a few questions.

Why don't you use the Excel UDF?

Why do you use Send("^V")?

Use _ExcelReadCell and _IEFormElementSetValue.


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
JonMay

Thank you! I have no idea why I thought that was a good idea.

Looking back I'm not even sure I was thinking it through properly.

Managed to copy each cell:

#include 
#include 

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

WinWaitActive("TeamDataSheet")
$oExcel.Visible = 1


WITH $oExcel.activesheet
Local $sCellValue_fn = _ExcelReadCell($oExcel, 12, 1)
Local $sCellValue_ln = _ExcelReadCell($oExcel, 12, 2)
Local $sCellValue_lv = _ExcelReadCell($oExcel, 12, 3)
Local $sCellValue_bs = _ExcelReadCell($oExcel, 12, 4)
Local $sCellValue_fd = _ExcelReadCell($oExcel, 12, 5)
Local $sCellValue_gn = _ExcelReadCell($oExcel, 12, 6)
Local $sCellValue_gy = _ExcelReadCell($oExcel, 12, 7)
Local $sCellValue_em = _ExcelReadCell($oExcel, 12, 8)

EndWith

And then when switch over to Internet Explorer:

WinWaitActive("Active Team Sheet")
$oIE = _IEAttach("Active Team Sheet")
$oForm = _IEFormGetObjByName($oIE, "form1")
$gridID_fn = "GridViewStudents$ctl04$txtStudentName"
$gridID_ln = "GridViewStudents$ctl04$txtStudentLastName"
$gridID_lv = "GridViewStudents$ctl04$ddlAcademicYearID"
$gridID_bs = "GridViewStudents$ctl04$ddlbDegree"
$gridID_fd = "GridViewStudents$ctl04$ddlbFieldofStudy"
$gridID_gn = "GridViewStudents$ctl04$ddlGenderID"

;First Name
$newfn = $sCellValue_fn
$o_fn_Text = _IEFormElementGetObjByName($oForm, $gridID_fn)
_IEFormElementSetValue($o_fn_Text, $newfn)

Thank you for your pointing in the right direction :)

Share this post


Link to post
Share on other sites
water

Yes, that looks much cleaner and will run more reliable!


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

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.