Data from Excel to Extra and back

tchad · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

Please post your code.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

tchad · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 25, 2009

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 · Jul 27, 2009

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 · Jul 27, 2009

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 · Jul 28, 2009

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 · Jul 29, 2009

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 · Jul 29, 2009

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 · Aug 4, 2009

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 · Aug 4, 2009

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 · Aug 31, 2009

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 · Aug 31, 2009

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 · Aug 31, 2009

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Data from Excel to Extra and back

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor