×
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!

*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

Attachmate and Excel interaction question

Attachmate and Excel interaction question

Attachmate and Excel interaction question

(OP)
Ok, I'm looking for help and this site has been good to me so far. I'm a government employee with minimal programming ability. I'm working on a macro script that hopefully will save us money and time. I do like to dig-in and figure things out, but I'm stumped. I've got a working macro used in Attachmate that will bounce around various screens, capture specific data, and enter onto a Data Capture tab in an Excel file. I'm using this to then pre-fill forms that we use so as to not have staff continue with duplication of work.

My question, is there a way to have the macro look either at a cell in Excel or Attachmate, recognize it's value as greater than 0, copy that information and add an additional screen to reference in the macro, but if the value is 0, then not reference to the additional screen? We currently run the macro in Attachmate.

I'm not looking for awards nor will this gain me any monetary benefit that I am aware of or looking for. I'm simply trying to make the job easier, quicker, and be more cost-effective for the tax dollars used to perform the job. Any help would be greatly appreciated. If you need to see what I have, which does work, I can post in thread if needed. Thank you.
Replies continue below

Recommended for you

RE: Attachmate and Excel interaction question

Hi,

Not a trivial task for someone who does not program!

Quote:

look either at a cell in Excel or Attachmate

Well you question exposes your lack of understanding. In Excel, chances are that the ZERO you see is a NUMBER, but the ZERO that you see in your emulator, in fact every single character on the screen is TEXT.

But, yes, by converting the character 0 to a number, it can be compared to a cell containing 0, via an If statement and then an appropriate transaction can be submitted to display a new screen.

I'd simply suggest posting specific question and the related code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Skip, thank you for your quick reponse. After my initial post, I did realize that copying from Attachmate converts the copied material to a text. I am pretty much copying off another macro we have and attempting to manipulate it with no real training.

"specific question and the related code." I would have posted the related code, but I don't have it and didn't know if it is possible. Now that I do, I'll figure it out. Thank you for your help.

RE: Attachmate and Excel interaction question

Copying from a screen (terminal) does not convert anything. All you have on the screen is text! Text that is comprised of digits is what may need to be converted to numbers before they can be compared with other numbers.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

Post back any time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Ok Skip, here is where I am stuck! I've literally gotten everything else to work, but stumbling here. I'm running this macro in Attachmate and using it to copy specific data over to an Excel spreadsheet. For this portion, I'm trying to have it look at a specific cell in Excel and if that cell has a value greater than 0, insert a command to copy that cell reference, paste it into Attachmate, go to that new screen, capture data, paste it into Excel, then get back to the previous screen; however, if the value is not greater than 0, then move on with the rest of the macro.

The very last section/paragraph is the beginning of the remainder of the macro and works fine if I take out all of the section above it in this thread.

The very first paragraph is where I believe I've screwed it up. Initially, the Compiler said this was good, but after trying it once it now says there is a CALL Syntax Error for the line "objWorkSheets.Range("A23").copy". I'll post the entire macro in a new post after this one for anyone's advice to make it cleaner. This is literally the last step for this macro version and we can start using it.

IF obj.WorkSheets("Data Input").Cells(23, "A")>0 Then
objWorkSheets.Range("A23").copy
Sess0.Screen.MoveTo 4,76
Sess0.Screen.pasteSess0.Screen.Sendkeys("<Enter>")

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(46, "A").Value = sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
obj.WorkSheets("Data Input").Cells(47, "A").Value = sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
obj.WorkSheets("Data Input").Cells(48, "A").Value = sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
obj.WorkSheets("Data Input").Cells(49, "A").Value = sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
obj.WorkSheets("Data Input").Cells(50, "A").Value = sess0.Screen.GetString(10, 11, 40) 'Sub DBA

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 4,24
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<EraseEOF >")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(4, 10, 4, 20)
MyArea.Select
MyScreen.Copy

RE: Attachmate and Excel interaction question

(OP)
I'm using the Macro Editor in Attachmate as our TSD has removed/blocked the EXCEL VBA editor on our machines. Also, we have no one in our TSD that will support macro's or even help. Way to go Big Government!

'--------------------------------------------------------------------------------
' This macro was created by
' Date: April 2, 2014
' User: afortuna
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 500 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


' This section of code contains the script

Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="C:\Users\AFortuna\Documents\Investigations\Inspections\Macro Files\Tester.xlsx" 'File location If the file is not already open
' Set obj = Getobject("C:\Users\AFortuna\Documents\Investigations\Inspections\Macro Files\Tester.xlsx") 'File is already open
obj.visible = True 'Not used if file is already open

