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.
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
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!
RE: Data from Excel to Extra and back
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
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,
Just traded in my old subtlety...
for a NUANCE!
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
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
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
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
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
'''
Sess0.Screen.putstring(.cells(lrow, icol).value) 2,24 'Will add correct coords later
'''
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!
RE: Data from Excel to Extra and back
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
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!
RE: Data from Excel to Extra and back
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
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
CODE
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
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
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
CODE
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
my coding is usually something like this. see if this helps or makes sense to you.
CODE
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
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
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
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.SendKeys ("S")
CODE
RE: Data from Excel to Extra and back
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
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
Next x
RE: Data from Excel to Extra and back
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
RE: Data from Excel to Extra and back
CODE
'lasttime = 1
r = 23
s = 2
CODE
'lasttime = 1
'r = 23
's = 2
exit do: end if
also, what does this do (in blue)
CODE
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
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.SendKeys ("<Enter>") 'does this go to page 2???
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
'Loop Until lasttime = 1
Loop Until s = 2
your code is interesting.
RE: Data from Excel to Extra and back
CODE
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
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
there's a sequencing for statements:
see if this makes any sense to you:
CODE
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
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
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!
RE: Data from Excel to Extra and back
add exit do after Else
CODE
exit do
RE: Data from Excel to Extra and back
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
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
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!
RE: Data from Excel to Extra and back
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,
Just traded in my old subtlety...
for a NUANCE!
RE: Data from Excel to Extra and back
I just need to go down to the next row within the loop, that's all.
RE: Data from Excel to Extra and back
did you at least try adding the exit do to see what will happen?
you need trust us
CODE
for
do
if
else
exit do : end if
loop
next
end with
RE: Data from Excel to Extra and back
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
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
which code should not run again?