Data from Excel to Extra and back

[tchad]

Hi,

Just after help with a macro to pull a number from Excel, put it into Extra, pull the result from a field in Extra and then loop with the next cell down, until there are no more cells left in Excel with data in them.

My macro I had worked for one line, but I can't work out how to loop it from the first cell to the bottom cell.

If anyone has any macros that could point me in the right direction, that would be great.

Thanks.

 

[blckngldhwk]

Sub ExcelExtraExcel()

    Dim Excel As Object, ExcelWorkbook As Object
    
            
    AppTitle = "EDS-MF1 - EXTRA! X-treme"
    
    AppActivate AppTitle
            
 
    Set Excel = GetObject(, "Excel.Application")
            
    Set System = CreateObject("Extra.System")
    
    Set Sess = System.ActiveSession
    
    Set MyScreen = Sess.Screen

    
    With Excel.Worksheets("WorksheetName")
    
        [b]Row = 7[/b]
        
        Do
        
            Pull = .Cells(Row, "A").Value
            
            MyScreen.Putstring Pull, 1, 1
            
            MyScreen.SendKeys ("<Enter>")
            MyScreen.WaitHostQuiet 100
            
            
            NewPull = MyScreen.Getstring(1, 1, 1)
            
            
            .Cells(Row, "B").Value = NewPull            
            
            Row = Row + 1


        [b]Loop until Row = 23[/b]
  
    End With
    
End Sub

 

[tchad]

Sub ExtractKeycode()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession


   With Worksheets("Input")

       Row = 2

       Do

           Pull = .Cells(Row, "A").Value  '**This will pull information from Excel


           Sess0.Screen.Putstring Pull, 2, 19 '**This will put information from Excel into Extra, use your own coordinates


           Sess0.Screen.SendKeys ("<Enter>")

           Sess0.Screen.MoveTo 6, 2

           Sess0.Screen.SendKeys ("S")

           Sess0.Screen.SendKeys ("<Enter>")

           Keycode = Sess0.Screen.Getstring(7, 7, 8)

           Store = Sess0.Screen.Getstring(7, 16, 4)

           Qty = Sess0.Screen.Getstring(7, 53, 5)

           '**This will pull information from Extra, use your own coordinates

           .Cells(Row, "N").Value = Keycode  '**This will place information from Extra to Excel

           .Cells(Row, "O").Value = Store

           .Cells(Row, "P").Value = Qty

           Row = Row + 1  '**This will progress the code to the next line


       Loop Until .Cells(Row, "A").Value <> ""  '** This will loop your code until there is a blank value in Column A in Excel

   End With

End Sub

With the above code, I need it to loop down the page, when it hits 23, presses enter and keeps looping until it hits an end point, then it will move to another point on the page and type a command, and start the process again. This will end ultimately when Column A is empty. So there are a couple of loops I need within the code.

Is this possible?

 

[vzachin]

hi Tchad,

my coding is usually something like this. see if this helps or makes sense to you.

Sub testLoop()
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System")
Set Sess = System.ActiveSession
Set Sess = Sess.Screen

With Sheets("Input")
'this defines the rows in Excel
begrw = 2
endrw = .Cells(Rows.Count, "a").End(xlUp).Row

'this will loop down the Excel sheet until the last row
For i = begrw To endrw
pull = .Range("a" & i)

'------------------------------------
'this will loop the Extra session from row 9 to row 23
'after it reaches row 23, this will go to the next i (row in excel)
For x = 9 To 23
extradata = Sess.GetString(x, 1, 1)
Next x
'-------------------------------------

Next i

End With

End Sub

 

[tchad]

Hi vzachin,

Thanks for the code.

I need it to work so it works as per the following.

Paste in PO number.

Press Enter.

