Macro Speed

[vzachin]

i have a macro that executes a query for a report. once the report appears on the screen, i execute a 2nd macro to scrape the data.

i combined the 2 macros but it is failing because the data from the report takes anywhere from 30 seconds to 5 minutes, sometimes even 10 minutes to appear on the screen.

how can i have the macro pause while the data is being retrieved and then continue once it appears on the screen?

i've tried the following to no avail:
Do While Sess0.screen.OIA.Xstatus <> 0
Do Events
Loop


thanks
vzach

 

[SkipVought]

vzach,

Are you tapping something like a DB2 table on the mainframe?

When you scrape the screen, where does the data end up being used? Do you then import into MS Access or Excel?

There might be a much easier way to do the entire process.

Skip,
[sub]
[red][/red]
[/sub]

 

[vzachin]

hi skip,

the data i retrieve is not on a DB2 table.i'm just extracting certain fields from a database on the mainframe.
i scrape the data into an Excel workbook.
i have my macros stored in Excel as well as in Extra.
vzach

 

[calculus]

I would put in a loop that waits for a specific field to appear that means the data is there.

For instance, if you knew that a field would go from " " to real data, you could do this:

Do while Trim(Sess0.Screen.GetString(MyX,MyY,5)) = ""
Doevents
Loop

This will wait until it see's non-spaces.

Hope it helps,
calculus

 

[SkipVought]

I guess I wanted the answer to...

are you scraping an INQUIRY formatted screen or

are you querying a database and getting a screen report to scrape?

Skip,
[sub]
[red][/red]
[/sub]

 

[vzachin]

hi calculus,

i tried as you suggested as follows but it jumps to END SUB.

Sess0.screen.SendKeys ("<PF1>")
Sess0.screen.WaitHostQuiet (1000)
Do While Trim(Sess0.Screen.GetString(24,2,7))= "DESIRED"
Doevents
Loop
The word "DESIRED" appears (24,2,7) when the data is ready to be viewed. Prior to the data appearing is the word "QUERY".

what am i doing wrong?

thanks
vzach

 

[vzachin]

hi SkipVought ,

i am querying a database and getting a screen report

 

[IknowMe]

Do While Trim(Sess0.Screen.GetString(24,2,7)) <> "DESIRED"


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

Do While Sess0.Screen.GetString(24,2,7) <> "DESIRED"

no sense in trimming your grabbing 7 characters.


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[SkipVought]

If you are performing a query, you could do it directly from Excel using Data/Get External Data/New Database Query...

assuming that you have an ODBC driver configured.

its pretty simple and cuts down on alot of useless steps.

Skip,
[sub]
[red][/red]
[/sub]

 

[vzachin]

hi Skip,

Unfortunately i'm not savvy enough to perform the type of query you mention.
i would have to check with my folks about this.

thanks
vzach

 

[vzachin]

hi MrMilson,

i tried your suggestion and that doesn't work either.
it goes to the end sub right away.

When i submit the query, i hit the ("<PF1>")key.
the screen does not change until data is returned from my query. When the data finally appears,the word "DESIRED" appears at(24,2,7).
The time frame from when i hit ("<PF1>") to the time the data appears can be anywhere from 30 seconds to 10 minutes.
i want the macro to pause after i hit ("<PF1>"), and then resume when the word "Desired" appears at (24,2,7).

 

[vzachin]

hi folks,

i have it figured out, finally!

i did this:

Do While Sess0.screen.GETstring(24, 2, 7) <> "DESIRED"
Sess0.screen.WaitHostQuiet (500)
Loop

i didn't understand where the LOOP statement went. i had originally put the LOOP at the end of the code.
thanks for all your help!

vzach

 

[IknowMe]

I'd add that DoEvents back in your loop. As i'm reading I think you were using it to explain that you had maore code in your loop. It's actually a useful function in your stall loop.

Do While Sess0.screen.GETstring(24, 2, 7) <> "DESIRED"
DoEvents
Loop

It will give your CPU a chance to breathe and help you avoid 'Helicoptering'

And Drop the line
Sess0.screen.WaitHostQuiet (500)
It's not needed and will slow you down


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[vzachin]

MrMilson,
thanks for the advice

 

[Pimafix]

Would this also work if the code runs to fast? My code goes from Excel to Extra and in Extra goes way to fast, it won't stop long enough to execute a wait string. Will the Do While work to slow down the execution?

 

[Skie]

Any reason you can't just code it all in Excel?