×
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!
  • Students Click Here

*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

Data from Excel to Extra and back

Data from Excel to Extra and back

Data from Excel to Extra and back

(OP)
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.

 

RE: Data from Excel to Extra and back




Hi,

This loops thru values in column A, assuming that row 1 is a heading...

CODE

dim r as range
for each r in range([A2], [A2].end(xldown))
   debug.print r.value
next r
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
Hi,

Thanks for the reply.

I tried using this just on some numbers on a spreadsheet, as I don't have Extra at home.

It doesn't seem to go down the list of numbers, I'm just wondering exactly what the code does? Will this pull data cell by cell until data has run out?

Do I insert this within my Extra code, it doesn't seem to be a code that's run before Extra is declared as an object and activated?

Thanks.

RE: Data from Excel to Extra and back



Please post your code.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
I won't have any code until I get back to work.

I'm fine on extracting the data, it's just the part where I have to pull a cell from the Excel sheet, put it into Extra, and then loop the process again until no cells are left, which is what I need help with.

RE: Data from Excel to Extra and back



Is that not what this loop does--go thru each cell in the list?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back



BTW, I strongly recommend coding in Excel VBA and referencing Attachmate from Excel.

To put it another way, would your rather drive a dune buggy and tow a Hummer, or drive a Hummer and tow a dune buggy?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
Hi,

I do my coding in Excel VBA at work, I have only done extracting so far which I find easy enough, it's just the sending of data from Extra, then extracting then looping again which gets me.

I have compiled the below code from what I have read tonight on the forums.

I'll add the correct coords later but am I getting the general idea on what to do?

Just to clarify, I'm pulling data from A2, placing it into Extra, pressing enter, pulling the results back out into B2, then looping so the macro jumps down to A3, repeats the process, extracts the data into B3 etc. I want this to happen until the A column runs out of data, i.e, runs into a blank cell.

Appreciate your help.

Sub Main()

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

Dim lRow As Long, iCol As Integer
Set xlSheet = xlApp.Worksheets("Sheet1")
With xlSheet
  Set Keycode = .Range("A:A")
  Set Pull = .Range("B1")

  Do
     For iCol = 1 To 7
        Select Case iCol
           Case 1
           
           Sess0.Screen.MoveTo 1, 1 'Will add correct coords later
           
           Sess0.Screen.SendKeys ("<EraseEOF>")
           
           Sess0.Screen.putstring(.cells(lrow, icol).value) 2,24 'Will add correct coords later
              
           Sess0.Screen.SendKeys ("<Enter>")
           
           xlSheet.Cells(2,2).value = Sess0.Screen.GetString(row,col,len)
           
              
        End Select
     Next
     
     
     
     lRow = lRow + 1
  Loop While .Cells(lRow, "A").Value <> ""
End With

End Sub

RE: Data from Excel to Extra and back


you logic is not well developed.

CODE

Sub Main()

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

    Dim r As Range, iCol As Integer
    With Sheets("Sheet1")
        For Each r In Range([A2], [A2].End(xlDown))
            Sess0.Screen.MoveTo 1, 1 'Will add correct coords later
            
            Sess0.Screen.SendKeys ("<EraseEOF>")
            For iCol = 1 To 7
               Select Case iCol
                  Case 1
                  
                  
                    Sess0.Screen.putstring .Cells(lRow, iCol).Value, 2, 24 'Will add correct coords later
                       
                    Sess0.Screen.SendKeys ("<Enter>")
                     
                'this logic needs you attention.
                'do you do this for each row & column in Excel
                'how are cl and ln defined?
                'do you have a table for this stuff (its what I do)
                    For rw = 7 To 22
                        .Cells(2, 2).Value = Sess0.Screen.GetString(rw, cl, Ln)
                    Next
               End Select
            Next
        Next
    End With
End Sub
BTW, you don't need xlApp if you're coding in Excel.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
Thanks for the reply. As I said, I'm only beginning with the copying of data to Extra, so please try to bear with me.

I'm not sure where you are confused with what I've said.

The GetString will always be from one place, so column and length are defined by myself when I check the coords in Extra.

Only column A will have data, looping through until there is just a blank cell. Column B will receive the data pulled from Extra by GetString.

  .Cells(2, 2).Value = Sess0.Screen.GetString(rw, cl, Ln)

In the above code, shouldn't an x be placed, like (x,2) if I want to keep looping down column B? Otherwise won't it just paste over the data already there until the loop is finshed?

Thanks.

RE: Data from Excel to Extra and back



Yes.

