Data from Excel to Extra and back

Question

Data from Excel to Extra and back

tchad (TechnicalUser)

(OP)

25 Jul 09 06:19

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.

SkipVought (Programmer) · Answer 1 · 2009-07-25 07:54:22

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 2 · 2009-07-25 09:04:45

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.

SkipVought (Programmer) · Answer 3 · 2009-07-25 09:13:45

Please post your code.

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

tchad (TechnicalUser) · Answer 4 · 2009-07-25 10:05:02

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.

SkipVought (Programmer) · Answer 5 · 2009-07-25 10:07:37

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

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

SkipVought (Programmer) · Answer 6 · 2009-07-25 10:10:54

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 7 · 2009-07-25 12:14:48

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

SkipVought (Programmer) · Answer 8 · 2009-07-25 12:41:20

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 9 · 2009-07-25 20:21:21

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.

SkipVought (Programmer) · Answer 10 · 2009-07-27 07:51:36

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 11 · 2009-07-27 15:51:27

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?

SkipVought (Programmer) · Answer 12 · 2009-07-28 07:59:09

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 13 · 2009-07-29 06:06:18

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

SkipVought (Programmer) · Answer 14 · 2009-07-29 07:40:04

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 15 · 2009-08-04 05:31:30

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

SkipVought (Programmer) · Answer 16 · 2009-08-04 08:02:23

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,
_{Just traded in my old subtlety...
for a NUANCE!}

tchad (TechnicalUser) · Answer 17 · 2009-08-31 04:34:56

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

blckngldhwk (TechnicalUser) · Answer 18 · 2009-08-31 12:06:16

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

tchad (TechnicalUser) · Answer 19 · 2009-08-31 15:59:50

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

blckngldhwk (TechnicalUser) · Answer 20 · 2009-08-31 16:08:13

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

tchad (TechnicalUser) · Answer 21 · 2009-09-07 07:28:51

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?

vzachin (TechnicalUser) · Answer 22 · 2009-09-08 09:29:26

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

tchad (TechnicalUser) · Answer 23 · 2009-09-12 00:12:01

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 (TechnicalUser) · Answer 24 · 2009-09-12 17:07:25

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

tchad (TechnicalUser) · Answer 25 · 2009-09-12 23:55:01

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

vzachin (TechnicalUser) · Answer 26 · 2009-09-13 21:50:26

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

tchad (TechnicalUser) · Answer 27 · 2009-09-14 04:00:39

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

vzachin (TechnicalUser) · Answer 28 · 2009-09-14 10:58:01

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

vzachin (TechnicalUser) · Answer 29 · 2009-09-14 11:17:41

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

vzachin (TechnicalUser) · Answer 30 · 2009-09-14 12:56:33

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.

vzachin (TechnicalUser) · Answer 31 · 2009-09-14 13:12:04

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

tchad (TechnicalUser) · Answer 32 · 2009-10-05 06:15:09

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

vzachin (TechnicalUser) · Answer 33 · 2009-10-05 14:44:24

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

tchad (TechnicalUser) · Answer 34 · 2009-10-07 07:38:09

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 (Programmer) · Answer 35 · 2009-10-07 10:34:48

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,
_{Just traded in my old subtlety...
for a NUANCE!}

vzachin (TechnicalUser) · Answer 36 · 2009-10-07 10:51:53

try this:
add exit do after Else

CODE

else
exit do

tchad (TechnicalUser) · Answer 37 · 2009-10-08 05:11:48

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 (Programmer) · Answer 38 · 2009-10-08 08:28:02

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,
_{Just traded in my old subtlety...
for a NUANCE!}

vzachin (TechnicalUser) · Answer 39 · 2009-10-08 08:53:12

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

SkipVought (Programmer) · Answer 40 · 2009-10-08 08:57:47

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

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

tchad (TechnicalUser) · Answer 41 · 2009-10-10 03:32:54

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.

vzachin (TechnicalUser) · Answer 42 · 2009-10-10 11:25:15

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

tchad (TechnicalUser) · Answer 43 · 2009-10-11 05:35:39

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

vzachin (TechnicalUser) · Answer 44 · 2009-10-11 16:28:17

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?

Come Join Us!

Posting Guidelines

Data from Excel to Extra and back