Tabs down. ( I'll use the moveto command for this)

Sendkeys "S"

Enter

Pulls data from the next screen, loops until it hits ****
then moves to 6,2 and types C1 Enter. May need to add in Enter if there if it needs to scroll down.

Process repeats itself again, until column "A" is empty.

So would the above be easy to do?

 

[vzachin]

hi tchad,

you already had most of the coding in your post 7 Sep 09 7:28.

you basically need to view each row until row 23, then go to the next page and continue viewing until ***.

Do
for x = 1 to 23  'this will start at row 1 until row 23

next x

if sess0.screen.getstring (?,?,3) = "***" then
sess0.screen.putstring "C1",6,2
Sess0.Screen.SendKeys ("<enter>") 
exit do 
else 
Sess0.Screen.SendKeys ("<???>") 'whatever your page advance key is

end if


Loop 'this assumes *** will always be found. if *** is not found, this will loop forever. you can add a counter to exit the loop if this is the case. or a qualifer for end of pages.

also in your code

Sess0.Screen.MoveTo 6, 2

           Sess0.Screen.SendKeys ("S")

you can use this instead

Sess0.Screen.PutString "S",6,2

hope i understood what you're trying to do. if not, we'll get there

 

[tchad]

Hi vzachin,

How does the below look?

Is everything in the right order?

Sub ExtractKeycode()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession


   With Worksheets("Input")

       Row = 2

       Do

           Pull = .Cells(Row, "A").Value  '**This will pull information from Excel


           Sess0.screen.putstring Pull, 2, 19 '**This will put information from Excel into Extra, use your own coordinates


           Sess0.screen.SendKeys ("<Enter>")

           Sess0.screen.MoveTo 6, 2

           Sess0.screen.SendKeys ("S")

           Sess0.screen.SendKeys ("<Enter>")
           
           Do
           
For x = 1 To 23  'this will start at row 1 until row 23

Next x

If Sess0.screen.getstring(7, 7, 8) = "********" Then

Sess0.screen.putstring "C1", 6, 2

Sess0.screen.SendKeys ("<enter>")

Keycode = Sess0.screen.getstring(7, 7, 8)

Store = Sess0.screen.getstring(7, 16, 4)

Qty = Sess0.screen.getstring(7, 53, 5)

.Cells(Row, "N").Value = Keycode  '**This will place information from Extra to Excel

.Cells(Row, "O").Value = Store

.Cells(Row, "P").Value = Qty

Exit Do

Else
Sess0.screen.SendKeys ("<Enter>") 'whatever your page advance key is

End If


Loop 'this assumes *** will always be found. if *** is not found, this will loop forever. you can add a counter to exit the loop if this is the case. or a qualifer for end of pages.

           

           '**This will pull information from Extra, use your own coordinates

          

           Row = Row + 1  '**This will progress the code to the next line


       Loop Until .Cells(Row, "A").Value <> ""  '** This will loop your code until there is a blank value in Column A in Excel

   End With

End Sub

 

[vzachin]

If Sess0.screen.getstring(7, 7, 8) = "********" Then

are the astericks always found at 7,7 ?
or where would the 3 astericks *** be found?

i thought you needed to look at the extra screen row by row...if that's not the case, you don't need this...

For x = 1 To 23  'this will start at row 1 until row 23

Next x

 

[tchad]

No, they are not always found at 7,7.
What do I put instead?

Yes, I do need the x = 1 statement.

I got the below working today, but it stopped when I was getting the second lot of data on the first screen.

Sub Newtest()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        '---------------------------------
        'assumption
        'data begins in row 1, column a,c,d,e
        'where column a is date
        'column c,d,e are the names
        '----------------------------------
        rw = 2

        With Worksheets("Sheet1")
        Range("B2").Select
        For x = rw To ActiveSheet.Rows.Count
                                             
        PO = .Cells(x, 1)
        
        '-----send data to Attachmate-------
        
        If PO = "" Then Exit Sub
        Sess0.Screen.MoveTo 2, 19
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<EraseEOF>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString PO, 2, 19
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.MoveTo 6, 2
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("S")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Do
        For r = 7 To 23
        dd = 7
        Keycode = Sess0.Screen.Area(r, dd, r, dd + 7, Page)
        Selection = Keycode
        If Keycode = "********" Then
        'lasttime = 1
        r = 23
        s = 2
        Else
        ActiveCell.Offset(0, 1).Select
        Store = Sess0.Screen.Area(r, dd + 9, r, dd + 12, Page)
        Selection = Store
        ActiveCell.Offset(0, 1).Select
        Qty = Sess0.Screen.Area(r, dd + 46, r, dd + 50, Page)
        Selection = Qty
        ActiveCell.Offset(1, -2).Select
        End If
        Next r
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        'Loop Until lasttime = 1
        Loop Until s = 2
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.MoveTo 1, 2
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("C1")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        
        Next x  'next row
        
        End With

End Sub

 

[vzachin]

what line did it stop on? i "assume" you are stepping through your code

 

[vzachin]

if i'm reading this correctly, then i would modify

If Keycode = "********" Then
        'lasttime = 1
        r = 23
        s = 2

to something like this

If Keycode = "********" Then
        'lasttime = 1
        'r = 23
        's = 2
exit do: end if

also, what does this do (in blue)

        [blue]ActiveCell.Offset(0, 1).Select[/blue]
        Store = Sess0.Screen.Area(r, dd + 9, r, dd + 12, Page)
        Selection = Store
        [blue]ActiveCell.Offset(0, 1).Select[/blue]
        Qty = Sess0.Screen.Area(r, dd + 46, r, dd + 50, Page)
        Selection = Qty
        [blue]ActiveCell.Offset(1, -2).Select[/blue]

 

[vzachin]

Hi tchad, i recreated your code in excel vba and stepped through it...it looks good so i'm perplexed

it stopped when I was getting the second lot of data on the first screen.

one observation i have is when you place your data back to excel, column a will not align with the data in b,c,d

also, the code over here:

        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")   'does this go to page 2???
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        'Loop Until lasttime = 1
        Loop Until s = 2

is this how you go to the next page? it would make more sense to go to next page only if If Keycode = "********" is not found...

your code is interesting.

 

[vzachin]

your code slightly modified; try it

Sub Newtest2()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        '---------------------------------
        'assumption
        'data begins in row 1, column a,c,d,e
        'where column a is date
        'column c,d,e are the names
        '----------------------------------
        rw = 2
        rw1 = 1
        With Worksheets("Sheet1")
        
        For x = rw To ActiveSheet.Rows.Count
                                             
        PO = .Cells(x, 1)
        
        '-----send data to Attachmate-------
        
        If PO = "" Then Exit Sub
        Sess0.Screen.MoveTo 2, 19
        Sess0.Screen.SendKeys ("<EraseEOF>")
        Sess0.Screen.PutString PO, 2, 19
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "S", 6, 2
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Do
        For r = 7 To 23
        dd = 7
        Keycode = Sess0.Screen.GetString(r, 7, 8)
        If Keycode = "********" Then
        Exit Do

        Else
        Store = Sess0.Screen.GetString(r, 16, 4)
        Qty = Sess0.Screen.GetString(r, 53, 4)
        rw1 = rw1 + 1
        .Cells(rw1, 2) = Keycode
        .Cells(rw1, 3) = Store
        .Cells(rw1, 4) = Qty
        End If
        Next r
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

        Loop
        
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        Sess0.Screen.PutString "C1", 1, 2
        Sess0.Screen.SendKeys ("<Enter>")
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
        
        Next x  'next row
        
        End With

End Sub

 

[tchad]

Tried another code, basically I just want it to loop but it comes up with the error, "no for without next"

Sub Newtest()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
g_HostSettleTime = 1


       NOTBEFORE = Application.InputBox("Not Before")
       NOTAFTER = Application.InputBox("Not After")

       Rw = 2

       With Worksheets("Multiple Store")

       For x = Rw To ActiveSheet.Rows.Count

       Keycode = .Cells(x, 1)
       Qty = .Cells(x, 2)
       Store = .Cells(x, 3)
       If Store = "" Then Exit Sub

       '-----send data to Attachmate-------


       'Front Screen

       Sess0.Screen.PutString Store, 11, 30
       Sess0.Screen.MoveTo 12, 30
       Sess0.Screen.SendKeys (NOTBEFORE)
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       Sess0.Screen.MoveTo 13, 30
       Sess0.Screen.SendKeys (NOTAFTER)
       Sess0.Screen.MoveTo 16, 30
       Sess0.Screen.SendKeys ("D")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       Sess0.Screen.SendKeys ("<Enter>")

       'Keycode Screen

       Do
       Sess0.Screen.MoveTo 6, 63
       Sess0.Screen.PutString Keycode, 6, 63
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       Sess0.Screen.SendKeys ("<Enter>")
       Sess0.Screen.SendKeys ("<Enter>")
       Sess0.Screen.SendKeys ("<Pf10>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

       'Pack control - Creation

       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       Sess0.Screen.MoveTo 11, 17
       Sess0.Screen.SendKeys ("<EraseEOF>")
       Sess0.Screen.PutString Qty, 11, 17
       Sess0.Screen.SendKeys ("<Enter>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       Sess0.Screen.SendKeys ("<Pf4>")
       If Keycode = "" Then
       Sess0.Screen.SendKeys ("<Pf6>")
       Sess0.Screen.SendKeys ("Test")
       Sess0.Screen.MoveTo 8, 69
       Sess0.Screen.SendKeys ("O")
       Sess0.Screen.SendKeys ("<Enter>")
       Sess0.Screen.SendKeys ("<Pf3>")
       Sess0.Screen.SendKeys ("<Pf3>")
       Sess0.Screen.SendKeys ("<Enter>")
       Else

       End If

       Next x

       x = x + 1

       Loop

       End With

       End Sub

 

[vzachin]

hi tchad,

there's a sequencing for statements:

see if this makes any sense to you:

with
if
for 
do

loop
next
end if
end with

fyi: your LOOP should be placed before NEXT X

see if that works for you

 

[tchad]

Vzachin,

Thanks for the info.

I want it to go to the next row, but since the loop is placed first, all it does is loop forever. Is there a way to solve this?

 

[SkipVought]

...loop forever. Is there a way to solve this?

Yes. Test a key value in your loop and exit the loop when the key value reaches or exceeds (+/-) the exit condition.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vzachin]

try this:
add exit do after Else

else
exit do

 

[tchad]

If I exit do, I won't be able to copy the other rows of data in.

I just want it to loop into the next row of data, but I can't put next x before loop...

 

[SkipVought]

It is VERY important to have a valid and logically correct control structure.

What you attempted will NEVER work -- loop levels and basic structure CANNOT cross other structure boundaries.

here is your basic structure...

    With Worksheets("Multiple Store")

       For x = Rw To ActiveSheet.Rows.Count

           Keycode = .Cells(x, 1)
           Qty = .Cells(x, 2)
           Store = .Cells(x, 3)
           If Store = "" Then Exit Sub
    
           '-----send data to Attachmate-------
    
    
           'Front Screen
    
    
           'Keycode Screen
    
           Do
        
               'Pack control - Creation
        
               If Keycode = "" Then
               
               Else
        
               End If
        
        
           Loop

       Next x

    End With

NOTICE:

1. Next x is moved to the correct place as the Do...Loop MUST be ENTIRELY within the For...Next loop

2. For...Next incriments x. Why are you incrimenting x also?

3. I strongly recommend indenting each level of the control structure as depicted above. It greatly improved readability. You can IMMEDIATELY visualize what happens under what conditions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vzachin]

did you try putting the exit do and see what happens?