Jump to content

Read from excel and type i AS400


Haugaard
 Share

Recommended Posts

Hello i realy need some help getting started on this script.

What i need to do

Read a number i a colum in excel

alt+tap to as400

type the number it have read in excel

wait 5 sec´s

alt+tab back to excel

Read the next number in the colum

alt+tab to AS400

type the number it have read in excel

wait 5 sec´s

and so on, until the "cell" in the colum no loger contain numbers.

This sound simple but i cant get started couse i dot know how to read from a xls document

hope theres some smart guys out there that can help me getting this started

Link to comment
Share on other sites

There are many different ways to accomplish this, depending on your level of ability.

Here are a few:

Read the fields directly (ExcelCOM UDF)

Save the Excel file to a txt/csv and parse the file (FileRead)

Automate the Copy/Switch/Paste keystrokes (Send)

Once you choose an approach and start coding, post it here if you need help.

[font="Tahoma"]"Tougher than the toughies and smarter than the smarties"[/font]

Link to comment
Share on other sites

There are many different ways to accomplish this, depending on your level of ability.

Here are a few:

Read the fields directly (ExcelCOM UDF)

Save the Excel file to a txt/csv and parse the file (FileRead)

Automate the Copy/Switch/Paste keystrokes (Send)

Once you choose an approach and start coding, post it here if you need help.

Thank you.

But i realy have problems getting started, i went for the UDF method. Here is what i figured out so far (ofcouse it does not work)

; Script Start - Add your code below here

#include <ExcelCOM_UDF.au3> ; Include the function collection

$oExcel = _ExcelBookOpen($FilePath="C:\Documents and Settings\thh\Skrivebord\x805.xls") ; Open file

Exit

Link to comment
Share on other sites

Thank you.

But i realy have problems getting started, i went for the UDF method. Here is what i figured out so far (ofcouse it does not work)

; Script Start - Add your code below here

#include <ExcelCOM_UDF.au3> ; Include the function collection

$oExcel = _ExcelBookOpen($FilePath="C:\Documents and Settings\thh\Skrivebord\x805.xls") ; Open file

Exit

You don't pass parameters in AutoIt that way. When you call the function _ExcelBookOpen() you pass the parameter for the file path like this:

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls") ; Open file

Internally to the function, it will get whatever you put in that parameter as the variable $FilePath. You don't set $FilePath yourself, it is handled by the function.

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

OKay here is what i have now and it works

But it cant read the data from excel :)

#include <ExcelCOM_UDF.au3> ; Include the function collection

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls"); Open file
;Read the data Row 3, cell 1

WinActivate("Session A - [24 x 80]"); activate AS400
Send(5656567); Write the data from excel
Sleep(1000)
Send("{ENTER}")
Sleep(1000)
MouseClick ( "Left", 310, 61 , 1); Mouseclick AS400 macro
Sleep(1000)
Send("{ENTER}"); Confirm use of macro
Sleep(7000); Wait for the macro to run, big sleep
WinActivate("Microsoft Excel - x805.xls"); activate Excel
; Read data Row 3, cell 2
WinActivate("Session A - [24 x 80]"); activate AS400

Exit

Need help for reading the cell and writing it to as400 and for a loop

Link to comment
Share on other sites

There is no help file for ExcelCOM_UDF yet, so you have to open the UDF (ExcelCOM_UDF.au3) and read the headers on the various functions.

In this case, you are looking for _ExcelReadCell():

;===============================================================================

;

; Description: Read information from the active worksheet of the specified Excel object.

; Syntax: $val = _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

; Parameter(s): $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

; $sRangeOrRow - Either an A1 range, or an integer row number to read from if using R1C1

; $iColumn - The column to read from if using R1C1 (default = 1)

; Requirement(s): None

; Return Value(s): On Success - Returns the data from the specified cell

; On Failure - Returns 0 and sets @error on errors:

; @error=1 - Specified object does not exist

; @error=2 - Specified parameter is incorrect

; @extended=0 - Row out of valid range