Sess0.Screen.Putstring "TAR", 21, 5
Sess0.Screen.Putstring "AI", 21, 9
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,12
MsgBox "ENTER CTPL"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3><PF3>")
Pause 2
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(11, "A").Value = sess0.Screen.GetString(13, 48, 3) 'Phone # Area
obj.WorkSheets("Data Input").Cells(12, "A").Value = sess0.Screen.GetString(13, 52, 3) 'Phone # Prefix
obj.WorkSheets("Data Input").Cells(13, "A").Value = sess0.Screen.GetString(13, 56, 4) 'Phone # Postfix
obj.WorkSheets("Data Input").Cells(14, "A").Value = sess0.Screen.GetString(4, 65, 3) 'License Part 1
obj.WorkSheets("Data Input").Cells(15, "A").Value = sess0.Screen.GetString(4, 69, 6) 'License Part 2
obj.WorkSheets("Data Input").Cells(16, "A").Value = sess0.Screen.GetString(10, 15, 50) 'DBA
obj.WorkSheets("Data Input").Cells(17, "A").Value = sess0.Screen.GetString(11, 20, 41) 'Business Street
obj.WorkSheets("Data Input").Cells(18, "A").Value = sess0.Screen.GetString(12, 20, 30) 'Business City
obj.WorkSheets("Data Input").Cells(19, "A").Value = sess0.Screen.GetString(12, 55, 2) 'Business State
obj.WorkSheets("Data Input").Cells(20, "A").Value = sess0.Screen.GetString(12, 63, 5) 'Business Zip
obj.WorkSheets("Data Input").Cells(21, "A").Value = sess0.Screen.GetString(18, 24, 3) 'SR Part 1
obj.WorkSheets("Data Input").Cells(22, "A").Value = sess0.Screen.GetString(18, 27, 6) 'SR Part 2
obj.WorkSheets("Data Input").Cells(23, "A").Value = sess0.Screen.GetString(18, 33, 5) 'SR Sublocation
obj.WorkSheets("Data Input").Cells(24, "A").Value = sess0.Screen.GetString(9, 15, 50) 'Sole Proprietor Name

Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(18, 24, 18, 32)
MyArea.Select
MyScreen.Copy

Sess0.Screen.Putstring "TAR", 21, 5
Sess0.Screen.Putstring "AI", 21, 9
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,12
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Pause 2
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(1, "A").Value = sess0.Screen.GetString(4, 38, 3) 'TAT
obj.WorkSheets("Data Input").Cells(2, "A").Value = sess0.Screen.GetString(4, 55, 3) 'District
obj.WorkSheets("Data Input").Cells(3, "A").Value = sess0.Screen.GetString(16, 17, 53) 'Mailing Street
obj.WorkSheets("Data Input").Cells(4, "A").Value = sess0.Screen.GetString(17, 17, 30) 'Mailing City
obj.WorkSheets("Data Input").Cells(5, "A").Value = sess0.Screen.GetString(17, 52, 2) 'Mailing State
obj.WorkSheets("Data Input").Cells(6, "A").Value = sess0.Screen.GetString(17, 60, 5) 'Mailing Zip
obj.WorkSheets("Data Input").Cells(7, "A").Value = sess0.Screen.GetString(14, 17, 2) 'County Code
obj.WorkSheets("Data Input").Cells(8, "A").Value = sess0.Screen.GetString(7, 15, 2) 'Start Date Month
obj.WorkSheets("Data Input").Cells(9, "A").Value = sess0.Screen.GetString(7, 18, 2) 'Start Date Day
obj.WorkSheets("Data Input").Cells(10, "A").Value = sess0.Screen.GetString(7, 21, 2) 'Start Date Year
obj.WorkSheets("Data Input").Cells(45, "A").Value = sess0.Screen.GetString(8, 17, 2) 'SR C/O Month

IF obj.WorkSheets("Data Input").Cells(23, "A")>0 Then
objWorkSheets.Range("A23").copy
Sess0.Screen.MoveTo 4,76
Sess0.Screen.pasteSess0.Screen.Sendkeys("<Enter>")

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(46, "A").Value = sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
obj.WorkSheets("Data Input").Cells(47, "A").Value = sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
obj.WorkSheets("Data Input").Cells(48, "A").Value = sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
obj.WorkSheets("Data Input").Cells(49, "A").Value = sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
obj.WorkSheets("Data Input").Cells(50, "A").Value = sess0.Screen.GetString(10, 11, 40) 'Sub DBA

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 4,24
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<EraseEOF >")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(4, 10, 4, 20)
MyArea.Select
MyScreen.Copy

Sess0.Screen.Putstring "CTS", 21, 5
Sess0.Screen.Putstring "CM", 21, 9
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,12
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Pause 1
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(28, "A").Value = sess0.Screen.GetString(8, 10, 3) 'Sole Proprietor SSN
obj.WorkSheets("Data Input").Cells(29, "A").Value = sess0.Screen.GetString(8, 14, 2) 'Sole Proprietor SSN
obj.WorkSheets("Data Input").Cells(30, "A").Value = sess0.Screen.GetString(8, 17, 4) 'Sole Proprietor SSN
obj.WorkSheets("Data Input").Cells(31, "A").Value = sess0.Screen.GetString(8, 27, 2) 'Sole Proprietor DOB
obj.WorkSheets("Data Input").Cells(32, "A").Value = sess0.Screen.GetString(8, 30, 2) 'Sole Proprietor DOB
obj.WorkSheets("Data Input").Cells(33, "A").Value = sess0.Screen.GetString(8, 33, 4) 'Sole Proprietor DOB
obj.WorkSheets("Data Input").Cells(34, "A").Value = sess0.Screen.GetString(8, 61, 8) 'Sole Proprietor CDL
obj.WorkSheets("Data Input").Cells(35, "A").Value = sess0.Screen.GetString(9, 10, 3) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(36, "A").Value = sess0.Screen.GetString(9, 16, 3) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(37, "A").Value = sess0.Screen.GetString(9, 22, 4) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(38, "A").Value = sess0.Screen.GetString(9, 48, 3) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(39, "A").Value = sess0.Screen.GetString(9, 54, 3) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(40, "A").Value = sess0.Screen.GetString(9, 60, 4) 'Sole Proprietor Phone
obj.WorkSheets("Data Input").Cells(41, "A").Value = sess0.Screen.GetString(16, 17, 53) 'Sole Proprietor Street
obj.WorkSheets("Data Input").Cells(42, "A").Value = sess0.Screen.GetString(17, 17, 30) 'Sole Proprietor City
obj.WorkSheets("Data Input").Cells(43, "A").Value = sess0.Screen.GetString(17, 56, 2) 'Sole Proprietor State
obj.WorkSheets("Data Input").Cells(44, "A").Value = sess0.Screen.GetString(17, 64, 5) 'Sole Proprietor Zip


