×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

(OP)
I'm really struggling here.

To give a quick overview:

I've got some data that needs to be input into reflection.

The data is filled into several fields

Like A B C D E

I've got the data formatted so that when copied and pasted it will enter into the fields. However I cannot seem to copy and paste commands across.


So I'll need to write some code that will:


1-Copy row 1 (with its 8 variables)
2-Paste into reflection
3-Run a couple of commands (eg next screen etc)
4-paste one last bit of information in
5-Send one command
6- Scrape the screen (only need the reference that is generated)
7-Save the reference.

There are 40-50 rows a day that need to be done hence why I'm looking to automate this to cut down on my work which is hindered by so much manual entry at the moment.


Where I've currently got to:

1- Arranged my data so that the main bulk at the beginning can be copied and pasted manually from Excel
2-Made a list of the various commands that need to be done.

I tried making a generic macro from the record feature, but it was very limited and I can't seem to get any decent results.


I'd really appreciate any help either personally or pointing me towards some resource, as it'd reduce my stress each day quite considerably as I could focus on more important things!

Thanks!

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

(OP)
To clarify part 3, I mean run some command keys in reflection.

part 4 is taking more information from excel.

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

Hi,

What code do you have thus far? Please post and explain where you are having difficulty.

No doubt you have searched here in Tek-Tips and have found some code in this forum as well as forum99: AttachMate solutions

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

(OP)
Hi SkipVought, thanks for the reply. I've read like 300 threads now and you seem to be the all knowledgeable guy of these forums! bigsmile


I haven't got any code to hand at the moment as I'm not at work. I'll post some up when I'm able. The code that I do have is primarily derived from the record feature in reflection 2011. (For reference we're also using Excel 2007 at work).

The main points of difficulty (apart from it all) are:

1: properly referencing the link between attachment reflection 2011 and the document in Excel 2007.
2-Once it takes one line and processes it (which leads to a reference code in reflection being displayed, I'm struggling to just scrape the actual reference code and put it into the excel sheet. Its just scraping the whole screen.

3- Once its done the scraping I don't know how to make it then check the next line and to process til there are no more lines.


I'm fairly new to VBA, as my specialisms haven't really taken me down this path before!


The main thing I need is that whatever solution I can produce, that its rock solid and is something I can rely on, as it'll have to be approved by the higherups for usage.

I work to live...

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

Let me also explain a situation with terminal emulators that is often overlooked. The terminal or screen is the I/O between you (or your program) and the mainframe system. The process is ASYNCHRONOUS to you or your program meaning that when your program runs statement 1 completes before statement 2 starts and statement 2 completes before statement 3 starts and so on, EXCEPT when you SendKeys to the mainframe via your emulator. Your program will keep on going to the next statement while the mainframe processes the information ASYNCHRONOUSLY. It might take a millisecond, 2 seconds, a minute-- who knows? Not even The Shadow knows! Your program has to WAIT. I see so many coders put a hard value wait into their code. It like deciding before you go to the store that when you cross a street, you will ALWAYS wait 5 seconds and the proceed to cross, REGARDLESS OF THE TRAFFIC CONDITIONS! No you look left and right, evaluate the conditions continuously UNTIL the conditions are safe to proceed.

CODE

oScreen.SendKeys("<ENTER>")
Do Until oScreen.WairForCursor(r,c). 'r,c are screen coordinates when cursor rests
  DoEvents
Loop 

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

The other thing is I'm not familiar with Reflections specifically. The keywords and syntax may moy be correct. But I know Excel very well, and I'd recommend coding in Excel VBA rather than in the emulator code editor.

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

(OP)
Okay, I understand that part, in fact I noticed already that the mainframe can lag occasionally for unknown reasons (probably due to the fact that its sending the data abroad) and thus adding in a contraint will help out massively in the long run.

I'll try incorporating the above tomorrow to establish good practice early on. :)

I work to live...

RE: How to pull data from Excel into Reflection (do its magic) and then scrape out reply.

For instance, check out thread1-1743778: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet my first post. It has some of the elements you might apply particularly reading data from Excel and writing to the emulator. But your Excel table will be easier to process.

I assume that your table starts in A1 with one row of headings (a proper table) followed by contiguous rows of data. If it were me, I'd make the table a Structured Table. Makes processing row ranges simpler.

I'd be doing all my coding in the Excel VB Editor as Excel VBA is a Caddilac compared to the Yugo of Attachmate VB editors. You have to have Excel open and accessible anyhow. It like J. G. Wentworth--its your application! Why not use it?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close