Help transfering data from Excel to AS400 emulator

E Coli · Post by **E Coli** » Sun Feb 02, 2003 7:29 pm

Hi All,

Hope someone out there can help me. I've written lots of macros that work inside Excel and Access, but this is the first time I've wanted to do something outside them. Here is my problem:

Essentially, I'm trying to automate data entry. I'd like to open a particular Excel spreadsheet, save the data on sheet 1 into an array, then transfer this data one cell at a time into our AS400 emulator. I was planning on doing a sendkeys type operation to send the data. As far as my AS400 emulator would know, I'm just a fast typist. I've found that I can't run this from inside Excel, because it doesn't send the sendkeys until after VB finishes executing. That would overrun my AS400 emulator buffer. So running it from inside Excel appears to be out as a solution.

Trouble is, I'm new to scripting and I haven't found an example that looks like what I'm trying to do. Does anyone have any ideas where I could find some similar examples?

Thanks in advance for helping a newbie!

Eric

Lumumba · Post by **Lumumba** » Mon Feb 03, 2003 8:00 am

What about the clipboard?

Script A (VBA):
Puts to data to the clipboard
Creates file: C:\AS400.not
Waits now till AS400.not will be gone/deleted
If finished to process, creates file: C:\AS400DONE.txt

Script B (Msched):

Code: Select all

Label>Wait4Content
IfFileExists>C:\AS400DONE.txt,End
IfFileExists>C:\AS400.not,TakeData,Wait4Content

Label>TakeData
GetClipboard>Content
PutClipboard>
If>Content=,Wait4Content,Paste2AS400

Label>Paste2AS400
SetFocus>AS400Title
Send>%Content%
SetFocus>ExcelSheet
Del>AS400.not
Goto>Wait4Content

Label>End
Del>C:\AS400DONE.txt
Message>AS400 done !

B runs in parallel, waiting for notifier (AS400.not) . If available, takes content from clipboard. Wipes the clipboard. Paste content to AS400 window. Deletes notifier.
A waits till notifier is gone to go on processing ...

Lumumba · Post by **Lumumba** » Mon Feb 03, 2003 8:05 am

Ooops, please check this line at the code above:
Del>C:\AS400.not

E Coli · Post by **E Coli** » Mon Feb 03, 2003 9:25 am

I like the idea! I hate to ask for more (but remember, I'm very new at scripting!). Can you help me with the syntax of Script A, opening the existing Excel file and copying the data to the clipboard? Also, how do I know when I've reached the end of the Excel data file? Is there any way to copy the data as an array, rather than one cell at a time?

I apologize for my dumb questions. I'm really not dumb, just very new to scripting and using VBA outside of Excel.

Thanks once again for your help!

Eric

Lumumba · Post by **Lumumba** » Mon Feb 03, 2003 2:45 pm

1) I have Macro Scheduler 6.x which doesn't provide the array functionality (Str[1]=...). So it's just an idea

Check the command reference of your Macro Scheduler release for details.

Let>cc=0
Let>maxcc=10
Let>k=0
Let>maxk=10

Label>ReadExcel
Add>lc,1
DDERequest>Excel,c:\test\test.xls,R%cc%C%cc%,Answer,5
Let>Str[%cc%]=%Answer%
If>cc=%maxcc%,Paste2AS400,ReadExcel

Label>Paste2AS400
SetFocus>AS400Title

Repeat>k
Add>k,1
Send>Str[%k%]
Wait>2
Until>k,%maxk%