; @extended=1 - Column out of valid range

; Author(s): SEO <locodarwin at yahoo dot com>

; Note(s): This function will only read one cell per call - if the specified range spans

; multiple cells, only the content of the top left cell will be returned.

;

;===============================================================================

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

; ...

EndFunc ;==> _ExcelReadCell

So, your call would be _ExcelReadCell($oExcel, 3, 2)

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

There is no help file for ExcelCOM_UDF yet, so you have to open the UDF (ExcelCOM_UDF.au3) and read the headers on the various functions.

In this case, you are looking for _ExcelReadCell():

So, your call would be _ExcelReadCell($oExcel, 3, 2)

:)

That was just what i was looking for, couse there are so few exambels of use that i cant figure out the system.

Im sure this will help me alot, thanks

Link to comment
Share on other sites

Well how can i the loop it?

Just make it like +1 and loop?

THe loop is done and work okay but not as i want it to.

It ither skips the first cell or it does not add +1 depending on where i place the $selle = selle + 1

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls"); Open file


Do
WinActivate("Microsoft Excel - x805.xls"); activate Excel
$selle = 1
$data1 = _ExcelReadCell($oExcel, $selle, 3);Read data
$selle = $selle + 1
Sleep(1000)
WinActivate("Session B - [24 x 80]"); activate AS400
Send($data1); Write the data from excel
Sleep(500)
Send("{ENTER}")
Sleep(500)
MouseClick ( "Left", 310, 61 , 1); Mouseclick AS400 macro
Sleep(500)
Send("{ENTER}"); Confirm use of macro
Sleep(3000); Wait for the macro to run, big sleep
until $data1 = 9999999; Do this to cells are empty not 4547535 (syntax for empty cell??
Exit
Link to comment
Share on other sites

The problem was that you were setting $selle = 1 within your loop, so the value never goes anywhere.

What you want is a For loop.

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls"); Open file

For $selle = 1 To 65536
    WinActivate("Microsoft Excel - x805.xls"); activate Excel
    $data1 = _ExcelReadCell($oExcel, $selle, 3);Read data
    If $data1 = 9999999 Then ExitLoop; Do this to cells are empty not 4547535 (syntax for empty cell??
    Sleep(1000)
    WinActivate("Session B - [24 x 80]"); activate AS400
    Send($data1); Write the data from excel
    Sleep(500)
    Send("{ENTER}")
    Sleep(500)
    MouseClick("Left", 310, 61, 1); Mouseclick AS400 macro
    Sleep(500)
    Send("{ENTER}"); Confirm use of macro
    Sleep(3000); Wait for the macro to run, big sleep
Next
Exit
I don't have Excel loaded, so I don't know if an empty cell = 9999999.

But maybe you have some other criteria you can use to verify the value.

[font="Tahoma"]"Tougher than the toughies and smarter than the smarties"[/font]

Link to comment
Share on other sites

The problem was that you were setting $selle = 1 within your loop, so the value never goes anywhere.

What you want is a For loop.

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls"); Open file

For $selle = 1 To 65536
    WinActivate("Microsoft Excel - x805.xls"); activate Excel
    $data1 = _ExcelReadCell($oExcel, $selle, 3);Read data
    If $data1 = 9999999 Then ExitLoop; Do this to cells are empty not 4547535 (syntax for empty cell??
    Sleep(1000)
    WinActivate("Session B - [24 x 80]"); activate AS400
    Send($data1); Write the data from excel
    Sleep(500)
    Send("{ENTER}")
    Sleep(500)
    MouseClick("Left", 310, 61, 1); Mouseclick AS400 macro
    Sleep(500)
    Send("{ENTER}"); Confirm use of macro
    Sleep(3000); Wait for the macro to run, big sleep
Next
Exit
I don't have Excel loaded, so I don't know if an empty cell = 9999999.

But maybe you have some other criteria you can use to verify the value.

Great! well i dont have other criteria other then that the script should stop when the cells in excel are empty.

So i just added 9999999 at the buttom of the excel cells.

I made this script for work. I have to make about 200 pages with 12 Produkt numbers active.

Now i can just make as400 prit the workload til an excel doc and add 9999999 in the last cell and lean back! :)

and it will allso be usefull at other times.

Well its ot perfect so i will work some more on it.

Thx for your replies and help

Link to comment
Share on other sites

The problem was that you were setting $selle = 1 within your loop, so the value never goes anywhere.

What you want is a For loop.

$oExcel = _ExcelBookOpen("C:\Documents and Settings\thh\Skrivebord\x805.xls"); Open file

For $selle = 1 To 65536
    WinActivate("Microsoft Excel - x805.xls"); activate Excel
    $data1 = _ExcelReadCell($oExcel, $selle, 3);Read data
    If $data1 = 9999999 Then ExitLoop; Do this to cells are empty not 4547535 (syntax for empty cell??
    Sleep(1000)
    WinActivate("Session B - [24 x 80]"); activate AS400
    Send($data1); Write the data from excel
    Sleep(500)
    Send("{ENTER}")
    Sleep(500)
    MouseClick("Left", 310, 61, 1); Mouseclick AS400 macro
    Sleep(500)
    Send("{ENTER}"); Confirm use of macro
    Sleep(3000); Wait for the macro to run, big sleep
Next
Exit
I don't have Excel loaded, so I don't know if an empty cell = 9999999.

But maybe you have some other criteria you can use to verify the value.

Great! well i dont have other criteria other then that the script should stop when the cells in excel are empty.

So i just added 9999999 at the buttom of the excel cells.

I made this script for work. I have to make about 200 pages with 12 Produkt numbers active.

Now i can just make as400 prit the workload til an excel doc and add 9999999 in the last cell and lean back! :)

and it will allso be usefull at other times.

Well its ot perfect so i will work some more on it.

Thx for your replies and help

Link to comment
Share on other sites

  • 6 years later...

Hi,

I'm having the same problem. I'm using AS400 and Excel as well but when I try to copy your code it says, "Unrecognized key-action in the highlighted line."

How do i do it?

Please help.

Thanks

Pinat

Link to comment
Share on other sites

Great! well i dont have other criteria other then that the script should stop when the cells in excel are empty.

So i just added 9999999 at the buttom of the excel cells.

I made this script for work. I have to make about 200 pages with 12 Produkt numbers active.

Now i can just make as400 prit the workload til an excel doc and add 9999999 in the last cell and lean back! :)

and it will allso be usefull at other times.

Well its ot perfect so i will work some more on it.

Thx for your replies and help

 

Hi,

 

I'm having the same problem. I'm using AS400 and Excel as well but when I try to copy your code it says, "Unrecognized key-action in the highlighted line."

 

How do i do it?

 

Please help.

 

Thanks

 

Pinat

Link to comment
Share on other sites

Welcome to AutoIt and the forum!

The last post on this thread is about 6 1/2 years old and the OP has been offline for about 6 years.

Please don't necro old threads!

I suggest to open a new one.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Pinat:  Your main problem is using old code.  Could you explain what you're trying to do?  I used to do exactly what you're trying to do now.

 

Hi Blue,

I'm doing a similar job as Hauggard, the author of this post..and here's what i do:

1. from as400 screen, i alt+tab to go excel screen

2. i copy the text from a cell in excel

3. alt+tab to go as400 screen

4. paste

5. alt+tab again to go excel screen

6. copy another text

7. alt+tab to go as400 screen & paste

I'm doing this over & over again..I need a macro to fasten my work.

Thanks,

Pinat

Link to comment
Share on other sites

Welcome to AutoIt and the forum!

The last post on this thread is about 6 1/2 years old and the OP has been offline for about 6 years.

Please don't necro old threads!

I suggest to open a new one.

 

Hi,

Can u give me a link to make new posts?

My time to surf is limited thats why i just responded to this old posts.

Thanks,

Pinat

Link to comment
Share on other sites

Go to the General Help and Support forum and click on "Start new topic".

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...