Attachmate and Excel interaction question

[afortuna98]

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.

 

[SkipVought]

You have code like this

'
        If Trim(Sess0.Screen.GetString(4, 9, 4)) = "FLAG" Then
[highlight #8AE234]            Sess0.Screen.SendKeys ("<PF3>")
            Do While Sess0.Screen.OIA.Xstatus <> 0
                DoEvents
            Loop[/highlight]
        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 [highlight #8AE234]ONLY when you do this[/highlight]?

If so...

'
        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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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.

 

[SkipVought]

Okay, thanks for clearing that up.

I also saw this...

objWorkSheets.Range("A23").copy

should be

obj[highlight #F57900].[/highlight]WorkSheets.Range("A23").copy

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

Thank you and be safe.

 

[afortuna98]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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

 

[SkipVought]

Is it capitalized & spelled correctly?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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.

 

[afortuna98]

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.

 

[SkipVought]

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

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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.

 

[SkipVought]

Same results!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[afortuna98]

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

 

[SkipVought]

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.

    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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[remy988]

hi afortuna98,

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

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

 

[afortuna98]

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!

 

[SkipVought]

Wonderful!

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

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]