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

[GenericUser1234]

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!

 

[GenericUser1234]

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

part 4 is taking more information from excel.

 

[SkipVought]

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

 

[GenericUser1234]

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!


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

 

[SkipVought]

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.

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

 

[SkipVought]

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.

 

[GenericUser1234]

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

 

[SkipVought]

For instance, check out thread1-1743778 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?