How to change EB Macros to VB to work w/.mdb rather than .txt files

[cast]

I'm fairly new to EB, and am self taught from the provided help files (6.5). I've written quite a few macros for many users but have run into a couple of issues.

1. I need my macro output to be into a Access table. (not via a linked .txt or .csv.

2. I need to be able to run multiple (sometimes multiples of the same macro) on multiple sessions.

3. In researching the above two issues, I see it may be easier to write these in VB(A)(#2 may not even be possible in EB), but even using calculus' FAQ I get several issues with my code (getfield is not defined for instance).

My question is this. Is number three a correct assumption? If so how different is the code. If not, I would still like to run the macro from Access, does anyone know how to do this? Below is some sample code:


sub main
'file objects
dim thisFile as string
dim nextnum as string
dim linefeed as string
'system objects
dim sys as object
dim sess as object
dim myscreen as object
'work objects
dim tollfree as string
dim logt as string
dim dnis as string
dim digits as string
dim reuse as string
dim row as integer
dim corp as string
dim lg1 as string
dim lg2 as string
dim lg3 as string
dim lg4 as string
dim lg5 as string
close
reset
set sys = createobject("extra.system")
if sys is nothing then
msgbox " session didnt work"
exit sub
end if
set sessionscollection = sys.sessions
set sess = sys.activesession
if sess is nothing then
msgbox " session didnt work"
exit sub
end if
set myscreen = sess.screen
filenumber = freefile
filenumber2 = freefile +1
filenumber3 = freefile +2

do

fileread = inputbox$ ("Enter filename to READ FROM (including PATH): ","Read From File Name","c:\Macro\trmie.txt")
if fileread = "" then
msgbox "You must enter a filename to read FROM"
else
filewrite = inputbox$ ("Enter filename to WRITE TO (including PATH): ","Write To File Name","c:\Macro\trmiout.txt")
if filewrite = "" then
msgbox "You must enter a filename to write TO"
end if
end if
loop until fileread <> "" and filewrite <> ""

tempfile = "c:\Macro\trimg.txt"

open fileread for input as #filenumber
open Filewrite for output as #filenumber2
open tempfile for output as #filenumber3

print #filenumber2, "Toll Free,LogTerm,DNIS,Digits,Re-Use"

'Process each number in the file

do

'Get a record
line input #filenumber,linefeed
tollfree = GetField (linefeed,1,",")
myscreen.sendkeys ("trmi<tab>e" & tollfree & " <enter>")
myscreen.waithostquiet (500)
corp = myscreen.getstring (3,8,8)
row = 11
do

if row = 21 then
myscreen.sendkeys ("<enter>")
myscreen.waithostquiet (500)
row = 11

end if

lg1 = myscreen.getstring (row,10,1)
if lg1 = " " then
lg1 = "0"
end if

lg2 = myscreen.getstring (row,11,1)
if lg2 = " " then
lg2 = "0"
end if

lg3 = myscreen.getstring (row,12,1)
if lg3 = " " then
lg3 = "0"
end if

lg4 = myscreen.getstring (row,13,1)
if lg4 = " " then
lg4 = "0"
end if

lg5 = myscreen.getstring (row,14,1)
if lg5 = " " then
lg5 = "0"
end if

logt = lg1 & lg2 & lg3 & lg4 & lg5
if logt = "00000" then
exit do
end if
print #filenumber3, tollfree & "," & corp & "," & logt
row = row + 1
loop
myscreen.sendkeys ("<PF10>")
myscreen.waithostquiet (1000)
close #filenumber3
open tempfile for input as #filenumber3
do
line input #filenumber3,linefeed
tollfree = getfield (linefeed,1,",")
corp = getfield (linefeed,2,",")
logt = getfield (linefeed,3,",")
myscreen.sendkeys ("trmi<tab>g" & corp & logt & "<enter>")
myscreen.waithostquiet (500)
dnis = myscreen.getstring (11,66,1)
digits = myscreen.getstring (12,66,10)
reuse = myscreen.getstring (13,66,2)
print #filenumber2, tollfree & "," & logt & "," & dnis & "," & digits & "," & reuse
loop until eof(filenumber3)
myscreen.waithostquiet(500)
close #filenumber3
open tempfile for output as #filenumber3
loop until eof(filenumber)
'Last Recored Processed
the_end:

close #filenumber
close #filenumber2
close #filenumber3
msgbox "File Complete"
end sub


Thanks

 

[calculus]

You're gonna love coding in VBA. I don't even use the EB environment anymore, it's just not as quick to code or debug in.

1. I've never tried to connect directly to Access via VBA, but if your code is in Access I would think it would work pretty well. You may want to search for assistance on this site for VBA help in Access.

2. You should be able to code in as many sessions as you'd like. The sessions information is actually the part of your code about "Create Object" and "sys.Sessions". You didn't give any detail as to exactly what you're trying to accomplish here, so I can't give you any more direction for now.

3. Your code should work pretty well. The "GetField" part doesn't have an exact counterpart, so you'll have to change your method for that. I use text files like so:

Open #1....
Write #1, Field1, Field2, Field3
close #1

Open #1 for input
Input #1, Field1Variable, Field2Variable, Field3Variable

This will suck in the variables all at once instead of individually. I didn't really understand what your code is doing, but I don't see anything else that will not transfer.

I would suggest you null your object at the end of the code though: "Set Sys = Nothing". This will free the memory and reset everything a bit cleaner.

Good luck,
calculus

 

[Lostie]

#1 - It is indeed possible in Extra Basic, over the years I've found that anything is possible in it However it would involve using CreateObject() to latch on to Access via the MS JET engine, then issue Access SQL commands to get from, insert to or update your tables - this is quite advanced stuff and maybe not what you need.

#2 - Extra macros are single-threaded, ie. you can only run one of them at a time, unfortunately. If you try to run more than one, the second will simply wait for the first one to finish, then it will start. So perhaps VBA would be better for you to use.

#3 - Most EB macros will work in Access VBA with very few changes (the main exception being dialog boxes).

A few months ago I made an EB Macro which gathers information from an Excel spreadsheet (.xls) and an Extra session, and inserts it directly into an Access database using the method I described in #1, let me know if you want some example code.

 

[JonFer]

I always start with MS Access (97 or 2000) and code in VBA to control the Extra sessions. It is very simple and powerful to have direct control of both applications. You can have tables in the database that function as screen maps so you don't have to change your modules when screen layouts change. It is great for screen scraping, too. You set a checkbox next to the fields you want and let it go find them.

Good luck with your projects.

 

[cast]

You can have tables in the database that function as screen maps so you don't have to change your modules when screen layouts change.

Can you give me an example of how to do this. I've almost completed my module but IT does seem to change my screens quite frequently. ;-)

Also, Is there a way to run the module without locking up the database it's running from?

Thanks everyone for your help.

 

[JonFer]

There are a couple of ways to use the screen map and it depends on what you are doing. Can you list a few examples of what you do on the screens? Are you extracting a few fields from multiple screens into one or multiple tables? Are you updating the screens?

The screen map table looks like this:

ScreenName
FieldName
FieldRow
FieldCol
FieldLen
FieldType

You look up positions with the ScreenName+FieldName which should be unique. You may need to add other fields to control the access method, indicate formats for comparison or updating, and set field types.


John

http://www.interautomateconsulting.com

 

[cast]

Basically I’m taking a CSV file and assigning variables based on line input. Using these variables I perform a few query functions with the Attachmate screen pulling further variables from the screen. I then evaluate both sets of variables and depending on the conditions I run the desired functions (which I have written as Subs) within the session. When complete I write the results to a new CSV file.
For example:

Do
Input file1, A, B
	With myscreen
		.waitforstring “SCREEN NAME 1”
		.moveto (11,16)
		.sendkeys (A & “<TAB>” & B & “<enter>”)
		.waitforstring “SCREEN NAME 2”
		C = .getstring (2,23,10)
		D = .getstring (21,10,2)
		if A = C and D = “” then
			call Sub1 
		elseif A<>C then
			call Sub2 
		else
			call Sub3
 		end if
	end with
write #file2,  A, B, C, D , E, F
loop until eof (file1)

Each sub does basically the same thing; performs session functions, retrieves variables from several screens and may call other subs.This is a very simplified example, so far I have a total of 27 variables, 56 screens, and 16 subs.

Thanks in advance for your help.

 

[JonFer]

For simple screen scraping, you can probably write a generic function that reads a table for the information on what screens to lookup. The table would be keyed from a value in your input file or table.

You've got a lot going on so you may want to take some time to think of a more general approach so you'll have less maintenance and it will be easier to add new functions. I'd get some help with the redesign and then use the forum for specific questions.



John

http://www.interautomateconsulting.com