But your code has 7 values from each Excel row going to your IMS screen.

CODE

For iCol = 1 To 7

'''

Sess0.Screen.putstring(.cells(lrow, icol).value) 2,24 'Will add correct coords later

'''
So I am totally confused.

Suppose you explain in prose, what the process is for onw row from Excel, explaining exactly where the results from your IMS screen finally end up.

DETAILS!!!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
OK.

Sorry, there should be 1 value, as it's only one cell going to Extra at a time.

The cell goes into Extra, presses enter, and the result comes up just below where I put the cell of data in. The correct coords are at work, so that's no problem to add in once we have corrected the code.

So, all I need is for the macro to show iCol = 1 if it's just one cell of data?

Data goes from A2, into Extra, presses enter, data gets pulled from Extra into B2 and the macro loops to A3 and so forth.

Does that make sense now?

RE: Data from Excel to Extra and back



Yes.  It would have saved bot you and me lots of time if you have explained your requirement like that right off!

Make sure that the correct coordinates are in getstring putstring and DataRefreshed

CODE

Sub Main()

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

    Dim r As Range, iCol As Integer
    With Sheets("Sheet1")
        For Each r In Range(.[A2], .[A2].End(xlDown))
            
            Sess0.Screen.SendKeys ("<EraseEOF>")
                  
            Sess0.Screen.putstring r.Value, 2, 24 'Will add correct coords later
               
            Sess0.Screen.SendKeys ("<Enter>")
            Sess0.Screen.MoveTo 1, 1 'move here until IMS responds
            
            Do
                DoEvents
            Loop Until Sess0.Screen.DataRefreshed(2, 24)    'IMS responds here (I guess)???
            
            r.Offset(0, 1).Value = Sess0.Screen.GetString(3, 24, 12) 'is this length correct???
        Next
    End With
End Sub
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
I've got the below code to work.

However, if I wanted to add more putstrings and getstrings, do I just add Example = (x,3) and so forth?


CODE


Sub Main()

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

    Dim r As Range, iCol As Integer
    With Sheets("Sheet1")
        For Each r In Range([A2], [A2].End(xlDown))
            Sess0.Screen.MoveTo 1, 1 'Will add correct coords later
            
            Sess0.Screen.SendKeys ("<EraseEOF>")
            For iCol = 1 To 1
               Select Case iCol
                  Case 1
                  
                  
                    Sess0.Screen.putstring .Cells(lRow, iCol).Value, 2, 24 'Will add correct coords later
                       
                    Sess0.Screen.SendKeys ("<Enter>")
                     
                'this logic needs you attention.
                'do you do this for each row & column in Excel
                'how are cl and ln defined?
                'do you have a table for this stuff (its what I do)
                    For rw = 7 To 22
                        .Cells(2, 2).Value = Sess0.Screen.GetString(rw, cl, Ln)
                    Next
               End Select
            Next
        Next
    End With
End Sub



RE: Data from Excel to Extra and back




Well, again, what is the logical PROCESS that you need to perform, stating what you get from Excel, where you put in the screen, what you, in turn get from IMS are return to Excel, where?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
Hi,

Example below.

5555  1001  29
5555  1002  50

Column A's number will never change, so I only need to pull that once into the first screen. Column B numbers need to match what's on screen, but if there is no qty (Column C) against a store number on the screen, a 0 needs to be put there. Not sure how I will work this...

RE: Data from Excel to Extra and back




What do you mean by, "Column B numbers need to match what's on screen"?

You need to state what you want to happen under the MATCH or NOT conditions.  You never state where the data is on the screen.

Are you just comparing the data on the screen to the data in Excel?

This is very detailed thinking and writing.  Every step and every option must be explained!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
I have some code below, I can't get it to loop properly because apparently I don't have enough loop statements as opposed to Do statements.

It basically starts at one screen where all the order numbers are, send S and Enter, pulls data from the next screen until it hits a certain point, then backs out into the first screen, where is repeats the process again until the loop ends.

Any ideas?

CODE


Sub Macro11()

 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 = 250    ' 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)

 Range("a2").Select
 Do
 r = 7
  dd = 7
 Do

   PO = Sess0.Screen.Area(6, 10, 6, 22, Page)
   If PO = "*************" Then
   r = 23
   t = 2
   Else
   Sess0.Screen.MoveTo 6, 2
   Sess0.Screen.SendKeys ("S")
   Sess0.Screen.SendKeys ("<Enter>")
   keycode = Sess0.Screen.Area(r, dd, r, dd + 7, Page)
   Selection = keycode
   Sess0.Screen.MoveTo 1, 2
   If keycode = "********" Then
   Sess0.Screen.MoveTo 1, 2
   Sess0.Screen.SendKeys ("C1")
   Sess0.Screen.SendKeys ("<Enter>")
   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
   r = r + 1
      End If
 Loop Until r = 23
  Sess0.Screen.SendKeys ("<Enter>")
   Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Loop Until t = 2