System.TimeoutValue = OldSystemTimeout

End Sub

RE: Attachmate and Excel interaction question

It's going to take me some time it analyse your code.

But just this general comment:

Whenever you SendKeys<> you ought to immediately follow with this code as you are doing most often but not consistently

CODE

'don't know what this is???
    MsgBox "ENTER CTPL"
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop    
'[b]if you're waiting for the system, then you don't need to wait for the cursor!
    Sess0.Screen.WaitForCursor 21, 21
    Sess0.Screen.SendKeys ("<Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop 

I'll be back with more comments.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Skip, that is the only user entered section, or what I'm hoping to be the only section. I'm trying to cut-down on their errors. Basically, it is waiting for them to enter the account number, then it will finish running. Your help is really appreciated.

RE: Attachmate and Excel interaction question



Is this a message area?

CODE

Sess0.Screen.GetString(4, 9, 4) 

How do you know that there is more data for this screen or if there is an error condition or if the screen is at the end?

Are you only dealing with one screen transaction or more in this code?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Our system flags accounts under review. When the account is flagged, there is a pop-up that prevents you from viewing other screens. That portion of the macro gets around that pop-up. One of the screens, for whatever our programmers reason, needed an F-3 twice where the other screens only needed it once.

RE: Attachmate and Excel interaction question

Is this why you declare and set another Sys & Sess. That confuses me.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
I dont' know. A chunk of this I took off another macro that used to work until we upgraded to Windows 7. The older macro used to copy paste everything into Notepad and print it for us to save time. We aren't able to paste into Notepad, which has derailed us. I've copied most of our field forms into a single Excel file with the hopes of actually using our Tablets in the field, electronically complete the forms in the field, and print them in the field. Since we are stumped on how to use the macro, I took it upon myself to try to adapt it to print into Excel, which I got most of it to work. The Flag sections work.

We had different coding for the sublocation for the staff to actually enter the number, that part started throwing me off, so I tried to write it, unsuccessfully, to check the Excel Spreadsheet for a value, then act based on that value. I'm trying to eliminate as much user time on it as possible so that we could finally take advantage of technology.

Here is the original section that I tried to insert:

Sess0.Screen.MoveTo 4,76
MsgBox "ENTER SUB ACCOUNT NUMBER AND PRESS ENTER - IF NO SUB JUST PRESS ENTER"

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitForKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Location = Trim(Sess0.Screen.GetString (05,24,1))
If Location = "0" Then

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<PF3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

I got confused by some of this. I don't want the entire screen to be copied, but only parts and sent to Excel and not Notepad.

RE: Attachmate and Excel interaction question



This was incorrect in two posts

CODE

IF obj.WorkSheets("Data Input").Cells(23, "A")>0 Then 
objWorkSheets.Range("A23").copy
Sess0.Screen.MoveTo 4,76
Sess0.Screen.pasteSess0.Screen.Sendkeys("<Enter>") 
and should be...

CODE

'
    If obj.Worksheets("Data Input").Cells(23, "A") > 0 Then
        objWorkSheets.Range("A23").Copy
        Sess0.Screen.MoveTo 4, 76
        Sess0.Screen.Paste
        Sess0.Screen.SendKeys ("<Enter>") 

BTW, I'd recommend coding in an indented structure. I can't read code banged up to the left!!!

CODE

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System") ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object. Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions
    
    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object. Stopping macro playback."
        Stop
    End If
    '--------------------------------------------------------------------------------
    ' Set the default wait timeout value
    g_HostSettleTime = 500 ' milliseconds
    
    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If
    
    ' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 Is Nothing) Then
        MsgBox "Could not create the Session object. Stopping macro playback."
        Stop
    End If
    If Not Sess0.Visible Then Sess0.Visible = True
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    
    ' This section of code contains the script
    
    Dim obj As Object
    Dim objWorkbook As Object
    Set obj = CreateObject("Excel.Application")
    obj.Workbooks.Open Filename:="C:\Users\AFortuna\Documents\Investigations\Inspections\Macro Files\Tester.xlsx" 'File location If the file is not already open
    ' Set obj = Getobject("C:\Users\AFortuna\Documents\Investigations\Inspections\Macro Files\Tester.xlsx") 'File is already open
    obj.Visible = True 'Not used if file is already open
    
    Sess0.Screen.PutString "TAR", 21, 5
    Sess0.Screen.PutString "AI", 21, 9
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Sess0.Screen.MoveTo 21, 12
    MsgBox "ENTER CTPL"
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Sess0.Screen.WaitForCursor 21, 21
    Sess0.Screen.SendKeys ("<Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Location = Trim(Sess0.Screen.GetString(4, 9, 4))
    If Location = "FLAG" Then
        Sess0.Screen.SendKeys ("<PF3><PF3>")
        Pause 2
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
    End If
    
    obj.Worksheets("Data Input").Cells(11, "A").Value = Sess0.Screen.GetString(13, 48, 3) 'Phone # Area
    obj.Worksheets("Data Input").Cells(12, "A").Value = Sess0.Screen.GetString(13, 52, 3) 'Phone # Prefix
    obj.Worksheets("Data Input").Cells(13, "A").Value = Sess0.Screen.GetString(13, 56, 4) 'Phone # Postfix
    obj.Worksheets("Data Input").Cells(14, "A").Value = Sess0.Screen.GetString(4, 65, 3) 'License Part 1
    obj.Worksheets("Data Input").Cells(15, "A").Value = Sess0.Screen.GetString(4, 69, 6) 'License Part 2
    obj.Worksheets("Data Input").Cells(16, "A").Value = Sess0.Screen.GetString(10, 15, 50) 'DBA
    obj.Worksheets("Data Input").Cells(17, "A").Value = Sess0.Screen.GetString(11, 20, 41) 'Business Street
    obj.Worksheets("Data Input").Cells(18, "A").Value = Sess0.Screen.GetString(12, 20, 30) 'Business City
    obj.Worksheets("Data Input").Cells(19, "A").Value = Sess0.Screen.GetString(12, 55, 2) 'Business State
    obj.Worksheets("Data Input").Cells(20, "A").Value = Sess0.Screen.GetString(12, 63, 5) 'Business Zip
    obj.Worksheets("Data Input").Cells(21, "A").Value = Sess0.Screen.GetString(18, 24, 3) 'SR Part 1
    obj.Worksheets("Data Input").Cells(22, "A").Value = Sess0.Screen.GetString(18, 27, 6) 'SR Part 2
    obj.Worksheets("Data Input").Cells(23, "A").Value = Sess0.Screen.GetString(18, 33, 5) 'SR Sublocation
    obj.Worksheets("Data Input").Cells(24, "A").Value = Sess0.Screen.GetString(9, 15, 50) 'Sole Proprietor Name
    
    Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object
    
    Set Sys = CreateObject("EXTRA.System")
    Set Sess = Sys.ActiveSession
    Set MyScreen = Sess.Screen
    Set MyArea = MyScreen.Area(18, 24, 18, 32)      'SR Part1 & SR Part2
    MyArea.Select
    MyScreen.Copy
    
    Sess0.Screen.PutString "TAR", 21, 5
    Sess0.Screen.PutString "AI", 21, 9
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Sess0.Screen.MoveTo 21, 12
    Sess0.Screen.Paste
    Sess0.Screen.SendKeys ("<Enter>")
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Location = Trim(Sess0.Screen.GetString(4, 9, 4))
    If Location = "FLAG" Then
        Sess0.Screen.SendKeys ("<PF3>")
        Pause 2
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
    End If
    
    obj.Worksheets("Data Input").Cells(1, "A").Value = Sess0.Screen.GetString(4, 38, 3) 'TAT
    obj.Worksheets("Data Input").Cells(2, "A").Value = Sess0.Screen.GetString(4, 55, 3) 'District
    obj.Worksheets("Data Input").Cells(3, "A").Value = Sess0.Screen.GetString(16, 17, 53) 'Mailing Street
    obj.Worksheets("Data Input").Cells(4, "A").Value = Sess0.Screen.GetString(17, 17, 30) 'Mailing City
    obj.Worksheets("Data Input").Cells(5, "A").Value = Sess0.Screen.GetString(17, 52, 2) 'Mailing State
    obj.Worksheets("Data Input").Cells(6, "A").Value = Sess0.Screen.GetString(17, 60, 5) 'Mailing Zip
    obj.Worksheets("Data Input").Cells(7, "A").Value = Sess0.Screen.GetString(14, 17, 2) 'County Code
    obj.Worksheets("Data Input").Cells(8, "A").Value = Sess0.Screen.GetString(7, 15, 2) 'Start Date Month
    obj.Worksheets("Data Input").Cells(9, "A").Value = Sess0.Screen.GetString(7, 18, 2) 'Start Date Day
    obj.Worksheets("Data Input").Cells(10, "A").Value = Sess0.Screen.GetString(7, 21, 2) 'Start Date Year
    obj.Worksheets("Data Input").Cells(45, "A").Value = Sess0.Screen.GetString(8, 17, 2) 'SR C/O Month
    
    If obj.Worksheets("Data Input").Cells(23, "A") > 0 Then
        objWorkSheets.Range("A23").Copy
        Sess0.Screen.MoveTo 4, 76
        Sess0.Screen.Paste
        Sess0.Screen.SendKeys ("<Enter>")
        
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
        
        Location = Trim(Sess0.Screen.GetString(4, 9, 4))
        If Location = "FLAG" Then
            Sess0.Screen.SendKeys ("<PF3>")
            Do While Sess0.Screen.OIA.Xstatus <> 0
                DoEvents
            Loop
        End If
        
        obj.Worksheets("Data Input").Cells(46, "A").Value = Sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
        obj.Worksheets("Data Input").Cells(47, "A").Value = Sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
        obj.Worksheets("Data Input").Cells(48, "A").Value = Sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
        obj.Worksheets("Data Input").Cells(49, "A").Value = Sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
        obj.Worksheets("Data Input").Cells(50, "A").Value = Sess0.Screen.GetString(10, 11, 40) 'Sub DBA
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.MoveTo 4, 24
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<EraseEOF >")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
    End If
    
    Set Sys = CreateObject("EXTRA.System")
    Set Sess = Sys.ActiveSession
    Set MyScreen = Sess.Screen
    Set MyArea = MyScreen.Area(4, 10, 4, 20)
    MyArea.Select
    MyScreen.Copy
    
    Sess0.Screen.PutString "CTS", 21, 5
    Sess0.Screen.PutString "CM", 21, 9
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.MoveTo 21, 12
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.Paste
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.SendKeys ("<Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Do While Sess0.Screen.OIA.Xstatus <> 0
        DoEvents
    Loop
    
    Location = Trim(Sess0.Screen.GetString(4, 9, 4))
    If Location = "FLAG" Then
        Pause 1
        Sess0.Screen.SendKeys ("<PF3>")
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
    End If
    
    obj.Worksheets("Data Input").Cells(28, "A").Value = Sess0.Screen.GetString(8, 10, 3) 'Sole Proprietor SSN
    obj.Worksheets("Data Input").Cells(29, "A").Value = Sess0.Screen.GetString(8, 14, 2) 'Sole Proprietor SSN
    obj.Worksheets("Data Input").Cells(30, "A").Value = Sess0.Screen.GetString(8, 17, 4) 'Sole Proprietor SSN
    obj.Worksheets("Data Input").Cells(31, "A").Value = Sess0.Screen.GetString(8, 27, 2) 'Sole Proprietor DOB
    obj.Worksheets("Data Input").Cells(32, "A").Value = Sess0.Screen.GetString(8, 30, 2) 'Sole Proprietor DOB
    obj.Worksheets("Data Input").Cells(33, "A").Value = Sess0.Screen.GetString(8, 33, 4) 'Sole Proprietor DOB
    obj.Worksheets("Data Input").Cells(34, "A").Value = Sess0.Screen.GetString(8, 61, 8) 'Sole Proprietor CDL
    obj.Worksheets("Data Input").Cells(35, "A").Value = Sess0.Screen.GetString(9, 10, 3) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(36, "A").Value = Sess0.Screen.GetString(9, 16, 3) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(37, "A").Value = Sess0.Screen.GetString(9, 22, 4) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(38, "A").Value = Sess0.Screen.GetString(9, 48, 3) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(39, "A").Value = Sess0.Screen.GetString(9, 54, 3) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(40, "A").Value = Sess0.Screen.GetString(9, 60, 4) 'Sole Proprietor Phone
    obj.Worksheets("Data Input").Cells(41, "A").Value = Sess0.Screen.GetString(16, 17, 53) 'Sole Proprietor Street
    obj.Worksheets("Data Input").Cells(42, "A").Value = Sess0.Screen.GetString(17, 17, 30) 'Sole Proprietor City
    obj.Worksheets("Data Input").Cells(43, "A").Value = Sess0.Screen.GetString(17, 56, 2) 'Sole Proprietor State
    obj.Worksheets("Data Input").Cells(44, "A").Value = Sess0.Screen.GetString(17, 64, 5) 'Sole Proprietor Zip
    
    
    System.TimeoutValue = OldSystemTimeout

End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
I'm not sure why that was on the same line. I know that shouldn't be like that. As to the indent, sorry.

RE: Attachmate and Excel interaction question

(OP)
This was the original that I started from:

'--------------------------------------------------------------------------------
' This macro was created by
' Date: Tuesday, 4-18-2011
' User: DMOOS
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP

End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 700 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If


' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Begin Dialog cbdlg 99, 130, 185, 130, "PACKAGE TYPE"
ButtonGroup .bg
OkButton 131, 74, 50, 14
CancelButton 132, 109, 50, 14
CheckBox 2, 0, 40, 10, "SOLE", .CheckBox1
CheckBox 2, 18, 40, 10, "PART", .CheckBox2
CheckBox 2, 36, 40, 10, "CORP", .CheckBox3
Text 44, 01, 96, 8, "SOLE OWNER"
Text 44, 19, 96, 8, "PARTNERSHIP"
Text 44, 37, 96, 8, "CORPORATION"
Text 44, 54, 96, 8, "Please check a checkbox"
Text 45, 78, 76, 8, "And then click OK -->"
End Dialog

Dim my_dialog as cbdlg
On Error Resume Next
Dialog my_dialog
If Err=102 then
MsgBox "canceled."
End If

'This section of code opens Notepad

Shell "Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%of"
Sendkeys "Courier{tab}Regular{tab}10{enter}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{Tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>TARAI000000000<Enter>")

'This section of code is for Sole Owner Accounts

If my_dialog.CheckBox1 Then

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,12
Sess0.Screen.Sendkeys("SOLE OWNER ACCOUNT<BackTab><BackTab><BackTab>TARAI")
g_HostSettleTime = 1000
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<EraseEOF>")
g_HostSettleTime = 700

'This section waits for you to input the account number on the go line,

Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Pause 1
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Putstring "M", 4, 7
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(1, 2, 22, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 4,76
MsgBox "ENTER SUB ACCOUNT NUMBER AND PRESS ENTER - IF NO SUB JUST PRESS ENTER"

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitForKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Location = Trim(Sess0.Screen.GetString (05,24,1))
If Location = "0" Then

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<PF3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>AUDMC<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Sendkeys("<PF3><PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>SPRAI")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section goes to VLTBC
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>VLTBC<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the SALES TAX number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(5, 69, 5, 77)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'This section goes to REVFZ
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>REVFZ")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<Tab>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 07,17
Sess0.Screen.Sendkeys("<Pf6><Down>y<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>TPSTP<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 15}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the TIN number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(4, 66, 4, 73)
MyArea.Select
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>CTSCM")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 8}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section Prints the File
Sendkeys "%FP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "-"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%P"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%FX"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "N"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

END IF

'This section of code is for Partnership Accounts

If my_dialog.CheckBox2 Then

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,12
Sess0.Screen.Sendkeys("PARTNERSHIP ACCOUNT<BackTab><BackTab><BackTab>TARAI")
g_HostSettleTime = 1000
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<EraseEOF>")
g_HostSettleTime = 700


'This section waits for you to input the account number on the go line,

Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Pause 1
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF


Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Putstring "M", 4, 7
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(1, 2, 22, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop


Sess0.Screen.MoveTo 4,76
MsgBox "ENTER SUB ACCOUNT NUMBER AND PRESS ENTER - IF NO SUB JUST PRESS ENTER"

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitForKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Location = Trim(Sess0.Screen.GetString (05,24,1))
If Location = "0" Then

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<PF3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>AUDMC<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Sendkeys("<PF3><PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>SPRAI")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section goes to VLTBC
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>VLTBC<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the SALES TAX number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(5, 69, 5, 77)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'This section goes to REVFZ
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>REVFZ")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<Tab>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 07,17
Sess0.Screen.Sendkeys("<Pf6><Down>y<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>TPSTP<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the TIN number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(4, 66, 4, 73)
MyArea.Select
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>CTSCM")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.Sendkeys("<PF10>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 10,67

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(10,69,13,78)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sendkeys "{Up 20}{Enter 12}"
Sendkeys "{Up 32}{Enter 12}"
Sendkeys "{Up 32}{Enter 12}"
Sendkeys "{Up 36}{Delete 40}"'{Enter 10}"

' Prints the File
Sendkeys "%FP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "-"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%P"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%FX"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "N"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

END IF

'This section of code is for Corporation Accounts

If my_dialog.CheckBox3 Then

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,12
Sess0.Screen.Sendkeys("CORPORATION ACCOUNT<BackTab><BackTab><BackTab>TARAI")
g_HostSettleTime = 1000
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<EraseEOF>")
g_HostSettleTime = 700

'This section waits for you to input the account number on the go line,

Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Pause 1
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen
Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Putstring "M", 4, 7
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(1, 2, 22, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 4,76
MsgBox "ENTER SUB ACCOUNT NUMBER AND PRESS ENTER - IF NO SUB JUST PRESS ENTER"

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitForKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Location = Trim(Sess0.Screen.GetString (05,24,1))
If Location = "0" Then

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<PF3>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>AUDMC<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy
AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.Sendkeys("<PF3><PF3><PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>SPRAI")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 21,21
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section goes to VLTBC
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>VLTBC<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the SALES TAX number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(5, 69, 5, 77)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'This section goes to REVFZ
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>REVFZ")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Sendkeys("<Tab>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.WaitForCursor 07,17
Sess0.Screen.Sendkeys("<Pf6><Down>y<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>TPSTP<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

'This section copies the TIN number
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(4, 66, 4, 73)
MyArea.Select
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,14
Sess0.Screen.Sendkeys("<BackTab><BackTab><BackTab>CTSCM")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Sess0.Screen.Paste
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.Sendkeys("<PF10>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Set MyArea = MyScreen.Area(1, 1, 20, 80)
MyArea.Select
MyScreen.Copy

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%{tab}"
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 10,67

Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen

Set MyArea = MyScreen.Area(10,68,13,78)
MyArea.Select
Sess0.Screen.Copy
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<PF3><PF3>")

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.Paste
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.MoveTo 21,05
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("CTSCM")
Sess0.Screen.PasteContinue
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
Set MyArea = MyScreen.Area(01,02,20,80)
MyArea.Select
Sess0.Screen.CopyAppend
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

AppActivate "Untitled - Notepad"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Enter 12}%EP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Sendkeys "{Up 20}{Enter 12}"
Sendkeys "{Up 32}{Enter 12}"
Sendkeys "{Up 32}{Enter 12}"
Sendkeys "{Up 36}{Delete 40}"'{Enter 10}"

' Prints the File
Sendkeys "%FP"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "{Tab}"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "-"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%P"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "%FX"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sendkeys "N"
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

System.TimeoutValue = OldSystemTimeout

End SUB

RE: Attachmate and Excel interaction question



Quote:

look either at a cell in Excel or Attachmate, recognize it's value as greater than 0, copy that information and add an additional screen to reference in the macro, but if the value is 0, then not reference to the additional screen?

If Cell value > 0 Or Attachmate value > "0" Then
   copy that information
   add an additional screen (and what does this mean? a different transaction or what?)
End If
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
So, I work for a taxation agency. We have Seller's Permits and other various licenses. A business owner, like a CVS as an example, would have one single Seller's Permit, but multiple (sub) locations attached to that Seller's Permit. Not all business owners have multiple locations. The first screen that we look at is actually a Cigarette License, which is attached to a specific Seller's Permit. The macro starts at the Cigarette License, copies off the information that we need, grabs the Seller's Permit number off the screen, then goes to that screen and copies off the information from that screen that is needed. IF there is a sublocation, then I need the macro to go to the appopriate screen sublocation screen after the Seller's Permit screen, grab information, then go back to the Seller's Permit and continue on with the remainder of the macro. If there isn't a sublocation, then I need it to just go on as normal.

My hope, was to have it recognize whether there was a sublocation from the appropriate Excel Spreadsheet Cell, and go from there without user input. I thought originally about having it react to an Attachmate reference instead, but the screen it would have to copy two sets of info from a prior screen, which I don't think is possible. I'm hoping it can reference back to the original Worksheet it is pasting all of this information into to make that decision how to proceed without user input.

I'm comfortable around computers, better at building them than any type of programming though. I know I'm in over-my-head, but I'm the only flag bearer in my agency on this one. Everyone agrees this would be a great idea, but no one either knows how to do it or is willing to spend the money (government problem) to get it done. I'm trying to smash it in between my normal job duties, because I know what it means to the agency and taxpayers if I can get it done.

RE: Attachmate and Excel interaction question

So, I don't know how your system works as far as paging and holding previous screen's data. In some IMS systems, you have PA keys (Program Action) and PF keys (Program Function). The PF keys transmit data, while the PA keys have different behaviors, like reloading previous screens.

What system is your terminal emulator interfacing with?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
We are using Extra! X-treme 9.0 Service Pack 2.

Our function keys send commands or we can go to our "Go" line and enter jump codes to get around.

If I take out the sublocation issue, the macro works. However, we need to be able to copy info from the sublocation screens on those rare occassions that the Seller's Permit has a sublocation.

As to what system, not sure. We have tablets connected to our network, which eventually interfaces with data at the TEALE Data Center, where most CA goverment info is kept.

RE: Attachmate and Excel interaction question


You have code like this

CODE

'
        If Trim(Sess0.Screen.GetString(4, 9, 4)) = "FLAG" Then
            Sess0.Screen.SendKeys ("<PF3>")
            Do While Sess0.Screen.OIA.Xstatus <> 0
                DoEvents
            Loop
        End If
        
        obj.Worksheets("Data Input").Cells(46, "A").Value = Sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
        obj.Worksheets("Data Input").Cells(47, "A").Value = Sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
        obj.Worksheets("Data Input").Cells(48, "A").Value = Sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
        obj.Worksheets("Data Input").Cells(49, "A").Value = Sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
        obj.Worksheets("Data Input").Cells(50, "A").Value = Sess0.Screen.GetString(10, 11, 40) 'Sub DBA 

Don't you want to write to the worksheet ONLY when you do this?

If so...

CODE

'
        If Trim(Sess0.Screen.GetString(4, 9, 4)) = "FLAG" Then
            Sess0.Screen.SendKeys ("<PF3>")
            Do While Sess0.Screen.OIA.Xstatus <> 0
                DoEvents
            Loop
        
            obj.Worksheets("Data Input").Cells(46, "A").Value = Sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
            obj.Worksheets("Data Input").Cells(47, "A").Value = Sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
            obj.Worksheets("Data Input").Cells(48, "A").Value = Sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
            obj.Worksheets("Data Input").Cells(49, "A").Value = Sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
            obj.Worksheets("Data Input").Cells(50, "A").Value = Sess0.Screen.GetString(10, 11, 40) 'Sub DBA
        Else
            'don't write to the Excel sheet
        End If 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
That Flag code just gets us past a pop-up if it occurs. We don't paste anything from that pop-up.

RE: Attachmate and Excel interaction question


So if there is no FLAG string, it does not matter. You still write from the screen to Excel?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Yes. I believe the original author wrote that particular code just to get around the pop-up, that doesn't always occur. It looked to me that he got the string to compare to the word Flag, which is at that string if the pop-up occurs, but we don't take paste any info off that pop-up screen. We can't ignore it if it comes up, so that portion of the macro looks for it's occurance, and if it happens, then hits the appropriate function key to dismiss the pop-up.

RE: Attachmate and Excel interaction question

Okay, thanks for clearing that up.

I also saw this...

CODE

objWorkSheets.Range("A23").copy 

should be

CODE

obj.WorkSheets.Range("A23").copy 

I'm headed home. We'll take on the next issue tomorrow.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Thank you and be safe.

RE: Attachmate and Excel interaction question

(OP)
So you know, when I run the macro with the fixes you've pointed out, I get the following error message (though the compiler says it's good):

Object error
Line number 143
Stopping Macro playback.

Line 143 is: Obj.WorkSheets.Range("A23").copy

RE: Attachmate and Excel interaction question

CODE

Obj.WorkSheets("data input").Range("A23").copy 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
Thank you. Last few catches were dumb misses on my part. Still have the same error pop-up.

RE: Attachmate and Excel interaction question

Is it capitalized & spelled correctly?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
I copied it from a prior reference that works earlier in the macro.

I ran this on an account that does not have a sublocation and everything ran perfect. It did exactly what I wanted it to do, which was look to see and skip it if there isn't an indicator in the Excel Spreadsheet. I'm only getting the error when there is a sublocation and it seems to be when trying to copy the cell from Excel. Not sure if the rest of the sublocation portion of the macro will work until we can get past the copy portion.

RE: Attachmate and Excel interaction question

(OP)
This is what I have down in the script:

IF obj.WorkSheets("Data Input").Cells(23, "A").Value>0 Then
obj.WorkSheets("Data Input").Range(23, "A").copy
Sess0.Screen.MoveTo 4,76
Sess0.Screen.paste
Sess0.Screen.Sendkeys("<Enter>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop

Location = Trim(Sess0.Screen.GetString (04,09,4))
If Location = "FLAG" Then
Sess0.Screen.Sendkeys("<PF3>")
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
End IF

obj.WorkSheets("Data Input").Cells(46, "A").Value = sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
obj.WorkSheets("Data Input").Cells(47, "A").Value = sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
obj.WorkSheets("Data Input").Cells(48, "A").Value = sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
obj.WorkSheets("Data Input").Cells(49, "A").Value = sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
obj.WorkSheets("Data Input").Cells(50, "A").Value = sess0.Screen.GetString(10, 11, 40) 'Sub DBA

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.MoveTo 4,24
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<EraseEOF >")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys ("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Do While sess0.Screen.OIA.Xstatus <> 0
DoEvents
Loop
END IF

Paragraph 1 is the comparison portion to determine if the sublocation screen is needed. This works if there isn't a sublocation, but keeps throwing up the same error message if there IS a sublocation. I changed a bit of the language hoping to match prior language, but still didn't help me any.
Paragraph 2 we've talked about today.
Paragraph 3 is the specfic information that I need from the screen captured and copied into Excel. Seems to work in the rest of the macro, so I'm assuming it will do the same here.
Paragraph 4 the actions necessary to get out of the sublocation screen and back to the main screen to finish running the macro.

RE: Attachmate and Excel interaction question

CODE

obj.WorkSheets("Data Input").Cells(23, "A").copy 

Range("A23")
Or
Cells(23,"A")

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
What I had read said to do it as Range, but now that you bring it up, I want the info in that specific cell.

RE: Attachmate and Excel interaction question

Same results!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

(OP)
I can't check it until tomorrow. No longer at work.

RE: Attachmate and Excel interaction question



I don't know what paragraphs you are referring to.

But regarding the sublocation screen code (if A23 > 0), if you fix the A23 reference as I stated, either Range("A23") or Cells(23, "A") or Cells(23, 1) all reference the same cell in VBA, then the following lines should execute.

CODE

If obj.Worksheets("Data Input").Cells(23, "A") > 0 Then
        Sess0.Screen.PutString obj.Worksheets("Data Input").Cells(23, "A"), 4, 76
        
        Sess0.Screen.SendKeys ("<Enter>")
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
        
        If Trim(Sess0.Screen.GetString(4, 9, 4)) = "FLAG" Then
            Sess0.Screen.SendKeys ("<PF3>")
            Do While Sess0.Screen.OIA.Xstatus <> 0
                DoEvents
            Loop
        End If
        
        With obj.Worksheets("Data Input")
            .Cells(46, "A").Value = Sess0.Screen.GetString(6, 73, 2) 'Sub Start Date Month
            .Cells(47, "A").Value = Sess0.Screen.GetString(6, 76, 2) 'Sub Start Date Day
            .Cells(48, "A").Value = Sess0.Screen.GetString(6, 79, 2) 'Sub Start Date Year
            .Cells(49, "A").Value = Sess0.Screen.GetString(7, 73, 2) 'Sub C/O Month
            .Cells(50, "A").Value = Sess0.Screen.GetString(10, 11, 40) 'Sub DBA
        End With
        
        Sess0.Screen.MoveTo 4, 24
        Sess0.Screen.SendKeys ("<EraseEOF >")
        Sess0.Screen.SendKeys ("<Enter>")
        Do While Sess0.Screen.OIA.Xstatus <> 0
            DoEvents
        Loop
    End If 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Attachmate and Excel interaction question

hi afortuna98,

trying to follow your logic. how is the sublocation determined?

Quote:

Paragraph 1 is the comparison portion to determine if the sublocation screen is needed. This works if there isn't a sublocation, but keeps throwing up the same error message if there IS a sublocation. I changed a bit of the language hoping to match prior language, but still didn't help me any.

btw, wrap your code in tags so that it's easier to view by inserting a bracket "[" followed by the word "code]" at the beginning of the code. at the end of the code, it's a bracket "[/" followed by the word "code]". just don't include the "quotes".

RE: Attachmate and Excel interaction question

(OP)
It works!!!!! I'm on cloud 9 right now. I've got more to do to it to make it fully functional, but this was a big step. Skip, thank you. I owe you Starbucks, just let me know how to get it to you!

Stupid silly errors on my part that could have made this faster, but I'm new and will hopefully learn from it. Now, to make it better. Reminds of the partially constructed Death Star that is able to shoot, now I just have to finish the outer shell!

RE: Attachmate and Excel interaction question

Wonderful!

I'll have a Trenta of French Roast. Or you can just send a box of k-cups to my iPhone winky smile

Post back when you need help. I'd suggest that you start a new thread.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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