End Sub

RE: Data from Excel to Extra and back

To answer your original question, here is a very simple code for doing what you originally asked.  Some of the early declarations may be unnecessary, Skip will let me know if that's the case.  This is to be used in VBA, keep in mind.

CODE

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")
    
        Row = 1
        
        Do
        
            Pull = .Cells(Row, "A").Value  '**This will pull information from Excel

            
            MyScreen.Putstring Pull, 1, 1 '**This will put information from Excel into Extra, use your own coordinates
            
            
            MyScreen.SendKeys ("<Enter>")
            MyScreen.WaitHostQuiet 100
            
            
            NewPull = MyScreen.Getstring(1, 1, 1) '**This will pull information from Extra, use your own coordinates
            
            
            .Cells(Row, "B").Value = NewPull  '**This will place information from Extra to Excel
            
            
            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

RE: Data from Excel to Extra and back

(OP)
Hi,

Thanks for that.

I won't be pulling any data from Excel, just pulling it from Extra into Excel.

So what loop can I use to terminate the sub once I've reached the point I want? Bear in mind, in my previous post it was ********

 

RE: Data from Excel to Extra and back

CODE

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")
    
        Row = 7
        
        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


        Loop until Row = 23
  
    End With
    
End Sub

RE: Data from Excel to Extra and back

(OP)

CODE

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?

RE: Data from Excel to Extra and back

hi Tchad,

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

CODE

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

RE: Data from Excel to Extra and back

(OP)
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?

RE: Data from Excel to Extra and back

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

CODE

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

CODE

Sess0.Screen.MoveTo 6, 2

           Sess0.Screen.SendKeys ("S")
you can use this instead

CODE

Sess0.Screen.PutString "S",6,2
hope i understood what you're trying to do. if not, we'll get there
 

RE: Data from Excel to Extra and back

(OP)
Hi vzachin,

How does the below look?

Is everything in the right order?

CODE


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

RE: Data from Excel to Extra and back


CODE

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

CODE

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

Next x

 

RE: Data from Excel to Extra and back

(OP)
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.

CODE


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

RE: Data from Excel to Extra and back

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

RE: Data from Excel to Extra and back

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

CODE

If Keycode = "********" Then
        'lasttime = 1
        r = 23
        s = 2
to something like this

CODE

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


also, what does this do (in blue)

CODE

        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

RE: Data from Excel to Extra and back

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

Quote:

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:

CODE

        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.

RE: Data from Excel to Extra and back

your code slightly modified; try it

CODE

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

RE: Data from Excel to Extra and back

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

CODE


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

RE: Data from Excel to Extra and back

hi tchad,

there's a sequencing for statements:

see if this makes any sense to you:

CODE

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

RE: Data from Excel to Extra and back

(OP)
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?

RE: Data from Excel to Extra and back



Quote:

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

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

try this:
add exit do after Else

CODE

else
exit do

RE: Data from Excel to Extra and back

(OP)
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...

RE: Data from Excel to Extra and back



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

CODE

    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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

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

RE: Data from Excel to Extra and back



Yes, you need something in the Do...Loop to Exit the loop, as both vzachin and I have suggested previously.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Data from Excel to Extra and back

(OP)
I don't want to exit the do though, as it will then repeat statements outside the do loop which I don't want repeated.

I just need to go down to the next row within the loop, that's all.

RE: Data from Excel to Extra and back

when you exit the loop, it will go to the next x. then it will go to the do/loop again until it exits...

did you at least try adding the exit do to see what will happen?

you need trust us

CODE

    with
    
        for
        
            do
            
                if
                
                
                else
                
                exit do : end if
                
            
            loop
            
        next
    
    end with

RE: Data from Excel to Extra and back

(OP)
I did, but the first lot of code below will run again if I abort the do. I need to keep looping but go to the next row. I did try it.

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

 

RE: Data from Excel to Extra and back

tchad,

there's something about your code that i don't quite understand.

is this what your code is supposed to be doing:
(1) find store
(2) if keycode is not blank, do something
(3) otherwise if keycode is blank, do something else
(4) then go to the next store and repeat

Quote:

first lot of code below will run again if I abort the do
which code should not run again?

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